【ExcelVBA】ExcelのマクロからMicrosoft Accessにデータを追加するには

本記事では、ExcelのマクロからMicrosoft Accessにデータを追加する方法についてご説明します。

ExcelのマクロからMicrosoft Accessにデータを追加する方法

ExcelのマクロからMicrosoft Accessにデータを追加するには、次の流れの通りにコードを書いていきます。

STEP.1
Microsoft Accessへの接続情報取得
Microsoft Accessに接続するための接続情報を取得します。
このMicrosoft Accessに接続するための接続情報は、Microsoft Accessに接続するための「鍵」みたいなものなので必ずコードに記述する必要があります。
STEP.2
Microsoft Accessに接続
接続情報が取得出来たら、その接続情報をもとにMicrosoft Accessに接続します。
Microsoft Accessに接続するには、ConnectionオブジェクトのOpenメソッドを使います。
STEP.3
Recordsetオブジェクトのインスタンスの生成
Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、Microsoft Accessにデータを追加するのに必要です。
STEP.4
データを追加するSQL文をもとにレコードセットを開いてデータを追加
データを追加するSQL文をもとにレコードセットを開いてデータを追加します。
レコードセットを開いてデータを追加するには、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に接続するには
実際のSQL(Insert)文

コード(例)の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」ボタンをクリックします。

  1. 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あたり)

最後に

本記事では、ExcelのマクロからMicrosoft Accessにデータを追加する方法についてご説明しました。

以下の処理を実装することで、Microsoft Accessにデータを追加することができます。

  • ①Microsoft Accessへの接続情報取得
  • ② ①の情報を参照してMicrosoft Accessに接続
  • ③Recordsetオブジェクトのインスタンスの生成
  • ④データを追加するSQL文をもとにレコードセットを開いてデータを追加

コードを実装する際は、先ほどお見せしたコードの例を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら