エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法

photo credit: morten f Skarjo likes records via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法について、シリーズでお伝えしています。

前回の記事はコチラです。

エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はUPDATEのSQL文を使ってAccessデータベースのレコードを呼び出して上書き更新する方法です。

Accessデータベースの特定のレコードを呼び出して、修正して上書き更新する方法についてお伝えしました。

前回記事では一つのレコードが対象だったのですが、まとめて置換をしたいときとか、複数のレコードを一気に修正したい場合もありますよね？

ということで、今回はエクセルVBAからAccessデータベースの複数レコードをまとめて上書き更新する方法についてお伝えします。

お題のデータベースとエクセルシート
データベースの大量のレコードをまとめてエクセルに呼び出す
データベースの複数のレコードを更新する
1. 前回のおさらい：単一のレコードを更新する
2. データベースの複数のレコードを更新するプログラム
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

お題のデータベースとエクセルシート

お題となるデータベースは前回に引き続いてこちらです。

テーブル名は「成績表」、フィールドの構成は

｜ID｜名前｜国語｜数学｜英語｜

です。今回は大量のレコードを更新したいので9,000行のレコードを用意しました。

データベースの大量のレコードをまとめてエクセルに呼び出す

では、まずこのデータベースのレコード全てをエクセルシートに呼び出してみましょう。

呼び出す先のエクセルシートはこちらの「Sheet3」です。

プログラムはこちらですね。

Sub fetchRecords()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")

Dim strFileName As String
strFileName = "test4.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

Dim strSQL As String
strSQL = "SELECT * FROM 成績表"

adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
ws.Range("A2").CopyFromRecordset adoRs

adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

もうお馴染みのSQLのSELECT文と、CopyFromRecordsetメソッドを使って出力しています。

実行をすると

このようにすべてのレコードを瞬時に取得できます。

詳しくはこちらの記事をご覧くださいね。

【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。

データベースの複数のレコードを更新する

いよいよここからが本番です。

まず、呼び出したレコードの値を変更します。

その上で、データベース上に存在するレコードを変更した値に上書きしていきたいと思います。

前回のおさらい：単一のレコードを更新する

前回のプログラムを少し修正することで対応できますので、まず復習から。

こちらは単一のレコードを修正するプログラムでした。

Sub updateRecord()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")

Dim strFileName As String
strFileName = "test4.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim strSQL As String
strSQL = _
    "UPDATE 成績表 " & _
    "SET " & _
        ws.Range("C1").Value & "=" & ws.Range("C2").Value & "," & _
        ws.Range("D1").Value & "=" & ws.Range("D2").Value & "," & _
        ws.Range("E1").Value & "=" & ws.Range("E2").Value & " " & _
    "WHERE ID=" & ws.Range("A2").Value

adoCn.Execute strSQL 'SQLを実行
adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

SQLのUPDATE文を使って、該当のIDにマッチするレコードを上書きするといった内容でした。

少し変更すればできそうですよね。

対象とするエクセルシートwsをSheet2をSheet3に変更
14行目から22行目、SQL文の生成から実行までを繰り返し処理とする

というように、変更をしていきます。

データベースの複数のレコードを更新するプログラム

データベースの複数のレコードを上書き更新するプログラムはこちらになります。

Sub updateRecords()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")

Dim strFileName As String
strFileName = "test4.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim strSQL As String

Dim i As Long
i = 2
Do While ws.Cells(i, 1) <> ""

    strSQL = _
        "UPDATE 成績表 " & _
        "SET " & _
            ws.Range("C1").Value & "=" & ws.Cells(i, 3).Value & "," & _
            ws.Range("D1").Value & "=" & ws.Cells(i, 4).Value & "," & _
            ws.Range("E1").Value & "=" & ws.Cells(i, 5).Value & " " & _
        "WHERE ID=" & ws.Cells(i, 1).Value

    adoCn.Execute strSQL 'SQLを実行
    i = i + 1
Loop

adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

17行目～29行目ですが、Do Whileループを使って、Sheet3に存在するレコード全てについて繰り返しをします。

繰り返し内部の処理は