みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。
前回の記事はコチラ。
Accessデータベースに特定の条件のデータが存在するかどうかを判定する方法についてお伝えしました。
さて、ここまでのシリーズでレコードの抽出とレコードの追加についてお伝えしてきましたが、既に存在しているレコードのデータを更新したいとき、どうすればよいでしょうか？
例えば
- 「呼び出し」ボタンで特定のレコードについて呼び出す
- エクセル上に呼び出されたレコードの値を修正
- 「更新」ボタンでAccessデータベースを更新
というような動きです。
今回は、そのようなプログラムの書き方をお伝えします。
エクセルVBAからAccessデータベースについてそのレコードを上書き更新する方法です。
では、よろしくお願いいたします。
お題となるデータベースとエクセルシート
まず、お題となるデータベースですがコチラです。
テーブル名は「成績表」。その名の通り、各人の成績について記録したテーブルで、そのフィールド構成は
という構成になっています。国語、数学、英語のフィールドには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行目に書き出しをするという流れです。
参考として、以下の記事たちをご覧いただくと良いと思います。
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を使います。
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
でしたね。ちょっと長いです。噛み砕くと
- テーブル名：成績表
- SET句のフィールド名と値
- C1セル、C2セル
- D1セル、D2セル
- E1セル、E2セル
- WHERE句の条件式：フィールドIDがA2セルと等しい
となります。
UPDATEのSQL文を実行
UPDATEによるSQL文を実行する場合は、ADODBコネクションオブジェクトに対するExecuteメソッドを使います。
書き方はこうです。
SELECTによるSQL文を実行する場合は、ADODBレコードセットオブジェクトに対するOpenメソッドを使いましたが、実行する内容で実行方法が異なるので注意して下さいね。
実行テストと結果
では、実際にこれらのプロシージャをボタンに設定をして、実行をしてみましょう。
今回は、Loganの成績表に関する値を変更したいので、IDが5のレコードを呼び出します。「呼び出し」ボタンを押すと「呼び出すIDを入力してください」とInputBoxが表示されますので「5」と入力して「OK」をします。
すると、このようにLoganのデータがシートに呼び出されます。
エクセルシート上で、Loganのデータについて正しい値を入力した上で「更新」ボタンを押します。
Accessデータベースの成績表テーブルを確認してみますと
このように無事に値が更新されました。
まとめ
エクセルVBAからAccessデータベースについてそのレコードを上書き更新する方法についてお伝えしました。
レコードを更新する場合は、UPDATEによるSQL文を作成して、ADODBコネクションオブジェクトに対するExecuteメソッドで実行をします。
おそらく、Accessデータベースを使ったシステムを作る場合は、だいぶとお世話になると思います。
さて、次回は今回のUPDATEを一行ずつではなくて複数行まとめて実行する方法についてお伝えします。
どうぞお楽しみに！
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する
エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。- なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
- エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
- 【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム
- 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
- 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
- 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
- エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
- 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
- エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
- エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
- エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
- エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
- エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
- エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
- エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
