Accessテーブルの参照方法で、ADO・DAO両方使えるサンプルです。
サンプルコードを利用するための前提条件
参照設定を行いでADO(Microsoft ActiceX Data Objects X.X Library)を参照可能なライブラリに設定します。「ツール」メニュー→「参照環境」で画面を表示します。
まずはサンプルコード
いきなりですがサンプルです。ADO(Data Access Object）接続を基本としていますが、DAO(ActiveX Data Object)接続をコメントとして記載していますので環境に応じて使い分けます。
Public Sub TAbleReadSample()
On Error GoTo myERR
' ADOの場合(自ファイルを参照する場合)
Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset
Set DB = CurrentProject.Connection
' ADO(他ファイルを参照する場合)--------------------------------------
' Dim DB As ADODB.Connection
' Dim RS As ADODB.Recordset
' Set DB = New ADODB.Connection
' db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\XXXXX\XXXXX.accdb"
' -------------------------------------------------------------------
' DAOの場合-----------------
' Dim WS As DAO.Workspace
' Dim DB As DAO.Database
' Dim RS As DAO.Recordset
' Set WS = DBEngine.Workspaces(0)
' Set DB = CurrentDb
' --------------------------
Dim SQL As String
Dim row As Long
'メンテナンス性を考慮したSQLの書き方(好みの問題です)
SQL = ""
SQL = SQL + vbCrLf + " SELECT"
SQL = SQL + vbCrLf + " ID"
SQL = SQL + vbCrLf + ",フィールド1"
SQL = SQL + vbCrLf + " FROM テーブル1"
SQL = SQL + vbCrLf + " ORDER BY ID DESC"
row = 0
' ADOの場合
Set RS = New ADODB.Recordset
RS.Open SQL, DB, adOpenKeyset
' DAOの場合---------------------
' Set RS = DB.OpenRecordset(SQL)
' ------------------------------
'レコードの存在チェック
If Not RS.EOF Then
'レコードを先頭から最後までループ
Do Until RS.EOF
'NULLが想定できるフィールドはNZ関数を使う
Debug.Print CStr(RS.Fields("ID")) + ":" + Nz(RS.Fields("フィールド1"))
'トランザクションを利用してSQLを実行
DB.BeginTrans
' DAOの場合------
' WS.BeginTrans
' ---------------
SQL = ""
SQL = SQL + vbCrLf + "DELETE FROM テーブル2 "
SQL = SQL + vbCrLf + "WHERE フィールド1 ='" + Nz(RS.Fields("フィールド1")) + "'"
DB.Execute (SQL)
SQL = ""
SQL = SQL + vbCrLf + "INSERT INTO テーブル2("
SQL = SQL + vbCrLf + "フィールド1"
SQL = SQL + vbCrLf + ")VALUES("
SQL = SQL + vbCrLf + "'" + Nz(RS.Fields("フィールド1")) + "'"
SQL = SQL + vbCrLf + ")"
DB.Execute (SQL)
DB.CommitTrans
' DAOの場合-----
' WS.CommitTrans
' --------------
'20回に1回Windowsに制御を戻す。
'(状況に応じて調整。アプリが応答なしにならない考慮)
If row Mod 20 = 1 Then
DoEvents
End If
row = row + 1
RS.MoveNext
Loop
Else
Debug.Print "データが存在しない。"
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
Exit Sub
myERR:
'エラーが発生した場合エラー番号とエラーメッセージを表示
Debug.Print CStr(Err.Number) + ":" + Err.Description
End Sub
ADOか？DAOか？
これから作成する場合はADOをお勧めします。マイクロソフトでは下記のようにADOを説明してます。Accessファイルかつスタンドアロンの場合はDAOが優れている可能性があります。
Microsoft ActiveX Data Objects (ADO) により、クライアント アプリケーションが、OLE DB プロバイダーを通じてデータベース サーバーのデータにアクセスし、これを操作できるようになります。ADO の主な利点は、使用が簡単で、高速に動作し、メモリのオーバーヘッドが小さく、ディスクの使用量が少ないことです。ADO では、クライアント/サーバー アプリケーションおよび Web ベース アプリケーションを構築するための重要な機能がサポートされています。
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/microsoft-activex-data-objects-reference
SQLの書き方について
サンプルは下記のような書き方をしています。
SQL = "" SQL = SQL + vbCrLf + " SELECT" SQL = SQL + vbCrLf + " ID" SQL = SQL + vbCrLf + ",フィールド1" SQL = SQL + vbCrLf + " FROM テーブル1" SQL = SQL + vbCrLf + " ORDER BY ID DESC"
この書き方は下記のようなメリットがあります。
- デバッグ中に変数の内容を表示した際、SQLが改行されるため確認しやすい。
- SQLの修正が将来発生したとき修正しやすい。例えばWhere条件を追加する等です。また、一部修正になった場合はコメントアウトとSQLを修正を行うことで修正履歴を残すことができます。
SQL = "" SQL = SQL + vbCrLf + " SELECT" SQL = SQL + vbCrLf + " ID" SQL = SQL + vbCrLf + ",フィールド1" SQL = SQL + vbCrLf + ",フィールド2" '★2020/01/01 カラム追加 SQL = SQL + vbCrLf + " FROM テーブル1" 'SQL = SQL + vbCrLf + " ORDER BY ID DESC" SQL = SQL + vbCrLf + " ORDER BY ID" '★2020/01/02 ソート条件修正
DoEvents関数について
DoEvents関数はWindowsに処理を渡すことができます。これはアプリケーションが長い処理とアプリケーションが応答なしになることを防ぎます。
トランザクションについて(BeginTrans・CommitTrans)
トランザクションは必ずしも必要な処理ではありませんが、トランザクションを設定することでデータの整合性を保ちます。サンプルではDeleteとInsertをトランザクション処理を行っていますがもしトランザクションがなくInsert処理でエラーになった場合はDeleteだけが実行されるため、データの整合性が保てなくなります。サンプルには記載していませんがRollbackTransを実行することでBeginTrans以降のSQL実行をなかったことにできます。
コメント