Access VBA:AccessからMySQLにデータソース(DSN)を作成せずにODBCデータソースレス接続してSQLの結果を取得するサンプルプログラム

ODBCデータソースレス接続の概要

本来であればAccessのVBAからMySQLなどの他のデータベースに接続する場合には、Connectionオブジェクトに接続文字列を指定して目的のデータベースとのコネクションを確立します。

他のデータベースとの接続をODBCドライバ経由で行う場合は、[ODBC データソース アドミニストレータ]でデータソース(DSN)を作成し、接続文字列の中でそのDSN名を指定します。

この方法だと他のクライアントPCでこのプログラムを実行した場合に同じDSNが定義されていないとエラーになり実行ができません。

また、いちいちクライアントPCごとにDSNを作成するのは面倒です。

データソースレス接続を使用すれば、DSNを作る必要がなく使用するODBCドライバがインストールされていればどの環境でも動作させることができます。

この記事ではデータソースを作成せずに接続文字列に直接MySQLのODBCドライバを指定して接続する方法を使ったサンプルプログラムを紹介します。

MySQLへのデータソースレスで接続する際の接続文字列について

MySQLにデータソースレス接続する際の接続文字列の記述について説明します。

データソースレス接続文字列の書式は以下のとおりです。

画面の都合で折り返して表示しています。

Driver={ODBCドライバ名};DATABASE=接続先データベース名;PWD=パスワード;
PORT=ポート番号;SERVER=データベースサーバー名;STMT=SET NAMES 文字コード;UID=接続ユーザー名"

以下に接続文字列中の各値に関する補足を記載します。

ODBCドライバ名

{ODBCドライバ名}に指定するMySQLのODBCドライバ名は、"MySQL ODBC 3.51 Driver"です。

{ODBCドライバ名}に指定するODBCドライバ名の確認方法の詳細についてはこちらの記事を参照してください。

ポート番号

"ポート番号"は、MySQLの場合デフォルト値は"3306"です。

ただし、ポート番号は変更されている場合がありますのでMySQLの管理者に確認してください。

STMT

"STMT="の記述については、Access側の文字コード"Shift_JIS"を指定してください。

このShift_JISを指定しないと取得したデータがAccess側で文字化けします。

機種依存文字や全角の記号などを含むデータが保存されているようなDBに接続する場合は、"cp932"を指定してください。

接続先MySQLがサーバーなどの別のマシンにインストールされている場合

接続したいMySQLデータベースがサーバーなどのネットワーク上の他のマシンにインストールされている場合には、そのMySQLデータベースサーバーがリモート接続を許可しているか管理者に確認してください。

MySQLにデータソースレス接続でSQLの実行結果を取得するサンプルプログラム

以下にMySQLにデータソースレスで接続してSQL(SELECT)を実行し、実行結果をデバッグウィンドウに表示するサンプルプログラムを紹介します。

以下のサンプルプログラムでは、このプログラムを実行するPC上にMySQLがインストールされている環境となっていますのでサーバー名が"localhost"となっています。

サーバーなどの別のマシンの場合は、そのマシン名もしくはIPアドレスを指定してください。

サンプルプログラムでは接続するMySQLデータベースとして"mysql"データベースを指定し、"root"ユーザーで接続しています。

文字コードについては、"Shift_JIS"を表す"sjis"を指定しています。

Sub sample()
Dim con As New ADODB.Connection, Rec As New ADODB.Recordset

  con.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};DATABASE=mysql;" _
                         & "PWD=password;PORT=3306;SERVER=localhost;" _
                         & "STMT=SET NAMES sjis;UID=root"
  con.Open
  
  Rec.Open "select * from user", con
    Do Until Rec.EOF
       Debug.Print Rec(0)
       Rec.MoveNext
    Loop

End Sub

データソースレス接続まとめ

データソースレス接続文字列は、MySQLだけでなく他のデータベースサーバー(SQL ServerやORACLEなど)でも使用できます。

その際の"ODBCドライバ名"については、[ODBC データソース アドミニストレータ] ダイアログボックスの[ドライバ]タブに表示されているドライバ名を"{"でくくって指定すればよいです。

ただし、ドライバによって接続文字列で指定するオプションが異なりますので注意してください。

接続文字列で指定するオプションの確認については、基本的にはUDLファイルを用いて接続文字列を作成しDSNを指定している部分の記述を"Driver={ドライバ名}"に変更すればよいです。

データソースレス接続を使用した各種データベースへの接続はAccessのVBAだけでなくExcelのVBAやVBでも同じように使用することができます。