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

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

■Data Access Objects(DAO)とは

Data Access Objects(DAO)はMicrosoftから提供されている、データベースへのアクセスに特化したオブジェクトで、元々はAccessのデータベースにアクセスするためのオブジェクトとして開発されたものです。
⇒詳細はMicrosoftのサイト（Data Access Objects(DAO)）を参照して下さい。

Office2000に搭載されていたDAO 3.6以降はアップグレードされていませんので、新たにプログラムを作成される場合は、ActiveX Data Objects(ADO)を使用した方が良いと思います。
⇒ADOの詳細については、Excel VBAでオブジェクト指向プログラミング（ADO編）を参照して下さい。

DAOのオブジェクト

DAOに含まれるオブジェクトは下表の通りです。
⇒下表では各オブジェクトの階層構造（依存関係）のみを表し、説明は煩雑になるため、割愛しました。

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

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

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

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

1. Dim dbe As Object

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

1. Set dbe = New dao.DBEngine
2. 'ここにDAOを使用した処理を記述
3. Set dbe = Nothing

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

■DAOの使用方法

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列に住所をセットするツールです。

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

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

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

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

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

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

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

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

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

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