ExcelWork.info

Excel(エクセル)のマクロ・VBA・関数・操作などのメモ

データベース(Access 等)に接続(ADO)

      2017/10/23

ADO で、データベースに接続

外部データベースを操作する方法のひとつに ADO(AcriveX Data Objects)があります。
使用するには、ライブラリファイルへの参照設定を行う方法と、CreateObject 関数 を使う方法があります。


Connection オブジェクト・Recordset オブジェクト


【書式】

<CreateObject 関数を使用する場合>
Dim cn As Object   …… Connection オブジェクト
Dim rs As Object   …… Recordset オブジェクト

Set cn = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

<参照設定を行う場合>
Dim cn As New ADODB.Connection   …… Connection オブジェクト
Dim rs As New ADODB.Recordset    …… Recordset オブジェクト


参照設定を行うライブラリファイルについて

Excel2003 以前では、Microsoft ActiveX Data Objects 2.8 Library を選択します。
Excel2007 以降では、Microsoft ActiveX Data Objects バージョン番号 Library を選択します。
※ 複数のバージョンがあります。特に必要がなければ最新バージョンを選択すると良いでしょう。
参照設定の方法については、ファイルシステムオブジェクト(FileSystemObject)の使い方 を参照してください。

CreatObject 関数 を使用する場合は、参照設定の必要はありませんが、VBE で、自動メンバ表示等のコーディング支援機能が使用できません。また、マクロの実行速度も遅くなります。



データベースへ接続(Open メソッド)


【書式】

object.Open[ ( ConnectionString, UserID, Password, Options ) ]


引数・戻り値

  • object  ・・・  対象となる Connection オブジェクトを指定します。
  • ConnectionString  ・・・  接続情報を含む文字列型(String)の値(「変数名=設定値;」)を指定します(省略可)。
    ConnectionString 内で使用する変数について】
    ■ Provider:接続するデータベースの種類を指定します。

    データベース 設定する文字列
    Access 2007 Microsoft.ACE.OLEDB.12.0;
    Access 2003 以前 Microsoft.Jet.OLEDB.4.0;
    Oracle MSDAORA;
    SQLServer SQLOLEDB;
    DB2 IBMDADB2;
    ODBC 接続 MSDASQL;
    Excel 2007 Microsoft.ACE.OLEDB.12.0;
    Excel 2003 以前 Microsoft.Jet.OLEDB.4.0;
    CSV ファイル Microsoft.Jet.OLEDB.4.0;

    ■ Data Source:接続するデータベースの場所(パス・ファイル名)を指定します。
      ※ CSV ファイルの場合、ファイルの存在するパスのみを指定します(最後は、\)。

    ■ Extended Properties:ExcelやCSVファイルに接続するときに使用します。

    データベース 設定する文字列
    Excel 2007 Excel 12.0;
    Excel 2003 以前 Excel 8.0;
    CSV ファイル Text;

  • UserID  ・・・  データベースに接続するときに使用するユーザー名を指定します(省略可)。
  • Password  ・・・  データベースに接続するときに使用するパスワードを指定します(省略可)。
  • Options  ・・・  このメソッドが、接続が確立された後 (同期) と確立される前 (非同期) のいずれで終了するかを決定する ConnectOptionEnum 値を指定します。
    定数 内容
    adAsyncConnect 16 非同期で接続します。ConnectComplete イベントは、接続可能かどうかを決定するために使用されることがあります。
    adConnectUnspecified -1 既定値です。同期で接続します。



データベースのレコードを参照する(Open メソッド)


【書式】

object.Open[ ( Source, ActiveConnection, CursorTypes, LockType, Options ) ]


