Excel VBA ADOを使用して外部データベースのレコードを参照する

スポンサーリンク

ADOを使用して外部データベースのレコードを参照する方法

ActiveX Data Objects【アクティブエックスデータオブジェクツ】(以下ADO)を使用して外部データベースのレコードを参照するには、Recordset【レコードセット】オブジェクトのOpen【オープン】メソッドを使用します。

レコードを参照するRecodsetオブジェクトの主なメソッド

メソッド 内容
Recordset.Open
【レコードセットオープン】
指定したテーブルのレコードを参照します。
CopyFromRecordset
【コピーフロームレコードセット】
Recordsetオブジェクト格納されたレコードをコピーして
指定したワークシートに貼り付けます。
Recordset.Close
【レコードセットクローズ】
外部データベースのレコードの参照を切断します。

Recordset.Open【レコードセットオープン】メソッド

Recordset.Open【レコードセットオープン】メソッドを実行すると引数Source【ソース】にテーブル名を指定した場合は、すべてのレコード、SELECT【セレクト】文を指定した場合は、抽出されたレコードが格納されたRecordset【レコードセット】オブジェクトが開いて
カーソルがあるレコード(カレントレコード)を参照できるようになります。

Recordset.Open【レコードセットオープン】メソッドの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト変数.
Open([Source, ActiveConnection, CursorType, LockType, Options])

  • オブジェクト変数(省略可)
    Recordsetオブジェクトのインスタンスが格納されたオブジェクト変数を指定します。
  • Source【ソース】(省略可)
    参照したいテーブル名、またはSQLのSELECT【セレクト】文を指定します。
  • ActiveConnection【アクティブコネクション】(省略可)
    外部データベースへの接続が確立されているConnection【コネクション】オブジェクトを指定します。
  • CursorType【カーソルタイプ】(省略可)
    レコードを参照するカーソルの種類をCursorTypeEnum列挙型の定数で指定します。
CursorTypeEnum列挙型の定数
定数 内容
adOpenUnspecified
【アンスペシーファイド】
‐1 カーソルの種類を指定しない
adOpenForwardOnly
【フォワードオンリー】
(既定値)
0 前方専用カーソル。
レコードを参照する方向が
レコードセットの前方
(末尾方向)のみである点以外は
静的カーソルと同じです。
adOpenKeyset
【キーセット】
1 キーセットカーソル。
ほかのユーザーによって追加された
レコードは表示されず
ほかのユーザーによって削除された
レコードにアクセスできない点以外は
動的カーソルを同じです。
adOpenDynamic
【ダイナミック】
2 動的カーソル。
ほかのユーザーによる
レコードの追加、変更削除を確認できます。
すべての動作が許可されます。
adOpenStatic
【スタッティック】
3 静的カーソル。
ほかのユーザーによるレコードの
追加、変更、削除は表示されません。
データを検索したり、レポートを作成したり
するときに指定します。
  • LookType【ロックタイプ】(省略可)
    複数のユーザーによって同じテーブルが操作されたときのロックの種類をLookTypeEnum列挙型の定数で指定します。
LookTypeEnum列挙型の定数
定数 内容
adLockUnspecified
【アンスペシーファイド】
‐1 ロックの種類を指定しない
※ADOのバージョン6.1では指定できません。
adLockReadOnly
【リードオンリー】(既定値)
1 読み取り専用。データの更新はできません。
adLockPessimistic
【ペスミステイク】
2 レコード単位の排他的ロック。
編集直後のレコードをロックします。
adLockOptimistic
【オプトミステイク】
3 レコード単位の共有的ロック。
RecordsetオブジェクトのUpdateメソッドを
呼び出したときだけレコードをロックします。
adLockBatchOptimistic
【バッチオプトミステイク】
4 共有的バッチ更新。
バッチ更新モード時のみ指定可能です。
  • Options【オプションズ】(省略可)
    引数Sourceに指定した内容の種類をCommandTypeEnum列挙型の定数で指定します。
