VBAからMySQLの接続や操作をする方法(VBAのみで接続可)

MySQLデータベースへの接続には、ODBCの設定を行う方法が一般的ですが、VBAだけで接続することも可能です。そしてそちらの方が単純です。

VBAだけで行うため、他のPCでの利用する場合でもマクロを持っていくだけで利用できるようになります。

この記事では、Excel VBAからMySQLへ接続する方法を詳しく解説します。

基本的な接続方法

以下はコード内でMySQL接続先を指定し、SELECT文を実行してその結果をセルに出力する例です。

重要なのは接続文字列の作成部分で、ここにMySQLへの接続情報を記載します。

パラメータ	説明	例
Driver	使用するODBCドライバー名	{MySQL ODBC 8.0 Unicode Driver}
Server	MySQLサーバーのホスト名またはIPアドレス	localhost, 192.168.1.100
Port	ポート番号（省略時は3306）	3306
Database	接続するデータベース名	testdb
User	ユーザー名	testuser
Password	パスワード	yourpassword
Option	ODBC Driverの接続オプション。通常のSQL利用であれば3でよい。	3

Sub MySQLConnect()
    Dim conn    As ADODB.Connection     '// VBA からデータベースへ接続するためのオブジェクト
    Dim connStr As String               '// 接続文字列
    Dim rs      As Object               '// ADODB.Recordset オブジェクト
    Dim sql     As String               '// SQL文字列
    Dim iRow    As Long                 '// セル縦位置
    Dim iCol    As Long                 '// セル横位置
    
    '// 接続文字列の作成
    connStr = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
              "Server=localhost;" & _
              "Database=testdb;" & _
              "User=testuser;" & _
              "Password=yourpassword;" & _
              "Option=3;"
    
    '// 接続オブジェクトの作成
    Set conn = New ADODB.Connection
    
    '// エラーハンドリング
    On Error GoTo ErrorHandler
    
    '// 接続
    conn.Open connStr
    
    '// 接続できている場合
    If conn.State = adStateOpen Then
        '// ここにデータベース操作のコードを記述。ここではSELECTの例
        sql = "SELECT * FROM testdb.t_sample LIMIT 5"
    
        '// SELECT文を実行してその結果を受け取る
        Set rs = conn.Execute(sql)
        
        iRow = 1
        '// データレコードを１行ずつ順に読み込み
        While Not rs.EOF
            '// フィールドをタブ区切りで出力
            Debug.Print rs.Fields(0).Value & vbTab & rs.Fields(1).Value & vbTab & rs.Fields(2).Value & vbTab & rs.Fields(3).Value
            
            '// セルに出力
            '// １レコードの全項目をループ
            For iCol = 0 To rs.Fields.Count - 1
                '// セルに項目値をセット
                Cells(iRow, iCol + 1).Value = rs.Fields(iCol).Value
            Next
            '// セル位置を１つ下に変更
            iRow = iRow + 1
            
            '// 次行に移動
            rs.MoveNext
        Wend

        '// レコードセットを閉じる
        rs.Close
       
        '// 接続を閉じる
        conn.Close
    End If
    
    Set conn = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox "接続エラー: " & Err.Description, vbCritical
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
        Set conn = Nothing
    End If
End Sub

Sub MySQLConnect()

Dim conn As ADODB.Connection '// VBA からデータベースへ接続するためのオブジェクト

Dim connStr As String '// 接続文字列

Dim rs As Object '// ADODB.Recordset オブジェクト

Dim sql As String '// SQL文字列

Dim iRow As Long '// セル縦位置

Dim iCol As Long '// セル横位置

'// 接続文字列の作成

connStr = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _

"Server=localhost;" & _

"Database=testdb;" & _

"User=testuser;" & _

"Password=yourpassword;" & _

"Option=3;"

'// 接続オブジェクトの作成

Set conn = New ADODB.Connection

'// エラーハンドリング

On Error GoTo ErrorHandler

'// 接続

conn.Open connStr

'// 接続できている場合

If conn.State = adStateOpen Then

'// ここにデータベース操作のコードを記述。ここではSELECTの例

sql = "SELECT * FROM testdb.t_sample LIMIT 5"

'// SELECT文を実行してその結果を受け取る

Set rs = conn.Execute(sql)

iRow = 1

'// データレコードを１行ずつ順に読み込み

While Not rs.EOF

'// フィールドをタブ区切りで出力

Debug.Print rs.Fields(0).Value & vbTab & rs.Fields(1).Value & vbTab & rs.Fields(2).Value & vbTab & rs.Fields(3).Value

'// セルに出力

'// １レコードの全項目をループ

