Accessテーブルの参照方法で、ADO・DAO両方使えるサンプルです。
サンプルコードを利用するための前提条件
参照設定を行いでADO(Microsoft ActiceX Data Objects X.X Library)を参照可能なライブラリに設定します。「ツール」メニュー→「参照環境」で画面を表示します。
まずはサンプルコード
いきなりですがサンプルです。ADO(Data Access Object）接続とDAO(ActiveX Data Object)接続を記載しています。通常はADOで問題ありませんが状況に応じてDAOを使用してください。
'ADOのサンプル
Public Sub TAbleReadSample_ADO()
On Error GoTo myERR
Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset
Set DB = CurrentProject.Connection
' (他ファイルを参照する場合)--------------------------------------
' 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"
' -------------------------------------------------------------------
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
Set RS = New ADODB.Recordset
RS.Open SQL, DB, adOpenKeyset
'レコードの存在チェック
If Not RS.EOF Then
'レコードを先頭から最後までループ
Do Until RS.EOF
'NULLが想定できるフィールドはNZ関数を使う
Debug.Print CStr(RS.Fields("ID")) + ":" + Nz(RS.Fields("フィールド1"))
'トランザクションを利用してSQLを実行
DB.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
'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
'DAOのサンプル
Public Sub TAbleReadSample_DAO()
On Error GoTo myERR
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
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を実行
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)
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実行をなかったことにできます。
コメント