Excel VBAでオブジェクト指向プログラミング（ADO編）

2021-10-122024-01-05

■ActiveX Data Objects(ADO)とは

ActiveX Data Objects(ADO)はMicrosoftから提供されている、データベースへのアクセスに特化したオブジェクトで、さまざまなデータベースに対応していることが最大の特徴です。
Microsoftのドキュメントには「Microsoft ActiveX データオブジェクト (ADO) を使用すると、クライアントアプリケーションは、OLE DB プロバイダーを介してさまざまなソースからのデータにアクセスして操作できます」と記載されており、データベースに接続した後は、データベースの種類を意識することなく、共通のインターフェースで操作できます。
⇒詳細はMicrosoftのサイト（ActiveX Data Objects (ADO)）を参照して下さい。

ADOのオブジェクト

ADOに含まれるオブジェクトは下表の通りですが、本稿ではConnectionオブジェクトとRecordsetオブジェクトに絞って説明します。

■Connectionオブジェクトとは

Connectionオブジェクトは、データソースとの接続に関するオブジェクトで、提供されているメソッドおよびプロパティは以下の通りです。

Connectionオブジェクトのメソッド

Connectionオブジェクトのプロパティ

■Recordsetオブジェクトとは

Recordsetオブジェクトは、レコードセット（レコード（行）の集合）を扱うオブジェクトで、提供されているメソッドおよびプロパティは以下の通りです。

Recordsetオブジェクトのメソッド

Recordsetオブジェクトのプロパティ

■ADOを使用するための事前準備

ADOを使用するための事前準備は、以下の3ステップです。
⇒オブジェクトを使用するための事前準備とオブジェクトブラウザによる調査方法については、Excel VBAでオブジェクト指向プログラミング（事前準備編）を参照して下さい。

①ライブラリの参照設定
Visual Basic Editor(VBE)のファイルメニューから[ツール]-[参照設定]を選択し、参照設定ダイアログでMicrosoft ActiveX Data Objects x.x Libraryのチェックボックスにチェックを入れ、[OK]ボタンを押下。

（補足説明）
『x.x』の部分だけ異なる項目が複数表示されます。
筆者は深く考えずに一番数字の大きいものを選んでいますが、Microsoftのドキュメントを見ると、ADOとWindowsの対応関係が下表のようになっているようなので、Windowsのバージョンに合ったライブラリを選択するのが本来の姿かと思います。

②オブジェクト変数の定義
プログラムの宣言部でオブジェクト変数を定義。

1. Dim cn, rs As Object

③インスタンスの生成
プログラムの処理部でインスタンスを生成。

1. Set cn = New ADODB.Connection
2. Set rs = New ADODB.Recordset
3. 'ここにADOを使用した処理を記述
4. Set rs = Nothing
5. Set cn = Nothing

（参考）「Set cn = New ADODB.Connection」の部分を「Set cn = CreateObject(“ADODB.Connection")」、「Set rs = New ADODB.Recordset」の部分を「Set rs = CreateObject(“ADODB.Recordset")」と記述すれば、ステップ①（ライブラリの参照設定）は不要です。

■ADOの使用方法

ADOでデータソースに接続し、データソースからレコードを読み出す処理について、詳細な手順を説明します。

データソースへの接続

ConnectionオブジェクトのOpenメソッドを実行し、データソースに接続します。
引数として指定する「接続文字列(Connection String)」の中には色々な項目がありますが、必須項目はProviderとData Sourceの2つです。

1. cn.Open “Provider=プロバイダ名;Data Source=’データソースのパス’;"

プロバイダ名はデータソースの種類毎に決っており、主なものは下表の通りです。

(※1)「Extended Properties="Excel 12.0;HDR=Yes;IMEX=1″」のような記述を追加要。
(※2)「Extended Properties="text;HDR=Yes;FMT=Delimited"」のような記述を追加要。

レコードの読み出し

さまざまな方法がありますが、RecordsetオブジェクトのOpenメソッドおよびExecuteメソッドでSQL(SELECT文)を使用した例を次項で紹介します。

RecordsetオブジェクトのOpenメソッドによる読み出し

ExcelシートのA列に格納された氏名を基に、Accessの住所録を検索し、ExcelシートのB列に郵便番号、C列に住所をセットするツールです。

