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
上記のコードを実行するとシートは下記のように更新されます。