ExcelのVBAでADOを使ってデータベースに接続し、テーブルやクエリー(SQL文を含む)をレコードセットで取得してCopyFromRecordsetメソッドを使用してワークシートのアクティブセルに貼り付けるサンプルプログラムを覚書しました。
以下 CopyFromRecordset を使用した一番簡単なサンプルプログラムを記述。
サンプルプログラム概要
このサンプルプログラムは、Accessに添付されているサンプルデータベース"Northwind.mdb"の"社員"テーブルのデータを全件取得してワークシートに貼り付けるサンプルプログラムになっています。
サンプルプログラム「GetRecordset_sample01」は、"Microsoft ActiveX Data Objects"を参照設定しない場合の例です。
参照設定しない場合、コード補完機能である「IntelliSense 」(インテリセンス)が機能しないので、使用するプロパティやメソッドに詳しくない場合に不便になります。
一方、「GetRecordset_sample01」は、参照設定している場合の例で「IntelliSense 」が動作するため、プログラムの入力や使えるプロパティ、メソッドが自動的に表示されるます。
VBAにあまり詳しくない人には便利な反面、他のPCでプログラムを実行する場合にそのPCに同じライブラリが入っていないとライブラリ参照エラーになって動作しないという問題が発生します。
このメリット、デメリットを考えてどちらを使うか判断してください。
Microsoft ActiveX Data Objectsを参照設定しない場合のサンプルプログラム
Sub GetRecordset_sample01()
Dim con, rec, i As Integer
'ADOのConnectionオブジェクトを作成
Set con = CreateObject("ADODB.Connection")
With con
'接続文字列:接続するデータベースによって異なる
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" _
& ";Data Source=C:¥Program Files¥Microsoft Office¥Office¥Samples¥Northwind.mdb"
.Open
End With
'ADOのRecordsetオブジェクトを作成
Set rec = CreateObject("ADODB.Recordset")
'Northwind.mdbの社員テーブルを開く
rec.Open "社員", con
'フィールド名をセルに書き出す必要がなければ For ～ Next の行までコメントアウトし、
'ActiveCell.Offset(1, 0).CopyFromRecordset rec の記述を ActiveCell.CopyFromRecordset rec
'に変更する
For i = 0 To rec.Fields.Count - 1
ActiveCell.Offset(0, i).Value = rec(i).Name
Next i
ActiveCell.Offset(1, 0).CopyFromRecordset rec
End Sub
Microsoft ActiveX Data Objectsを参照設定する場合サンプルプログラム
他のPCで実行する可能性がある場合は参照設定しないGetRecordset_sample01を使用したほうがいいかと思います。
Sub GetRecordset_sample02()
Dim con As New ADODB.Connection, rec As New ADODB.Recordset, i As Integer
'ADOのConnectionオブジェクトを作成
With con
'接続文字列:接続するデータベースによって異なる
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" _
& ";Data Source=C:¥Program Files¥Microsoft Office¥Office¥Samples¥Northwind.mdb"
.Open
End With
'ADOのRecordsetオブジェクトを作成
'Northwind.mdbの社員テーブルを開く(SQL文を指定しても良い)
rec.Open "社員", con
'フィールド名をセルに書き出す必要がなければ For ～ Next の行までコメントアウトし、
'ActiveCell.Offset(1, 0).CopyFromRecordset rec の記述を ActiveCell.CopyFromRecordset rec
'に変更する
For i = 0 To rec.Fields.Count - 1
ActiveCell.Offset(0, i).Value = rec(i).Name
Next i
ActiveCell.Offset(1, 0).CopyFromRecordset rec
End Sub
CopyFromRecordsetを使ったサンプルプログラム補足
CopyFromRecordsetは、レコードセットをべたっと貼り付けるだけなので簡単で早い反面、レコードごとやフィールドごとに何かしら加工をしたい場合には不向きです。
例えば、ある列の値に特定のコードを付加したいとか、決められた表示形式に変換してセルに書き出させたい場合などは、CopyFromRecordset は向いていません。
そのような処理が必要になった場合は、「Do Until rec.EOF」を使って1行ずつ書きださせる方式を選択したほうが良いかと思います。
コメント