引数・戻り値

  • object  ・・・  対象となる Recordset オブジェクトを指定します。
  • Source  ・・・  テーブル名、クエリ、SQL文などを指定します。
  • ActiveConnection  ・・・  Connection オブジェクト を指定します。
  • CursorTypes  ・・・  レコードを参照するカーソルの種類を CursorTypeEnum クラス の定数(またはその値)を指定します。
    定数 内容
    adOpenForwardOnly 0 前方専用カーソル。レコードのスクロール方向が前方向に限定されていることを除き、静的カーソルと同じ働きをします。(既定値)
    adOpenKeyset 1 キーセットカーソルを使います。ほかのユーザーが追加したレコードは表示できない点を除き、動的カーソルと同じく、自分の Recordset からほかのユーザーが削除したレコードはアクセスできません。ほかのユーザーが変更したデータは表示できます。
    adOpenDynamic 2 動的カーソルを使います。ほかのユーザーによる追加、変更、および削除を確認できます。プロバイダがブックマークをサポートしていない場合を除き、Recordset 内でのすべての動作を許可します。
    adOpenStatic 3 キーセットカーソルを開きます。データの検索またはレポートの作成に使用するための、レコードの静的コピーです。ほかのユーザーによる追加、変更、または削除は表示されません。
    adOpenUnspecified -1 カーソルの種類を指定しません。

  • LockType  ・・・  省略可能です。プロバイダが Recordset を開くときに使うロック(同時作用)の種類を LockTypeEnum クラス の定数(またはその値)を指定します。
    定数 内容
    adLockReadOnly 1 読み取り専用。データの変更は出来ません(既定値)。
    adLockPessimistic 2 レコード単位の排他的ロック。編集直後のデータソースでレコードをロックします。
    adLockOptimistic 3 レコード単位の共有的ロック。Update メソッド を呼び出した場合にのみ、レコードをロックします。
    adLockBatchOptimistic 4 共有的バッチ更新。バッチ更新モードの場合にのみ指定できます。
    adLockUnspecified -1 ロックの種類を指定しません。

  • Options  ・・・  引数 Source に指定した内容の種類を CommandTypeEnum クラス の定数(またはその値)を指定します(AND 演算子で組み合わせて使用可)。
    定数 内容
    adCmdText 1 SQL 文
    adCmdTable 2 テーブル名
    adCmdStoredProc 4 ストアド プロシージャ名
    adCmdUnknown 8 不明(既定値)
    adCmdFile 256 永続的に保存された Recordset のファイル名
    adCmdTableDirect 512 列がすべて返されるテーブル名
    adCmdUnspecified -1 コマンドタイプ引数を指定しません




データベース(Access)へ接続する例

サンプルVBAソース その1


実行結果

ADO Access (参照設定)



データベース(Access)へ接続する例(CreateObject 関数を使用)

サンプルVBAソース その2


実行結果

ADO Access (CreateObject 関数)



 - データベース , , , , , , , ,

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  関連記事

Update メソッド(ADO)
レコードの更新(ADO)Update メソッド

データベースのレコードを更新する Update メソッド カレントレコードの内容 …

Excelに接続する(ADO)
Excelファイルに接続(ADO)

ADOでExcelファイルに接続 ADO を使用してExcelファイルを読み込み …

RecordCount プロパティ(ADO)
Recordset オブジェクト内のレコード数(ADO)RecordCount プロパティ

RecordCount プロパティ Recordset オブジェクトのレコード数 …

ADO Fields コレクション
フィールド名(ADO)Fields コレクション

Fields コレクション Recordset オブジェクト内の1つ以上の列(フ …

Recordsetオブジェクト内のカーソル移動
カレントレコードの移動(ADO)MoveFirst、MoveLast、MoveNext、MovePrevious、Move

MoveFirst メソッド、MoveLast メソッド、MoveNext メソ …

Sort プロパティ(ADO)
レコードを並べ替える(ADO)Sort プロパティ

データベースのレコードを並べ替える Sort プロパティ 【書式】 object …

Command.Execute Insert Into 文
SQL文を実行する(ADO)Execute メソッド

Command オブジェクトの Execute メソッドで、SQL文を実行 Co …

ADOX テーブルの作成
テーブルの作成・削除/フィールドの作成・削除(ADOX)

ADOX を使用してテーブル・フィールド(列)を作成・削除 Table オブジェ …

ADOX データベース作成1
データベースを作成する(ADOX)

ADOX を使用してデータベース(Access)を作成 Catalog オブジェ …

SQL実行結果
データベース(Oracle)に接続する(ODBC を使用)

ODBC接続(ADO) ADOで、ODBC(Open Database Conn …