2020/8/16

【Excel VBA】Accessのレコード追加【SQLのINSERTでできます】

Excel VBA

Excel VBAでAccessのレコードを追加する方法をご紹介します。Excel VBAからSQL文のINSERTを使ってAccessのレコードを追加することができます。

はじめに

訪問ありがとうございます。この記事ではExcel VBAからAccessへ接続してレコードを追加する方法についてご紹介します。

Excel VBAでAccessのレコードを追加する方法がわかれば、Access VBAがわからなくてもExcel VBAでAccessのレコードを自由に追加することができます。

扱うデータ量が増えてきたり複数人が同時に作業する必要があるといった事情でAccessを使ってみたいと考えている方に参考になるかと思います。

この記事でわかること

Excel VBAでSQL文を使ってAccessのレコードを追加する方法

Excel VBAでAccessのレコード追加
Excel VBAコード

オブジェクト作成（Accessへ接続）
SQL文（INSERT）
SQL実行（レコード追加）
後処理

おわりに

Excel VBAでAccessのレコード追加

Excel VBAでAccessのレコードを追加する方法です。

フィールドの『名前』、『身長』、『体重』がそれぞれ『鈴木』、『190』、『68』であるレコードを追加します。

フィールド『ID』は自動で連番が追加されます。

Accessのレコードを追加した結果

新しくレコードが追加されました。Excel VBAのコードについて説明していきます。

Excel VBAコード

Accessのレコードを追加するExcel VBAコードです。

'Accessのレコードを追加する Sub Test4() Dim DBpath As String 'Accessファイルのフルパス Dim adoCn As Object 'Accessへ接続用のオブジェクト Dim strSQL As String 'SQL文 'Accessファイルへ接続する Set adoCn = CreateObject("ADODB.Connection") 'Accessへ接続用のオブジェクトを作成 DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するAccessファイルのフルパス adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く 'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Access（～2003）ファイルを開く 'レコード追加のSQL文 strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('鈴木','190','68')" 'レコード追加 '---変数を使う場合--- Dim CurName, CurTall, CurWeight CurName = "鈴木" CurTall = 190 CurWeight = 68 'レコード追加のSQL文 strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('" & CurName & "','" & CurTall & "','" & CurWeight & "')" '--------------------- 'SQLの実行（レコードの更新・追加・削除の場合） adoCn.Execute strSQL 'SQLを実行 '後処理 adoCn.Close 'Accessへの接続を解除する Set adoCn = Nothing 'Accessへの接続用のオブジェクトを開放 End Sub

少し長いですが、Accessへレコードを追加するのに重要なところは赤色の部分です。

そのほかは、テンプレみたいなものですのであまりに気にする必要はありません。

では、Excel VBAコードについて説明していきます。

オブジェクト作成（Accessへ接続）

Accessへ接続するためのオブジェクトを作成します。

これはテンプレみたいなものですので、あまり重要視する必要はありません。

'Accessファイルへ接続する Set adoCn = CreateObject("ADODB.Connection") 'Accessへ接続用のオブジェクトを作成 DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するAccessファイルのフルパス adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル（2007～）を開く 'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Access（～2003）ファイルを開く

ファイルパスの部分だけ取得したいAccessファイルに合わせて変更するだけでいいです。

Accessのバージョンでコードが変わりますので、Access 2007以降とAccess2003以前のExcel VBAコードを載せておきます。

SQL文（INSERT）

ここが重要です。

Accessにレコードを追加するSQL文です。『INSERT』を使います。

'レコード追加のSQL文 strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('鈴木','190','68')" 'レコード追加

テーブル『DB』のフィールド『名前、身長、体重』に対し『鈴木、190、68』であるレコードを追加する。という意味になります。

フィールド『ID』は自動で連番が追加されますので記載する必要はありません。

変数を使う場合は次のようにします。

'変数を使う場合 Dim CurName, CurTall, CurWeight CurName = "鈴木" CurTall = 190 CurWeight = 68 'レコード追加のSQL文 strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('" & CurName & "','" & CurTall & "','" & CurWeight & "')"

『&』と『"』で変数を囲っています。

エクセルでセルに文字列の数式を入れる方法と同じです。

SQL実行（レコード追加）

ここも重要です。

作成したSQLを実行するExcel VBAコードです。

'SQLの実行（レコードの更新・追加・削除の場合） adoCn.Execute strSQL 'SQLを実行

これでAccessへレコードが追加されます。

後処理

最後に後処理をして終了です。

これはテンプレなので気にせず貼り付ければいいです。

'後処理 adoCn.Close 'Accessへの接続を解除する Set adoCn = Nothing 'Accessへの接続用のオブジェクトを開放

Accessへの接続を解除して、作成したオブジェクトを開放しています。

おわりに

Excel VBAでSQLを使ってAccessへ接続してレコードを追加する方法をご紹介しました。

データベースを複数人で共有して同時に更新したい場合やデータ容量が増えてきた場合にAccessは便利です。

Accessをデータベースとして使う場合は、Accessの接続制限でファイルが壊れたり動かなくなってしまわないようにExcel VBAから接続する方法をおすすめします。

Excel VBAから接続する方法を使えば入出力フォームもExcel VBAで作成できるのでAccess VBAの知識がなくて簡単です。

参考になればと思います。最後までご覧くださいましてありがとうございました。