For iCol = 0 To rs.Fields.Count - 1

'// セルに項目値をセット

Cells(iRow, iCol + 1).Value = rs.Fields(iCol).Value

'// セル位置を１つ下に変更

iRow = iRow + 1

'// 次行に移動

rs.MoveNext

Wend

'// レコードセットを閉じる

rs.Close

'// 接続を閉じる

conn.Close

End If

Set conn = Nothing

Exit Sub

ErrorHandler:

MsgBox "接続エラー: " & Err.Description, vbCritical

If Not conn Is Nothing Then

If conn.State = adStateOpen Then conn.Close

Set conn = Nothing

End If

End Sub

1. ADODB.Connection

ADODB.Connectionとは、VBA からデータベースへ接続するためのオブジェクトです。

VBA で MySQL や SQL Server、Access、Oracle など外部データベースに接続するときは、必ずこの ADODB.Connection を使います。

ADODB.Connection は以下のようなVBAとデータベースを繋げる際の処理で利用します。

1. データベースへ接続する

Set conn = New ADODB.Connection
connStr = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
          "Server=localhost;" & _
          "Database=testdb;" & _
          "User=testuser;" & _
          "Password=yourpassword;" & _
          "Option=3;"
conn.Open connStr

Set conn = New ADODB.Connection

connStr = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _

"Server=localhost;" & _

"Database=testdb;" & _

"User=testuser;" & _

"Password=yourpassword;" & _

"Option=3;"

conn.Open connStr

2. SQL を実行する（SELECT、INSERT、UPDATE、DELETEなど）

Set rs = conn.Execute("SELECT * FROM table1")

1	Set rs = conn.Execute("SELECT * FROM table1")

3. トランザクション処理（BeginTrans / CommitTrans / RollbackTrans）

conn.BeginTrans
' SQL 実行
conn.CommitTrans

conn.BeginTrans

' SQL 実行

conn.CommitTrans

4. 接続を閉じる

conn.Close

1	conn.Close

2. ADODB.Recordset

ADODB.Recordset（レコードセット）とは、データベースの SELECT 結果（複数行・列のデータ）を保持する入れ物です。主に以下の用途で利用します。

1. SQL の SELECT 結果を受け取る

Set rs = conn.Execute("SELECT * FROM t_0001")

1	Set rs = conn.Execute("SELECT * FROM t_0001")

2. レコード行を順に読みこむ

Do Until rs.EOF
    Debug.Print rs.Fields(0).Value
    rs.MoveNext
Loop

Do Until rs.EOF

Debug.Print rs.Fields(0).Value

rs.MoveNext

Loop

3. レコード数・フィールド数の取得

rs.Fields.Count
rs.RecordCount

1 2	rs.Fields.Count rs.RecordCount

4. 列名やインデックスでアクセス

rs.Fields("name").Value
rs.Fields(0).Value   '// 0がレコードの中での先頭項目

1 2	rs.Fields("name").Value rs.Fields(0).Value '// 0がレコードの中での先頭項目

5. 終了処理

rs.Close

rs.Close

接続できない場合

MySQLに接続できない場合、多くの原因は接続文字列にあります。以下の確認を行ってください。

ドライバ名（Driver=…）が正しいか。
パスワードは正しいか。
MySQLのコマンドラインでの接続文字列は「mysql -u testuser -pyourpassword testdb」のように、パスワード部分を示す”-p”と実際のパスワード”yourpassword”がスペースなしで「-pyourpassword」のようになっています。VBAのパスワードに”p”が誤って付いていないか確認してください。
ポート 3306 が開いているか。
MySQL のユーザー権限が正しいか。
認証方式（MySQL 8 の sha2-password）でエラーが出ていないか。
文字化けをする場合は文字コード設定が合っているか（utf8mb4 推奨）

上のサンプルではport（ポート番号）やCharset（文字コードセット）などを書いていませんが、以下のように追加すると問題が解決することもあります。

conn.Open _
    "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
    "Server=192.168.1.10;" & _
    "Port=3306;" & _
    "Database=testdb;" & _
    "User=testuser;" & _
    "Password=yourpassword;" & _
    "Charset=utf8mb4;" & _
    "Connection Timeout=15;"

conn.Open _

"Driver={MySQL ODBC 8.0 Unicode Driver};" & _

"Server=192.168.1.10;" & _

"Port=3306;" & _

"Database=testdb;" & _

"User=testuser;" & _

"Password=yourpassword;" & _

"Charset=utf8mb4;" & _

"Connection Timeout=15;"

基本的な接続方法

1. ADODB.Connection

2. ADODB.Recordset

接続できない場合

関連記事