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

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

■Access VBAのオブジェクトについて

MicrosoftからAccessの機能を利用するためのオブジェクト群が提供されていますが、下位オブジェクト（サブクラス）の種類が非常に多いため、最上位のApplicationオブジェクトに絞って、メソッドとプロパティの一覧を掲載します。
⇒詳細はMicrosoftのサイト（Office VBA リファレンス(Access)）を参照して下さい。

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

Applicationオブジェクトで提供されているメソッドおよび定義内容は、下表の通りです。

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

Applicationオブジェクトで提供されているプロパティおよび定義内容は、下表の通りです。

■Accessのオブジェクトを使用するための事前準備

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

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

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

1. Dim ac As Object

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

1. Set ac = New Access.Application
2. 'ここにAccessを使用した処理を記述
3. Set ac = Nothing

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

■Accessデータベースからデータを取得する処理

ApplicationオブジェクトのDBEngineプロパティにより、DAOのDBEngineオブジェクトを取得できますので、それ以降はDAOの機能を利用して処理を行うことができます。
ここではAccessのオブジェクトからDAOでデータベースをオープンし、レコードを読み出す処理について、詳細な手順を説明します。
⇒DAOの詳細については、Excel VBAでオブジェクト指向プログラミング（DAO編）を参照して下さい。

データベースのオープン

DBEngineオブジェクトのOpenDatabaseメソッドを実行し、Databaseオブジェクトを作成します。

1. dbe.OpenDatabase(Name, [Options], [ReadOnly], [Connect])

引数として指定できる項目は下表の通りですが、指定必須の引数はNameだけです。

レコードの読み出し

DatabaseオブジェクトのOpenRecordsetメソッドを実行し、Recordsetオブジェクトを作成します。

1. db.OpenRecordset(Name, [Type], [Options], [LockEdit])

引数として指定できる項目は下表の通りですが、指定必須の引数はNameだけです。

Accessデータベースから検索条件と一致したレコードを読み出し

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

行番号7でAccess.ApplicationオブジェクトのDBEngineプロパティから、DAOのDBEngineオブジェクトを取得しています。
行番号10でDatabaseオブジェクトのOpenRecordsetメソッドにSQLを指定し、SELECT文のWHERE句で条件式を指定しています。
引用符が入れ子になっていてわかり難いですが、WHERE句の部分は『WHERE 氏名=’○□　△☆’』といった形式です。

1. Dim sht, ac, dbe, db, rs As Object
2. Dim i As Integer
4. Private Sub Sample1()
5.     Set sht = ActiveSheet
6.     Set ac = New Access.Application
7.     Set dbe = ac.DBEngine
8.     Set db = dbe.OpenDatabase(“C:\work\住所録.mdb")
9.     For i = 1 To sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
10.         Set rs = db.OpenRecordset(“SELECT 氏名,郵便番号,住所 from 住所録 WHERE 氏名='" & sht.Cells(1, 1) & “'")
11.         If rs.EOF = False Then
12.             sht.Cells(1, 2) = rs.Fields(“郵便番号")
13.             sht.Cells(1, 3) = rs.Fields(“住所")
14.         End If
15.     Next i
16.     Set rs = Nothing
17.     db.Close
18.     Set db = Nothing
19.     Set dbe = Nothing
20.     Set ac = Nothing
21. End Sub

Accessデータベースから全レコードを読み出し

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

行番号7でAccess.ApplicationオブジェクトのDBEngineプロパティから、DAOのDBEngineオブジェクトを取得しています。
行番号9でDatabaseオブジェクトのOpenRecordsetメソッドにSQLを指定し、全レコードを読み出しています。
行番号10はExcelの機能で、RangeオブジェクトのCopyFromRecordsetメソッドを使用しています。

1. Dim sht, ac, dbe, db, rs As Object
2. Dim i As Integer
4. Private Sub Sample2()
5.     Set sht = ActiveSheet
6.     Set ac = New Access.Application
7.     Set dbe = ac.DBEngine
8.     Set db = dbe.OpenDatabase(“C:\work\住所録.mdb")
9.     Set rs = db.OpenRecordset(“SELECT 氏名,郵便番号,住所１,住所２,住所３ from 住所録")
10.     sht.Cells(1, 1).CopyFromRecordset rs
11.     Set rs = Nothing
12.     db.Close
13.     Set db = Nothing
14.     Set dbe = Nothing
15.     Set ac = Nothing
16. End Sub

Accessデータベースのテーブル名およびフィールド名を読み出し

Accessデータベースのテーブル名とフィールド名を一覧にしてExcelシートに出力するツールです。

行番号7でAccess.ApplicationオブジェクトのDBEngineプロパティから、DAOのDBEngineオブジェクトを取得しています。
行番号10でDatabaseオブジェクトのTableDefsプロパティでTableDefsコレクションを取得し、行番号11でTableDefオブジェクトのNameプロパティでテーブル名を取得しています。
また、行番号12でTableDefオブジェクトのFieldsプロパティでFieldsコレクションを取得し、行番号13でFieldオブジェクトのNameプロパティでフィールド名を取得しています。

1. Dim sht, ac, dbe, db, t, f As Object
2. Dim i As Integer
4. Private Sub Sample3()
5.     Set sht = ActiveSheet
6.     Set ac = New Access.Application
7.     Set dbe = ac.DBEngine
8.     Set db = dbe.OpenDatabase(“C:\work\住所録.mdb")
9.     i = 1
10.     For Each t In db.TableDefs
11.         sht.Cells(i, 1) = t.Name
12.         For Each f In t.Fields
13.             sht.Cells(i, 2) = f.Name
14.             i = i + 1
15.         Next f
16.     Next t
17.     db.Close
18.     Set db = Nothing
19.     Set dbe = Nothing
20.     Set ac = Nothing
21. End Sub

■Accessの定義情報を取得する処理

ApplicationオブジェクトのCurrentDataプロパティにより、CurrentDataオブジェクトを取得すると、下表のような定義情報のコレクションを取得することができます。
ここでは定義情報の一覧を表示する処理について、詳細な手順を説明します。

定義情報のコレクション

CurrentDataオブジェクトのプロパティにより、下表のコレクションを取得することができます。

AllTablesコレクションからテーブル名の一覧を取得

AllTablesコレクションからテーブル名を一覧にしてExcelシートに出力するツールです。

行番号8でAccess.ApplicationオブジェクトのCurrentDataプロパティから、CurrentDataオブジェクトを取得しています。
行番号10でCurrentDataオブジェクトのAllTablesプロパティでAllTablesコレクションを取得し、行番号11でTableオブジェクトのNameプロパティでテーブル名を取得しています(※)。

1. Dim sht, ac, db, t As Object
2. Dim i As Integer
4. Private Sub Sample4()
5.     Set sht = ActiveSheet
6.     Set ac = New Access.Application
7.     ac.OpenCurrentDatabase (“C:\work\住所録.mdb")
8.     Set db = ac.CurrentData
9.     i = 1
10.     For Each t In db.AllTables
11.         sht.Cells(i, 1) = t.Name
12.         i = i + 1
13.     Next t
15.     ac.CloseCurrentDatabase
16.     Set db = Nothing
17.     Set ac = Nothing
18. End Sub

(※)All～コレクションから取得する各要素（オブジェクト）については、AccessObjectという名称で下表のプロパティが定義されています。