CommandTypeEnum列挙型の定数
定数 内容
adCmdUnspecified
【アンスペシーファイド】
-1 種類を指定しません。
adCmdText
【テキスト】
1 SQL文
adCmdTable
【テーブル】
2 テーブル名
adCmdStoredProc
【ストアドプロック】
4 ストアドプロシージャ名
adCmdUnknown
【アンクノン】(既定値)
8 不明
adCmdFile
【ファイル】
256 保存されたRecordsetのファイル名
adCmdTableDirect
【テーブルダイレクト】
512 列がすべて返されるテーブル名

CopyFromRecordset【コピーフロームレコードセット】メソッド

Recordset【レコードセット】オブジェクトに格納されているすべてのレコードをワークシートにコピーするにはRange【レンジ】オブジェクトのCopyFromRecordset【コピーフロームレコードセット】メソッドを使用します。

CopyFromRecordset【コピーフロームレコードセット】メソッドの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト.CopyFromRecordset( Data[, MaxRows, MaxColumns])

  • オブジェクト(必須)
    レコードの貼付け先のセル範囲の左上端のセルをRange【レンジ】オブジェクトで指定します。
  • Data【データ】(必須)
    レコードが格納されているRecordset【レコードセット】オブジェクト変数を指定します。
  • MaxRows【マックスロウズ】(省略可)
    ワークシートにコピーするレコードの最大数を指定します。省略した場合はすべてのレコードをコピーします。
  • MaxColumns【マックスカラムス】(省略可)
    ワークシートにコピーするフィールドの最大数を指定します。省略した場合はすべてのフィールドをコピーします。

Recordset.Close【レコードセットクローズ】メソッド

外部データベースのレコードの参照を切断するには、Recordset【レコードセット】オブジェクトのClase【クローズ】メソッドを使用します。Recordset【レコードセット】オブジェクトのOpen【オープン】メソッドで外部データベースのレコードを参照した後は必ず参照を切断します。

Recordset.Close【レコードセットクローズ】メソッドの書式と設定値の説明

オブジェクト変数.Close:Set オブジェクト変数 = Nothing

接続時に使用したRecordset【レコードセット】オブジェクトのインスタンスが格納されているオブジェクト変数に対してClose【クローズ】メソッドで接続を切断し、そのオブジェクト変数にNothing【ナッシング】を代入して使用していたメモリー領域を解放します。

※外部データベース接続切断のConnection.Close【コネクションクローズ】メソッドの実行前(コードの前)にこのコードを記述します。


Accessのテーブルのすべてのレコードをワークシートに読み込む例

Accessのテーブル

コードと解説

Sub レコード読み込み()
Dim コネクション As New ADODB.Connection
Dim レコード As New ADODB.Recordset
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
レコード.Open Source:="商品マスター", ActiveConnection:=コネクション
Range("A1").CopyFromRecordset Data:=レコード
レコード.Close: Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub
2行目【Dim コネクション As New ADODB.Connection】
Dim【デム】ステートメントでNew【ニュー】キーワード使用して外部データベースとの接続を確立するConnection【コネクション】オブジェクトのインスタンスを生成してオブジェクト変数「コネクション」に格納します。


3行目【Dim レコード As New ADODB.Recordset】
Dim【デム】ステートメントでNew【ニュー】キーワード使用して外部データベースのレコードを参照するRecordset【レコードセット】オブジェクトのインスタンスを生成してオブジェクト変数「レコード」に格納します。


4行目~6行目【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】

Connection【コネクション】オブジェクトのOpen【オープン】メソッドを使用して外部データベースの接続をします。


7行目【レコード.Open Source:=”商品マスター”, ActiveConnection:=コネクション】
Recordset【レコードセット】オブジェクトのOpen【オープン】メソッドを使用して外部データベースのレコードを参照します。


8行目【Range(“A1”).CopyFromRecordset Data:=レコード】
CopyFromRecordset【コピーフロームレコードセット】メソッドで参照したレコードをコピーしてA1セルに貼り付けます。


9行目【レコード.Close: Set レコード = Nothing】
Recordset【レコードセット】オブジェクトのClose【クローズ】メソッドで外部データのレコードの参照を切断します。外部データ接続の切断前に実行します。


10行目【コネクション.Close: Set コネクション = Nothing】
Connection【コネクション】オブジェクトのClose【クローズ】メソッドで外部データ接続を切断します。


実行結果


以上で、ADOを使用して外部データベースのレコードを参照する方法についての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告