AccessからMySQLのテーブルを取得する

MySql community

MySQL Community Editionは、世界で最も多く使用されているオープンソース・データベースの、無償で自由にダウンロードして利用することができるバージョンです。

GPLライセンスに従って利用することができ、多くのオープンソース・デベロッパーが参加する、巨大で活発なコミュニティによるサポートを享受することができます。

https://www.mysql.com/jp/products/community

MySQL Connector/ODBC

1. 必要な準備

1-1. MySQL Connector/ODBCのインストール

1.MySQL Connector/ODBCをダウンロードしてインストールします。

1.インストール後、Windowsの「ODBC Data Source Administrator」でMySQLへの接続を設定できます。

MySQL Connector/ODBC

https://dev.mysql.com/downloads/connector/odbc

1-2. MySQLの接続情報を確認

•サーバーアドレス（例: localhost または IP アドレス）

•ポート番号（デフォルトは 3306）

•データベース名（例: testdb）

•ユーザー名とパスワード

DAO　…　Data Access Objects

1.VBAエディタを開く（Alt + F11）。

2.**[ツール] → [参照設定]**を選択。

3.Microsoft Office XX.X Access Database Engine Object Library（最新バージョン）をチェック。

4.古い DAO 3.6 がチェックされている場合は、そのチェックを外す。

ADO　…　ActiveX Data Object

1.1 ADOライブラリの参照設定

ADOを使用するには、プロジェクトで参照設定を行う必要があります。

1.VBAエディタを開く（Alt + F11）。

2.メニューから [ツール] > [参照設定] を選択。

3.Microsoft ActiveX Data Objects x.x Library にチェックを入れる

（x.x はバージョン、一般的には最新の「6.1」がおすすめ）。

4.[OK]をクリック。

接続文字列　MySQL　基本構文

Driver={MySQL ODBC 8.0 Unicode Driver};
Server=[サーバーアドレス] ;
Database=[データベース名] ;
User=[ユーザー名] ;
Password=[パスワード] ;
Option=[オプション];"

接続テスト

MySQLへの接続テスト

Option Compare Database
'CursorTypeEnum（カーソルタイプ）
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'ロックタイプ
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

ErrHandler:<br>Debug.Print "エラーが発生しましSub mysql_connect_Test()
  Dim ado As Object ' ADO Connectionオブジェクト
  Dim res As Object ' ADO Recordsetオブジェクト
  Dim strDsn As String
  ' 接続用DSN（ここでMySQLサーバー情報を設定）
    strDsn = ""
    strDsn = strDsn + "Driver={MySQL ODBC 8.0 Unicode Driver};"
    strDsn = strDsn + "Server=localhost;" ' サーバーアドレス
    strDsn = strDsn + "Database=eng001;" ' データベース名
    strDsn = strDsn + "User=db_user;"     ' ユーザー名
    strDsn = strDsn + "Password=db_user;" ' ユーザーバスワード
    strDsn = strDsn + "Option=3;"
  Debug.Print strDsn
  On Error GoTo ErrHandler  ' エラー処理開始
  Set ado = CreateObject("ADODB.Connection")
      ado.Open strDsn
        If ado.State = 1 Then Debug.Print "接続成功!!"; ado.State
        ' ここに　処理を記述する
      ado.Close
  Set ado = Nothing
  Exit Sub
  
ErrHandler:
    Debug.Print "エラーが発生しました: " & Err.Description
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub
た: " &amp; Err.Description<br>If Not ado Is Nothing Then<br>If ado.State = 1 Then ado.Close<br>Set ado = Nothing<br>End If<br>End Sub

テーブル一覧の取得

Function ado_Dsn_MySql() As String
  Dim strDsn As String
  strDsn = ""
  strDsn = strDsn + "Driver={MySQL ODBC 8.0 Unicode Driver};"
  strDsn = strDsn + "Server=localhost;"
  strDsn = strDsn + "Database=eng001;"
  strDsn = strDsn + "User=db_user;"
  strDsn = strDsn + "Password=db_user;"
  strDsn = strDsn + "Option=19;"
  ado_Dsn_MySql = strDsn
End Function

