本記事では、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 = "INSERT INTO memInfo (" strSQL = strSQL & "memID" strSQL = strSQL & ", memName" strSQL = strSQL & ", postalCode" strSQL = strSQL & ", addrs" strSQL = strSQL & ", phoneNum" strSQL = strSQL & ", birth" strSQL = strSQL & ", mailaddrs" strSQL = strSQL & ", delFlg) " strSQL = strSQL & "VALUES " strSQL = strSQL & "(" strSQL = strSQL & 10 strSQL = strSQL & ",'" & "ユーザ10" & "'" strSQL = strSQL & ",'" & "123-4567" & "'" strSQL = strSQL & ",'" & "住所1-1-1" & "'" strSQL = strSQL & ",'" & "080-1234-5678" & "'" strSQL = strSQL & ",'" & 19870511 & "'" strSQL = strSQL & ",'" & "abc@xyz.com" & "'" strSQL = strSQL & ",0)" '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
コードの解説
最初に見て頂きたいのは41行目です。
RecordsetオブジェクトのOpenメソッドを使い、テーブルにデータを追加しています。
なお、どんなデータを追加するかは変数strSQLにSQL文を設定し(コードの例では10行目から28行目)、Openメソッドの引数に指定します。(41行目)
また、Microsoft Accessにデータを追加するにはまずExcelがMicrosoft Accessに接続しなければいけませんが、その接続は31行目から35行目で行っています。
ExcelのマクロからMicrosoft Accessに接続する処理は次の記事で詳しく説明しているので、そちらもあわせてご覧ください。
【ExcelVBA】ExcelのマクロからMicrosoft Accessに接続するにはコード(例)の30行目から48行目で記述したSQL(Insert)文は次の通りです。
INSERT INTO memInfo (
memID
,memName
,postalCode
,addrs
,phoneNum
,birth
,mailaddrs
,delFlg
)
VALUES
(
10
,N’ユーザ10′
,’123-4567′
,N’住所1-1-1′
,’080-xxxx-1234′
,’19870511′
,’abc@xyz.com’
,0
)
コード(例)だとInsert文が分かりにくいと思ったので参考にしていただければと思います。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「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 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
【動画】ExcelのマクロからMicrosoft Accessにデータを追加する参考動画
ツイート内の動画では、Excelのシートの下部にあるコンボボックスに「追加」を選択して黄色のセルに追加したいデータを入力後に実行ボタンをクリックすると、Microsoft Accessに接続して黄色のセルのデータを追加しています。(動画の1:02~1:35あたり)
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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。