行番号10でRecordsetオブジェクトのOpenメソッドにSQLを指定し、SELECT文のWHERE句で条件式を指定しています。
引用符が入れ子になっていてわかり難いですが、WHERE句の部分は『WHERE 氏名=’○□　△☆’』といった形式です。

1. Dim sht, cn, rs As Object
2. Dim i As Integer
4. Private Sub Sample1()
5.     Set sht = ActiveSheet
6.     Set cn = New ADODB.Connection
7.     cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’C:\work\住所録.mdb’;"
8.     Set rs = New ADODB.Recordset
9.     For i = 1 To sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
10.         rs.Open “SELECT 氏名,郵便番号,住所 from 住所録 WHERE 氏名='" & sht.Cells(i, 1) & “'", cn, adOpenKeyset, adLockReadOnly
11.         If rs.EOF = False Then
12.             sht.Cells(i, 2) = rs.Fields(“郵便番号")
13.             sht.Cells(i, 3) = rs.Fields(“住所")
14.         End If
15.     Next i
16.     Set rs = Nothing
17.     cn.Close
18.     Set cn = Nothing
19. End Sub

RecordsetオブジェクトのExecuteメソッドによる読み出し

単純にAccessの住所録を全件読み出し、Excelシートに出力するツールです。

行番号8でRecordsetオブジェクトのExecuteメソッドにSQLを指定し、全レコードを読み出しています。
行番号9はExcelの機能で、RangeオブジェクトのCopyFromRecordsetメソッドを使用しています。

1. Dim sht, cn, rs As Object
3. Private Sub Sample2()
4.     Set sht = ActiveSheet
5.     Set cn = New ADODB.Connection
6.     cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’C:\work\住所録.mdb’;"
7.     Set rs = New ADODB.Recordset
8.     Set rs = cn.Execute(“SELECT 氏名,郵便番号,住所 FROM 住所録")
9.     sht.Cells(1, 1).CopyFromRecordset rs
10.     Set rs = Nothing
11.     cn.Close
12.     Set cn = Nothing
13. End Sub

ADOXを使用してテーブルとフィールドの一覧を表示するExcelツール

ADOX(ActiveX Data Objects Extensions for Data Definition Language and Security)はMicrosoftから提供されている、「ADOのオブジェクトとプログラミングモデルを拡張した」オブジェクトです。
⇒詳細はMicrosoftのサイト（ADOX(ActiveX Data Objects Extensions for Data Definition Language and Security)）を参照して下さい。

ここではデータベースとフィールドの一覧を表示するためのサンプルプログラムをご紹介します。

①ライブラリの参照設定
Visual Basic Editor(VBE)のファイルメニューから[ツール]-[参照設定]を選択し、参照設定ダイアログで
Microsoft ADO Ext. x.x for DDL and Securityのチェックボックスにチェックを入れ、[OK]ボタンを押下。

（参考）サンプル・プログラムの行番号10「Set ct = New ADOX.Catalog」の部分を「Set ct = CreateObject(“ADOX.Catalog")]と記述すれば、ライブラリの参照設定は不要です。

②サンプル・プログラム
行番号13でCatalogオブジェクトのTablesプロパティでTablesコレクションを取得し、行番号15でTableオブジェクトのNameプロパティでテーブル名を取得しています。
また、行番号18でRecordsetオブジェクトのFieldsプロパティでFieldsコレクションを取得し、行番号19でFieldオブジェクトのNameプロパティでフィールド名を取得しています。

1. Dim sht, ct, cn, rs, t, f As Object
2. Dim strSQL As String
3. Dim i As Integer
5. Private Sub Sample3()
6.     Set sht = ActiveSheet
7.     Set cn = New ADODB.Connection
8.     cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=’C:\work\住所録.mdb’;"
9.     Set ct = New ADOX.Catalog
10.     ct.ActiveConnection = cn
11.     Set rs = New ADODB.Recordset
12.     i = 1
13.     For Each t In ct.Tables
14.         If t.Type = “TABLE" Then
15.             sht.Cells(i, 1) = t.Name
16.             strSQL = “SELECT * FROM " & sht.Cells(i, 1)
17.             Set rs = cn.Execute(strSQL)
18.             For Each f In rs.Fields
19.                 sht.Cells(i, 2) = f.Name
20.                 i = i + 1
21.             Next f
22.         End If
23.     Next t
24.     Set rs = Nothing
25.     Set ct = Nothing
26.     cn.Close
27.     Set cn = Nothing
28. End Sub