本記事では、ExcelのマクロからMicrosoft Accessのデータを更新する方法についてご説明します。
ExcelのマクロからMicrosoft Accessのデータを更新する方法
ExcelのマクロからMicrosoft Accessのデータを更新するには、次の流れの通りにコードを書いていきます。
このMicrosoft Accessに接続するための接続情報は、Microsoft Accessに接続するための「鍵」みたいなものなので必ずコードに記述する必要があります。
Microsoft Accessに接続するには、ConnectionオブジェクトのOpenメソッドを使います。
このインスタンスは、Microsoft Accessのデータの更新に必要です。
レコードセットを開いてデータを更新するには、RecordsetオブジェクトのインスタンスのOpenメソッドを使用します。
このOpenメソッドにデータを更新するSQL文を引数に指定します。
コードの例
Excelのマクロのコード(例)
Excelのマクロのコード(例)は次の通りです。
Dim strFileName As String, strSQL as string Dim adoCON As New ADODB.Connection Dim adoRS As New ADODB.Recordset 'カレントディレクトリのデータベースパスを取得 strFileName = ActiveWorkbook.Path & "\work.accdb" 'データを更新するSQL文 strSQL = "Update memInfo set " strSQL = strSQL & "memName = '" & "ユーザ1" & "'" strSQL = strSQL & ", postalCode = '" & "123-4567" & "'" strSQL = strSQL & ", addrs = '" & "住所1-1-1" & "'" strSQL = strSQL & ", phoneNum = '" & "080-1234-5678" & "'" strSQL = strSQL & ", birth = '" & 19870511 & "'" strSQL = strSQL & ", mailaddrs = '" & "abc@xyz.com" & "'" strSQL = strSQL & " WHERE memID = " & 1 'Microsoft Accessへの接続情報を取得する adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & strFileName & "" 'Microsoft Accessに接続する adoCON.Open 'オブジェクトの設定 Set adoRS = CreateObject("ADODB.Recordset") adoRS.CursorLocation = 3 ' クライアントサイドカーソルに変更 'レコードセットを開く adoRS.Open strSQL, adoCON, adOpenDynamic 'クローズ処理 Set adoRS = Nothing adoCON.Close Set adoCON = Nothing
コードの解説
最初に見て頂きたいのは30行目です。
RecordsetオブジェクトのOpenメソッドを使い、テーブルのデータを更新しています。
なお、どんなデータを更新するかは変数strSQLにSQL文を設定し(コードの例では10行目から17行目)、Openメソッドの引数に指定します。(30行目)
また、Microsoft Accessのデータを更新するにはまずExcelがMicrosoft Accessに接続しなければいけませんが、その接続は20行目から24行目で行っています。
ExcelのマクロからMicrosoft Accessに接続する処理は次の記事で詳しく説明しているので、そちらもあわせてご覧ください。
【ExcelVBA】ExcelのマクロからMicrosoft Accessに接続するにはコード(例)の10行目から17行目で記述したSQL(Update)文は次の通りです。
Update dbo.tbl_userInfo set
memName = ‘ユーザ1’
, postalCode = ‘123-4567’
, addrs = ‘住所1-1-1’
, phoneNum = ‘080-1234-5678’
, birth = ‘19870511’
, mailaddrs = ‘abc@xyz.com’
WHERE memID = 1
コード(例)だとUpdate文が分かりにくいと思ったので参考にしていただければと思います。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの2行目の「ADODB.Connection」と3行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As New ADODB.Connection Dim adoRS As New ADODB.Recordset
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
【動画】ExcelのマクロからMicrosoft Accessのデータを更新する参考動画
ツイート内の動画では、更新ボタンをクリックすると、Microsoft Accessに接続して黄色のセルのデータを更新しています。(動画の0:15~0:26あたり)
ExcelVBAからAccessのDBにアクセスして参照・更新処理ができるか確認してみました😀
以前ExcelVBAとSQL Serverとの連携を確認してみたときと似たようなこと(https://t.co/yi0FE4XM9W)を今回やってみました😄#Excel#VBA#Access pic.twitter.com/TZHe7I8PNg
— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) March 15, 2021
最後に
本記事では、ExcelのマクロからMicrosoft Accessのデータを更新する方法についてご説明しました。
以下の処理を実装することで、Microsoft Accessのデータを更新することができます。
- ①Microsoft Accessへの接続情報取得
- ② ①の情報を参照してMicrosoft Accessに接続
- ③Recordsetオブジェクトのインスタンスの生成
- ④データを更新するSQL文をもとにレコードセットを開いてデータを更新
手順は多いですが、コードを実装する際は、先ほどお見せしたコードの例を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。