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

records

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

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

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

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

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

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

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

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

スポンサーリンク

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

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

Accessのデータベース

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

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

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

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

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

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

Accessデータベースのレコードを呼び出すエクセルシート
プログラムはこちらですね。

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メソッドを使って出力しています。

実行をすると

エクセルシートにAccessデータベースのレコードを呼び出した

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

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

【エクセル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に存在するレコード全てについて繰り返しをします。

繰り返し内部の処理は

  • SQL文を作成する
  • SQLを実行して上書き更新

という内容ですね。そんなに難しくありませんね。

まとめ

エクセルVBAでAccessデータベースの複数のレコードを上書き更新するプログラムについて紹介しました。

システムによって、1つのレコードだけを更新した場合と、複数のレコードをまとめて更新したい場合とがあると思いますが、SQLのUPDATE文による方法であれば、どちらの場合でも応用が簡単ですね。

さて、実はレコード更新については、今回の方法に少し疑問が残ります。

以前、レコードの追加に関しては、SQLのINSERT INTO文を使う方法よりも、ADODBレコードセットオブジェクトのAddNewとUpdateメソッドを使うほうが実行速度がはるかに速いということを確認しました。

429 Too Many Requests

UPDATEに関しても、ADODBレコードセットオブジェクトを操作する何らかの方法のほうが実行速度が速いのでは?という疑問がわいてきますよね。

次回の記事でその疑問について答えを出していきたいと思います。

301 Moved Permanently

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  9. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  10. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  12. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  13. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  14. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  15. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  16. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  17. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
タイトルとURLをコピーしました