VBAオジサンのらくがき帳

ODBCデータソースからDBのデータを取得する

2021-07-31 00:00:00

データベースからデータを取得する際によく使われる、ODBCデータソースを使った方法を紹介します。

会社によっては共有DBがあらかじめPCにシステムDNSとして登録されているケースも多いと思います。登録されているデータソースはODBCデータソースアドミニストレーターで確認できます。32bit版と64bit版があり、Excelのバージョンに合わせて設定が必要です。

自分で追加する場合はユーザーDNSとして登録するのが簡単だと思います。今回のサンプルでは「MyDB」という名前でユーザーDNSを登録しています。

あらかじめDBには下のデータを登録しています。

create table T1 (id int, text varchar(10));
insert into T1 values (1, 'hello');
insert into T1 values (2, 'goodbye');

データの取得は下のコードでできます。事前バインディングを使用する場合は「Microsoft ActiveX Data Objects *.* Library」を参照設定に追加しておく必要があります。 ConnectionString にはODBSのDSN(データソースネーム)とID、パスワードを指定します。(UID、PWD以外に指定できるパラメータについては利用するDBの種類によって違いがあります。ConnectionStringにはDNS指定以外にもいろいろな方法があります、他の方法についても次回まとめて紹介したいと思います。)

Dim conn As Object 'ADODB.Connection
Dim rs As Object 'ADODB.Recordset
Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = "DSN=MyDB;UID=***;PWD=***"
conn.Open
Set rs = conn.Execute("select * from T1")
Do Until rs.EOF
    Debug.Print rs.Fields(0).Value & "," & rs.Fields(1).Value
    rs.MoveNext
Loop
rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

取得したデータをそのままシートに張り付ける場合はRangeオブジェクトのCopyFromRecordsetメソッドを使用するのが便利です。(上記のDo Untilループのところを下に置き換える。)

If Not rs.EOF Then
    Range("A1").CopyFromRecordset rs
End If

上記のコードを実行するとシートは下記のように更新されます。