SQL Server:ODBC接続する際にODBCデータソース(DSN)を作成せずにSQL Serverのデータベースに接続する(ODBCデータソースレス接続)方法

Accessなどでリンクテーブルやパススルークエリーを作成する際にDSNを作成せずに作成する方法。

通常DSNを作成し、そのDSNを指定してリンクテーブルやパススルークエリーを作成するが、DSNを使用すると、他のマシンから使用する際にいちいちDSNを作成しないとならない。

ファイルDSNを共有するという方法もあるが、共有フォルダを作成する必要があるなど面倒。

データソースレス接続を使用すれば、DSNを作成する必要がなく、接続に必要なODBCドライバさえPCにセットアップされていればよい。

ODBC接続されたリンクテーブルなどを含むmdbファイルを配布したり、他のマシンでの利用が前提となるようなAccessデータベースを作成する場合は非常に便利。

ODBCデータソースレス接続時のODBC接続文字列の構文(SQL Serverの例)
ODBCデータソースレスを使用したExcelのVBAサンプルプログラム
1. サンプルプログラムの実行前提条件
2. ExcelのVBAサンプルプログラム
ODBCデータソースレス接続に関する資料

ODBCデータソースレス接続時のODBC接続文字列の構文(SQL Serverの例)

ODBCデータソースレスで接続する際のODBC接続文字列の構文例を以下に記載する。

PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=<接続するサーバー名>;DATABASE=<参照するデータベース名>;UID=<ユーザーID>;PWD=<パスワード>;

基本的な記載例は以下の通り。

PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=サーバー名;DATABASE=データベース名;

以降のオプションについては、DSNを利用した通常の接続文字列で使用するオプション指定が可能。(全てのオプションについて確認はしていない)

また、「DRIVER={XXXX}」に指定するドライバー名は、データソース設定画面の[ドライバ]タブに表示される名前を記述すればよい。

SQL Serverだけでなく、Accessやテキストファイルなど、他のドライバについても同様に利用が可能。

ODBCデータソースレスを使用したExcelのVBAサンプルプログラム

以下にExcelでODBCデータソースレスでSQL Serverに接続してテーブルのデータをデバッグウィンドウに出力するVBAサンプルを記載する。

サンプルプログラムの実行前提条件

VBAのサンプルプログラムの実行前提条件を以下に記載しておく。

設定名	設定する値
サーバー名	MyServer
データベース名	northwind
参照テーブル名	運送会社
ユーザーID	sa
パスワード	password

ExcelのVBAサンプルプログラム

Northwindデータベースの運送会社テーブルの運送会社名をデバッグウィンドウに表示するExcelのVBAサンプルプログラムを以下に記載する。

Sub sample()
Dim con As Object, rec As Object
    Set con = CreateObject("ADODB.Connection")
    Set rec = CreateObject("ADODB.Recordset")
    con.Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=MyServer;" _
                & "DATABASE=northwind;UID=sa;PWD=password;"
    rec.Open "運送会社", con, 2, 3
        Do Until rec.EOF
            Debug.Print rec("運送会社")
            rec.MoveNext
        Loop
End Sub