派遣で働くエンジニアのスキルアップを応援するサイト

PRODUCED BY RECRUIT

第4話 DAOとADOの違い【連載】実務で使えるAccessのコツ

f:id:itstaffing:20210803162809j:plain

データベース管理ソフトウェア、Accessを実務で使うときに「知っているとちょっと便利なコツ」について紹介するこのコラム。今回は、AccessVBAを使う方なら一度は考えるDAOとADOの違いと使い分けについて、記事公開時点(2021年8月)の最新の情報を踏まえて解説します。あなたのAccess力アップに役立ちますように。

前回のコラムを見逃した方はこちらからご覧ください。
第3話 マクロとVBAの違い

【筆者】今村 ゆうこさん
【筆者】今村 ゆうこさん
地方の非IT系企業に勤務し、業務アプリケーション開発を行うプログラマー。著書のイラストや図解も手掛けている。大学卒業後出身地へUターン就職し、2度の産休/育休を経て現在は小学生と保育園児の2人のこどもを抱えるワーキングマザー。主にVBAを使ってデータベースの情報を加工/展開するアプリを開発するほか、WordPressを使った個人ブログ(https://ateitexe.com/)で技術情報を発信している。著書に『Accessデータベース 本格作成入門』『Excel & Access連携 実践ガイド』(技術評論社)など。

リクルートスタッフィング

  DAOとADOってなんのこと?    

AccessVBAを使ってデータベースを操作しようとすると、DAOやADOという単語を耳にしませんか? これは一体、何なのでしょうか?

ざっくり言うと、どちらもデータベースへ接続・操作する手段のことです。Accessでは、どちらも利用することができます。

f:id:itstaffing:20210803162811j:plain

今回は、この2つについて学んでいきましょう。

  どう違うの?    

この2つは略称で、正式名称は以下のとおりです。

・DAO … Data Access Objects
・ADO … ActiveX Data Object

DAOはAccessで使用されるデータベースに特化した手段です。それに対してADOは、Accessをはじめ各社の主要なデータベースでも幅広く使える接続手段です。

DAOに関しては、Access開発当初からずっと使われてきた技術だったものの、Access2000よりADOが使えるようになったことで、一時期はADOが推奨される風潮があったようです。

しかし、Access2007よりDAOが新しくなったことから、現在では「古い手段」というイメージは払拭されています。

  どちらを使うべき?    

この記事が公開される2021年8月現在では、Accessにおいて、DAO/ADOどちらを使っても大きな差はありません。

DAOは元々Accessに特化した技術なのでADOよりも高速と言われていますが、パソコンのスペックもどんどん上がっているので、体感できるほどの差にはなりにくいと思われます。

Accessのみで完結するシステムならばDAO、Access以外でデータベースを使う・または今後その可能性がある場合はADO、という判断をしてもよいのではないでしょうか。

  テンプレート化して使うのがおすすめ    

データベースを操作するには、SQLというデータベースに問い合わせを行うための言語を使います。

VBAからデータベースを操作する際、SQLは目的によって内容を変えなくてはなりませんが、そのSQLを「処理する部分」は一定です。DAO/ADOそれぞれ、処理部分のみ切り分けたテンプレートを用意しておけば、SQLのみの変更でかんたんに利用することができます。

f:id:itstaffing:20210803162814j:plain

ただし、SQLでの問い合わせには大きく分けて「データベースから任意のレコードを抽出する」働きと「データベースの内容を変更する」働きがあるので、VBAで「処理する部分」はDAO/ADOそれぞれに、2種類ずつ必要になります。

特に「任意のレコードを抽出する」場合、レコードセットというオブジェクトが登場します。これは、データベースのレコードが集まった表形式のデータ構造で、Accessでテーブルやクエリをデータシートビューで開いた時と同じイメージのものをVBA上で保持、扱うことができます。

後述のコードで、任意のレコードの集合を「レコードセット」という形で取得し、それを展開する記述がありますので、注目してみてください。

それではここからはDAO/ADOのテンプレートとして使える、SQLを「処理する部分」のAccessVBAをご紹介します。任意のテーブルが存在しているaccdbファイルを開いた状態からAlt+F11キーでVBE(Visual Basic Editor)を開きます。

「挿入」→「標準モジュール」を作成して、そこへコードをコピー&ペーストしてお使いください。

f:id:itstaffing:20210803162751j:plain

  DAOのテンプレート    

それではまず、DAOを利用する方法から紹介します。

DAOは2021年8月現在ではAccessの規定のデータベース接続方法と考えられるため、標準で「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」にチェックが付いています。この設定でDAO.DatabaseやDAO.Recordsetなどのオブジェクトが利用できます。

f:id:itstaffing:20210803162755j:plain

以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。

Public Function getRsInArray_DAO(ByVal sql As String) As Variant
'## レコードセットを配列へ入れて返す

On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言
Set db = CurrentDb '現在開いているデータベースへ接続

'レコードセットのオープン
Dim rs As DAO.Recordset 'DAOレコードセットオブジェクト変数宣言
Set rs = db.OpenRecordset(sql) 'カーソルタイプを指定してレコードセットを開く

'空だったら最終処理へ
If rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合
  getRsInArray_DAO = Array() '空の配列を返す
  GoTo Finally '最終処理へジャンプ
End If

'レコード数を取得
Dim recordCount As Long
rs.MoveLast
recordCount = rs.recordCount
rs.MoveFirst

'配列に展開
Dim ary() As Variant
ReDim ary(recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義
Dim rsNum As Long '縦要素(レコードセット)の繰り返し用変数
Dim fldNum As Long '横要素(フィールド)の繰り返し用変数
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
For fldNum = 0 To rs.Fields.Count - 1 'フィールドの数だけ繰り返す
  ary(rsNum, fldNum) = rs(fldNum) '配列に格納
Next fldNum
rsNum = rsNum + 1 '縦要素をカウントアップする
rs.MoveNext '次のレコードに移動する
Loop

getRsInArray_DAO = ary '配列を返す

GoTo Finally '正常に終了したら最終処理へジャンプ

Err_Handler: '例外処理
  Dim msgTxt As String
  msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
  MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
  getRsInArray_DAO = Array() '空の配列を返す

Finally: '最終処理
If Not rs Is Nothing Then 'レコードセットオブジェクトが存在している場合のみ
  rs.Close 'レコードを閉じる
  Set rs = Nothing
End If
If Not db Is Nothing Then '接続解除
  db.Close
  Set db = Nothing
End If
End Function

以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行し、結果をTrue/Falseで返します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。

Public Function tryExecute_DAO(ByVal sqlList As Collection) As Boolean
'## SQLの実行
On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim ws As DAO.Workspace 'トランザクション用オブジェクト
Set ws = DBEngine(0)

Dim db As DAO.Database 'DAOデータベース用オブジェクトの宣言
Set db = CurrentDb '現在開いているデータベースへ接続

ws.BeginTrans 'トランザクション開始

'実行
Dim sql As Variant
For Each sql In sqlList 'SQL文リストをループ
  db.Execute sql '1行ずつ実行
Next sql

ws.CommitTrans '確定

tryExecute_DAO = True '成功だった場合、関数の結果にTrueを入れる
GoTo Finally '正常に終了したら最終処理へジャンプ

ErrorHandler: '例外処理(エラーが起きたらここへジャンプ)
ws.Rollback '元の状態へ戻す
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージ格納
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力

Finally: '最終処理
If Not db Is Nothing Then '接続解除
  db.Close
  Set db = Nothing
End If
If Not ws Is Nothing Then 'トランザクション用のオブジェクトを破棄
  ws.Close
  Set ws = Nothing
End If
End Function

  ADOのテンプレート    

続いてADOを利用する方法です。

DAOの場合、「ツール」→「参照設定」の「Microsoft Office XX.X Access database engine Object Library」を利用しましたが、ADOで同様にするには、「Microsoft ActiveX Data Objects X.X Library」を利用します。パソコン環境によってバージョンが複数存在する可能性がありますが、数字の大きなものがよいでしょう。

f:id:itstaffing:20210803162759j:plain

ただし、このライブラリはデフォルトでチェックがオフになっているので、VBAを組み込んだAccessファイルを別のパソコンで使うときに、参照設定でチェックを入れ直さないとエラーになってしまいます。

ライブラリを使ったほうが開発時は便利なのですが、運用時にうっかりエラーを引き起こす可能性もあるので、今回は参照設定の「Microsoft ActiveX Data Objects X.X Library」にチェックを入れずにADOを利用するコードをご紹介します。

以下はレコードの抽出で使用するテンプレートです。引数のSQLによって抽出されたレコードを2次元配列へ格納して返します。

Public Function getRsInArray_ADO(ByVal sql As String) As Variant
'## レコードセットを配列へ入れて返す

Const adStateOpen = 1 'レコードセットが開いている場合の設定値
Const adOpenKeyset = 1 'レコードセットカーソルタイプの設定値

On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim cn As Object 'ADOコネクション用オブジェクトの宣言
Set cn = CurrentProject.Connection '現在開いているデータベースへ接続

'レコードセットのオープン

Dim rs As Object 'レコードセット用変数宣言
Set rs = CreateObject("ADODB.RecordSet") 'ADOレコードセットオブジェクトを作成
rs.Open sql, cn, adOpenKeyset 'カーソルタイプを指定してレコードセットを開く

'空だったら最終処理へ

If rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合
  getRsInArray_ADO = Array() '空の配列を返す
  GoTo Finally '最終処理へジャンプ
End If

'配列に展開
Dim ary() As Variant
ReDim ary(rs.recordCount - 1, rs.Fields.Count - 1) '配列の要素数を定義
Dim rsNum As Long '縦要素(レコードセット)の繰り返し用変数
Dim fldNum As Long '横要素(フィールド)の繰り返し用変数
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
For fldNum = 0 To rs.Fields.Count - 1 'フィールドの数だけ繰り返す
  ary(rsNum, fldNum) = rs(fldNum) '配列に格納
Next fldNum
rsNum = rsNum + 1 '縦要素をカウントアップする
rs.MoveNext '次のレコードに移動する
Loop

getRsInArray_ADO = ary '配列を返す

GoTo Finally '正常に終了したら最終処理へジャンプ

Err_Handler: '例外処理
  Dim msgTxt As String
  msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
  MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
  getRsInArray_ADO = Array() '空の配列を返す

Finally: '最終処理
If Not rs Is Nothing Then 'レコードセットオブジェクトが存在している場合のみ
  If rs.State = adStateOpen Then rs.Close 'レコードセットが開いていたら閉じる
  Set rs = Nothing
End If
If Not cn Is Nothing Then
  cn.Close '接続解除
  Set cn = Nothing
End If
End Function

以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行します。トランザクションが実装されているので、途中でエラーが発生した場合は処理されません。

Public Function tryExecute_ADO(ByVal sqlList As Collection) As Boolean
'## SQLの実行

On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示

Dim cn As Object 'ADOコネクション用オブジェクトの宣言
Set cn = CurrentProject.Connection '現在開いているデータベースへ接続

cn.BeginTrans 'トランザクション開始

'実行
Dim sql As Variant
For Each sql In sqlList 'SQL文リストをループ
  cn.Execute sql '1行ずつ実行
Next sql

cn.CommitTrans '確定

tryExecute_ADO = True '成功だった場合、関数の結果にTrueを入れる
GoTo Finally '正常に終了したら最終処理へジャンプ

ErrorHandler: '例外処理(エラーが起きたらここへジャンプ)
cn.RollbackTrans '元の状態へ戻す
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージ格納
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力

Finally: '最終処理
If Not cn Is Nothing Then
  cn.Close '接続解除
  Set cn = Nothing
End If
End Function

  テンプレートの利用(レコードの抽出)    

SQLのSELECT構文を作成し、引数としてテンプレートへ渡して結果のレコードセットを二次配列の形で受け取るコードがこちらです。

SELECT構文の基本形は以下のようなものです。

SELECT フィールド1, フィールド2 FROM テーブル WHERE 条件;

Public Sub selectRecord()
'## レコードの抽出

'SQL作成
Dim sql As String
sql = "任意のSELECT構文"

'レコードセットを配列で取得
Dim ary() As Variant
ary = getRsInArray_DAO(sql) '←DAOを利用する場合
'ary = getRsInArray_ADO(sql) '←ADOを利用する場合

'空の配列だったら終了

If isEmptyArray(ary) Then Exit Sub

'中身の確認
Dim rsCount As Long
rsCount = UBound(ary, 1) '第1要素(レコード)数
Dim fldCount As Long
fldCount = UBound(ary, 2) '第2要素(フィールド)数
Dim x As Long, y As Long
For x = 0 To rsCount
For y = 0 To fldCount
  Debug.Print ary(x, y), ; 'イミディエイトウィンドウに出力
Next y
Debug.Print "" 'イミディエイトウィンドウの改行
Next x
End Sub

取得部分は、DAO/ADOどちらか一方をお使いください。

配列を受け取った後、中身を確認する前に配列が空かどうかの判定も必要なので、以下の関数も任意のモジュールに追記してください。

Public Function isEmptyArray(ByVal tgtAry As Variant) As Boolean
'## 配列が空か判定

On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
If UBound(tgtAry) >= 0 Then '配列に要素が存在したら
  Exit Function  '終了(Falseを返す)
End If

Err_Handler:  'エラーもしくは要素が存在しない場合
  isEmptyArray = True  'Trueを返す
End Function

コードを実行すると、SELECT構文で抽出した配列の中身がVBEのイミディエイトウィンドウ(表示されていない場合は「表示」→「イミディエイトウィンドウ」)で確認できます。

f:id:itstaffing:20210803162804j:plain

取得した配列は、そのままコンボボックスやリストボックスのソースにセットすることができるほか、レコードの値を非連結で扱えるのでさまざまな実装に役立ちます。

  テンプレートの利用(レコードの挿入・更新・削除)    

SQLのINSERT/UPDATE/DELETE構文をコレクションに格納し、引数としてテンプレートへ渡して結果をTrue/Falseの形で受け取るコードがこちらです。

それぞれの構文の基本形は以下のようなものです。

・INSERT INTO テーブル(フィールド1, フィールド2) VALUES(値1, 値2);
・UPDATE 
テーブル SET フィールド1=値1, フィールド2=値2  WHERE  条件;
・DELETE FROM
 テーブル  WHERE 条件;

複数のSQL文に対応するためコレクションに格納していますが、格納されるSQL文が1つでも動作します。数が多い場合はDoやForなどのループを使ってSQLを作成&コレクションへ格納していくとスマートです。

Public Sub executeSQL()
'## レコードの挿入・更新・削除

'コレクションの作成
Dim sqlList As Collection
Set sqlList = New Collection

'SQL作成
Dim sql As String 'SQL文用変数の宣言

sql = "任意のINSERT/UPDATE/DELETE構文" '1つ目のSQL文
sqlList.Add sql 'コレクションへ追加

sql = "任意のINSERT/UPDATE/DELETE構文" '2つ目のSQL文
sqlList.Add sql 'コレクションへ追加

'実行
If tryExecute_DAO(sqlList) Then '処理が成功した場合 '←DAOを利用する場合
'If tryExecute_ADO(sqlList) Then '処理が成功した場合 '←ADOを利用する場合

  MsgBox "正常に終了しました", vbOKCancel + vbInformation, "終了" '終了メッセージ
End If
End Sub

実行部分は、DAO/ADOどちらか一方をお使いください。

実行すると、問題が起きた場合は処理されずにエラー内容が、問題なく処理が終了した場合は終了メッセージが表示されます。

f:id:itstaffing:20210803162748j:plain

いかがだったでしょうか?次回はVBAでSQL文を扱う際のコツやポイントについてです。1行で済むような短い文ならば理解しやすいのですが、可読性のために改行したい、変数を使いたい、などの実用的なSQL組み込みのためのヒントをご紹介しますので、ぜひご期待ください。

 

第1話「イベント」を使いこなそう
第2話 レイアウトで躓きやすいポイント
第3話 マクロとVBAの違い