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

photo credit: AlessandroBerzuini Opposite Sky via photopin (license)

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

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

前回の記事はコチラ。

429 Too Many Requests

Accessデータベースに特定の条件のデータが存在するかどうかを判定する方法についてお伝えしました。

さて、ここまでのシリーズでレコードの抽出とレコードの追加についてお伝えしてきましたが、既に存在しているレコードのデータを更新したいとき、どうすればよいでしょうか？

例えば

「呼び出し」ボタンで特定のレコードについて呼び出す
エクセル上に呼び出されたレコードの値を修正
「更新」ボタンでAccessデータベースを更新

というような動きです。

今回は、そのようなプログラムの書き方をお伝えします。

エクセルVBAからAccessデータベースについてそのレコードを上書き更新する方法です。

では、よろしくお願いいたします。

お題となるデータベースとエクセルシート
Accessデータベースから指定のIDのレコードを呼び出す
Accessデータべースの特定のレコードを更新する
1. SQLのUPDATE文
2. UPDATEのSQL文を実行
実行テストと結果
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

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

まず、お題となるデータベースですがコチラです。

テーブル名は「成績表」。その名の通り、各人の成績について記録したテーブルで、そのフィールド構成は

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

という構成になっています。国語、数学、英語のフィールドには100点満点の点数が数値として入ります。

ちなみに、Loganさんは点数がメッチャ低いですね…先生が間違えて入力してしまったようです。修正をしないといけません。

それで、このようなエクセルシートを用意しました。

「呼び出し」ボタンを押すと、呼び出すレコードの「ID」の入力を求められます。

IDを入力すると、A2からE2のせるに該当するIDのレコードがAccessデータベースから呼び出されます。

その後、エクセルを修正して「更新」ボタンを押すと、その修正した通りにAccessデータベース上のレコードも更新されるという寸法です。

Accessデータベースから指定のIDのレコードを呼び出す

ではまず、「呼び出し」ボタンで動作をする、Accessデータベースからレコードを呼び出すプロシージャを紹介しましょう。

Sub fetchRecord()

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 adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

Dim id As Long
id = InputBox("呼び出すIDを入力してください")

Dim strSQL As String
strSQL = "SELECT * FROM 成績表 WHERE ID=" & id

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

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

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

End Sub

今回、Accessデータベースのファイル名は「test4.accdb」、エクセルシートのシート名は「Sheet2」です。

14行目までは、シートとAccessデータベースに接続をするためのADOコネクションおよびADOレコードセットの各オブジェクトの準備です。

20行目で該当のIDのレコードを抽出するためにSELECTを使ったSQL文を作成しています。

そして22行目でSQLを実行して、その結果をシートの2行目に書き出しをするという流れです。

参考として、以下の記事たちをご覧いただくと良いと思います。

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

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

【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法

エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。

Accessデータべースの特定のレコードを更新する

では、今度は「更新」ボタンで動作をする、Accessデータベースの特定のレコードを更新するプロシージャを紹介します。

こちらです。

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

まず、11行目まではSub fetchRecord()と全く一緒ですね。シートとデータベースを操作するための準備をしています。

それ以降について、以降で解説をしていきます。

SQLのUPDATE文

今回はレコードを更新するSQL文を実行するという方法で、データベースのレコードを更新しています。

レコードを更新するSQL文ではUPDATEを使います。

UPDATE テーブル名 SET フィールド名1=値1,フィールド名2=値2 WHERE フィールド名=検索値

SET句の後に、フィールドごとに更新後の値を指定します。カンマを使うことで、複数のフィールドの値を更新することが可能となります。

WHERE句には更新をするレコードを特定するための条件式を入力します。

今回のSQL文は

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

でしたね。ちょっと長いです。噛み砕くと