エクセルVBAのADO機能を利用した、アクセスDBとの連携について解説していきます。
この記事では、データベースの抽出/登録/更新/削除の各操作について解説します。
まだ、データベースの接続/切断について未読の方は、次の記事で解説しています。
VBA初期設定
レコード抽出
抽出
データベースからのレコード抽出のサブプロシージャを標準モジュール：TESTに作成します。
レコードセットのデータを、配列変数に格納し、処理を実行する流れを解説します。
Sub レコード抽出() '''''''''' レコード抽出 '''''''''' 'レコードセットオブジェクトの作成 Dim adoRs As Object Set adoRs = CreateObject("ADODB.Recordset") 'SQL文の変数への取り込み Dim strSQL As String strSQL = "SELECT*FROM T_受注 WHERE 削除 = False" 'レコードセットの取得 adoRs.Open strSQL, adoCn '変数の設定 Dim ary受注() As Variant ReDim ary受注(adoRs.RecordCount - 1, 3) '配列変数への取り込み Dim i As Long i = 0 Do Until adoRs.EOF ary受注(i, 0) = adoRs!受注ID ary受注(i, 1) = adoRs!受注日 ary受注(i, 2) = adoRs!商品名 ary受注(i, 3) = adoRs!数量 i = i + 1 'カウンターを加算 adoRs.MoveNext 'カーソルを1行下へ Loop 'レコードセットオブジェクトの破棄 adoRs.Close Set adoRs = Nothing '''''''''''''''''''''''''''''''''''''''' 'データの書き込み Sheets(1).Cells(2, 1).Resize(i, 4).Value = ary受注 End Sub
@1.レコードセットオブジェクトの作成
adoRsオブジェクトを作成します。
@2. SQL文の変数への取り込み
データ抽出用のSQL文を作成し、文字列変数に格納します。
レコードセットの抽出には、SELECTを使用します。
フィールド名の代わりに、"*"を使用すると、全てのフィールドが抽出されます。また、テーブル名の代わりにクエリ名を使用することもできます。
今回はT_受注テーブルの、削減フラグの立っていないレコードを抽出します。
@3.レコードセットの取得
adoRsオブジェクトのOpenメソッドを使用し、 SQLを実行して、抽出結果をレコードセットへ取り込みます。
@4.配列変数の設定
データ格納用に、配列変数を宣言し、要素数を設定します。
adoRs.RecordCountで、レコードセットのレコード数を取得できます。
@5.配列変数への取り込み
Do_Until_Loop構文でadoRsオブジェクト内をループしながら、変数に取り込んでいきます。
繰り返し条件は、adoRs.EOF がTrue になるまでです。EOFとは、End Of Fileの略称で、レコードセットが最後のレコードより後に達するとTrueを返します。
レコードセットのレコードが無くなったら、ループを抜けます。
＊Do_Loopステートメントについては、下記の記事で解説しています。
データの指定は、 adoRs.Fields(フィールド名)のようにFieldsコレクションを使用する方法もありますが、adoRs!フィールド名と"!"を使用すると短いコードで指定できます。
@6. レコードセットオブジェクトの破棄
レコードセットのデータは、配列変数に取り込みましたので、adoRsオブジェクトを破棄します。
adoRsオブジェクトのCloseメソッドを使用し、閉じます。
adoRsオブジェクトを破棄して終了です。
@7.データの書き込み
取得データを、ワークシートに書き込みます。
VBA実行
実行用のプロシージャを作成します。
Sub 実行() Call 接続 Call レコード抽出 Call 切断 End Sub
データベースからのレコード抽出のVBAを実行してみましょう。
ワークシートにデータが書き込まれていれば、データ取得完了です。
レコード登録
登録
データベースへのレコード登録のサブプロシージャを標準モジュール：TESTに作成します。
Sub レコード登録() '''''''''' レコード登録 '''''''''' 'レコードセットオブジェクトの作成 Dim adoRs As Object Set adoRs = CreateObject("ADODB.Recordset") '受注データの登録 With adoRs .Open "T_受注", adoCn, adOpenKeyset, adLockOptimistic .MoveLast '最終レコードに移動 .AddNew '新規レコードに追加 !受注日 = #6/6/2023# !商品名 = "商品A" !数量 = 2 !削除 = False .Update 'レコードの更新 End With 'レコードセットオブジェクトの破棄 adoRs.Close Set adoRs = Nothing '''''''''''''''''''''''''''''''''''''''' End Sub
@1.レコードセットオブジェクトの作成
adoRsオブジェクトを作成します。
@2.受注データの登録
adoRsオブジェクトのOpenメソッドを使用し、テーブルのデータをレコードセットに取り込みます。
レコードの追加処理を可能にするため、パラメータのadOpenKeyset (キーセットカーソルを使用)と、adLockOptimistic(Update時のレコード単位のロック)を指定します。
@3.レコードの追加
MoveLastで新規レコードに移動し、AddNewで各フィールドにデータを書き込んでいきます。
書き込み時のデータはフィールドの型にあわせて、次の設定が必要です。
日付型 ： # 日付 #
文字列 ： " 文字列 " (ダブルコーテーション)
数値型 ： 数値 (そのまま)
Yes/No型 ： True / False
@4.レコードの保存
Updateでデータを書き込んだ新規レコードが、保存されます。
@5. レコードセットオブジェクトの破棄
adoRsオブジェクトのCloseメソッドを使用し、閉じます。
adoRsオブジェクトを破棄して終了です。
VBA実行
実行用のプロシージャを作成します。
Sub 実行() Call 接続 Call レコード登録 Call 切断 End Sub
データベースへのレコード登録のVBAを実行してみましょう。
テーブルの新規レコードにデータが書き込まれていれば、データ登録完了です。
レコード更新
更新
データベースへのレコード更新のサブプロシージャを標準モジュール：TESTに作成します。
Sub レコード更新() '''''''''' レコード更新 '''''''''' 'SQL文の変数への取り込み Dim strSQL As String strSQL = "UPDATE T_受注 " & _ "SET 受注日 = #2023/6/10#, " & _ "商品名 = '商品D', " & _ "数量 = 20, " & _ "削除 = False " & _ "WHERE 受注ID = 6" 'SQLの実行 adoCn.Execute strSQL '''''''''''''''''''''''''''''''''''''''' End Sub
@1.レコード更新
レコードの更新はSQLの実行で行いますので、レコードセットは必要ありません。
@2.SQL文の変数への取り込み
データ更新用のSQL文を作成し、文字列変数に格納します。SQL文が長くなるときは、改行を入れながらコードを書きます。
レコードの更新には、UPDATEを使用します。
書き込み時のデータはフィールドの型にあわせて、次の設定が必要です。
日付型 ： # 日付 #
文字列 ： ' 文字列 ' (シングルコーテーション)
数値型 ： 数値 (そのまま)
Yes/No型 ： True / False
@3.SQLの実行
adoCnオブジェクトのExecuteメソッドで、データベースにSQLを実行します。
VBA実行
実行用のプロシージャを作成します。
Sub 実行() Call 接続 Call レコード更新 Call 切断 End Sub
データベースへのレコード更新のVBAを実行してみましょう。
テーブルの該当レコードのデータが書き変わっていれば、データ更新完了です。
レコード削除
論理削除
削除には、論理削除と物理削除がありますが、まずは論理削除を解説します。論理削除は、削除フラグのフラグを変更するだけなので、レコード更新と同様のコードとなります。
データベースへのレコード論理削除のサブプロシージャを標準モジュール：TESTに作成します。
Sub レコード論理削除() '''''''''' レコード削除 '''''''''' 'SQL文の変数への取り込み Dim strSQL As String strSQL = "UPDATE T_受注 " & _ "SET 削除 = True " & _ "WHERE 受注ID = 6" 'SQLの実行 adoCn.Execute strSQL '''''''''''''''''''''''''''''''''''''''' End Sub
@1.レコード削除
レコードの削除はSQLの実行で行いますので、レコードセットは必要ありません。
@2.SQL文の変数への取り込み
データ削除用のSQL文を作成し、文字列変数に格納します。
レコードの論理削除には、UPDATEを使用します。
@3.SQLの実行
adoCnオブジェクトのExecuteメソッドで、データベースにSQLを実行します。
VBA実行
実行用のプロシージャを作成します。
Sub 実行() Call 接続 Call レコード論理削除 Call 切断 End Sub
データベースへのレコード論理削除のVBAを実行してみましょう。
テーブルの該当レコードのフラグが書き変わっていれば、データ削除完了です。
物理削除
物理削除を解説します。
データベースへのレコード物理削除のサブプロシージャを標準モジュール：TESTに作成します。
Sub レコード物理削除() '''''''''' レコード削除 '''''''''' 'SQL文の変数への取り込み Dim strSQL As String strSQL = "DELETE FROM T_受注 " & _ "WHERE 受注ID = 6" adoCn.Execute strSQL '''''''''''''''''''''''''''''''''''''''' End Sub
@1.レコード削除
レコードの削除はSQLの実行で行いますので、レコードセットは必要ありません。
@2.SQL文の変数への取り込み
データ削除用のSQL文を作成し、文字列変数に格納します。
レコードの論理削除には、DELETEを使用します。
@3.SQLの実行
adoCnオブジェクトのExecuteメソッドで、データベースにSQLを実行します。
VBA実行
実行用のプロシージャを作成します。
Sub 実行() Call 接続 Call レコード物理削除 Call 切断 End Sub
データベースへのレコード物理削除のVBAを実行してみましょう。
テーブルの該当レコードが削除されていれば、データ削除完了です。
VBAでの基本的なデータベース操作方法を解説しました。
が、実践するとなるとなかなか難しいものです。特に、VBAで書くSQL文はスペースやカンマの位置が見にくかったり、データ型に対応した書式があったりするので、注意が必要です。
コツを掴むまで、繰り返し実践しましょう。
概要解説に戻る
www.minizaiko.com