Sub sql_GetTableList()
    Dim ado As Object  ' ADO Connection
    Dim res As Object  ' ADO Recordset
    Dim strDsn As String
    Dim strSQL As String
    
    Debug.Print "テーブル一覧"
    strDsn = ado_Dsn_MySql()
    On Error GoTo ErrHandler
    Set ado = CreateObject("ADODB.Connection") ' ADO接続作成
        ado.Open strDsn '　ｍｙSQLに接続
    
        strSQL = "SHOW TABLES;" ' テーブル一覧を取得するSQL
        Set res = ado.Execute(strSQL) ' クエリ実行
        
        Do Until res.EOF ' テーブル名をDebug出力
            Debug.Print res.Fields(0).Value
            res.MoveNext
        Loop
        ado.Close: Set ado = Nothing
  Exit Sub

ErrHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbExclamation, "エラー"
    On Error Resume Next
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

テーブルの検索

Sub ado_Select_Table()
    Dim ado As Object ' ADO Connectionオブジェクト
    Dim res As Object ' ADO Recordsetオブジェクト
    Dim strDsn, strSQL As String
    Dim i As Integer
    
    strDsn = ado_Dsn_MySql() ' 接続文字列の取得
    strSQL = "SELECT * FROM 顧客マスタ" ' 実行するSQL文の作成

    Set ado = CreateObject("ADODB.Connection")  ' ADO オブジェクトの作成
        ado.Open strDsn                         'データベースへの接続
    Set res = CreateObject("ADODB.Recordset")   'レコードセットの作成
        res.Open strSQL, ado, adOpenKeyset, adLockReadOnly
    
    On Error GoTo ErrorHandler
    ' データを表示
    For i = 0 To res.Fields.Count - 1   ' ヘッダーを書き込む
        Debug.Print res.Fields(i).Name & ":";
    Next i
    Debug.Print
    
    While Not res.EOF
        For i = 0 To res.Fields.Count - 1
            Debug.Print res.Fields(i).Value & ":";
        Next i
        res.MoveNext
        Debug.Print
    Wend
    res.Close: Set res = Nothing ' 終了処理
    ado.Close: Set ado = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
    Set ws = Nothing
End Sub

テーブルのインポート

Sub ImportMySQLTable_ODBC()
    Dim conn As Object
    Dim strConnect_ado As String
    Dim strConnect_dao As String
    Dim strSQL As String
    Dim rst As Object
    Dim db As DAO.Database
    Dim tblName As String
    Dim mysqlTable As String

    tblName = "import_顧客マスタ" ' Access側のテーブル名
    mysqlTable = "顧客マスタ" ' MySQL側のテーブル名

    strConnect_ado = ado_Dsn_MySql()            ' 接続文字列（MySQL ODBC 8.0 ドライバ使用例）
    strConnect_dao = "ODBC;" & strConnect_ado
    
    Set conn = CreateObject("ADODB.Connection") ' ADO接続を作成
        conn.Open strConnect_ado
        strSQL = "SELECT * FROM " & mysqlTable  ' SQLでMySQLテーブルから全データを取得
    Set rst = CreateObject("ADODB.Recordset")   ' ADOレコードセットを開く
        rst.Open strSQL, conn, 1, 3             ' 1 = adOpenKeyset, 3 = adLockOptimistic

    Set db = CurrentDb ' DAOでAccess DBを取得
    On Error Resume Next ' 既にテーブルが存在する場合、削除
    db.TableDefs.Delete tblName
    On Error GoTo 0
    ' レコードセットをAccessにインポート
    DoCmd.TransferDatabase acImport, "ODBC Database", strConnect_dao, _
                           acTable, mysqlTable, tblName, False
    rst.Close: Set rst = Nothing ' 後始末
    conn.Close:  Set conn = Nothing
    Set db = Nothing
    MsgBox "インポートが完了しました: " & tblName
End Sub

リンクテーブルによる更新

Sub create_LinkTable_mysql()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String
    Dim linkedTableName As String
    Dim mysqlTableName As String

    linkedTableName = "Linked_顧客マスタ"    ' Accessで表示するテーブル名
    mysqlTableName = "顧客マスタ"            ' MySQL側のテーブル名

    strConnect = dao_Dsn_MySql() ' 接続文字列（MySQL ODBC 8.0 ANSI ドライバ）
    
    Set db = CurrentDb ' 現在のAccessデータベースを取得

    On Error Resume Next ' 既存のリンクテーブルがあれば削除
    db.TableDefs.Delete linkedTableName
    On Error GoTo 0

    Set tdf = db.CreateTableDef(linkedTableName) ' 新しくリンクテーブルを作成
    tdf.SourceTableName = mysqlTableName
    tdf.Connect = strConnect
    db.TableDefs.Append tdf
    MsgBox "リンクテーブルが作成されました: " & linkedTableName
End Sub

よかったらシェアしてね！

AccessからMySQLのテーブルを取得する