VBA・SQL｜AccessでINSERT INTOに変数を使う方法

VBAでSQLを使ってAccessにレコードを追加するのはINSERT INTO文を使います。

INSERT INTO文に変数を使ってレコードを追加したいという質問がよく寄せられます。

INSERT INTOに変数を使うには、以下のように記述します。

"INSERT INTO テーブル名 VALUES('" & 変数1 & "');"

今回は、レコードを追加する際のINSERT INTO文に変数を使う方法や、変数に置き換える際のコツについて徹底的にお伝えしていきますね☆

INSERT INTO文に変数を使うには、VALUES内の値が入る部分を「 & 変数 & 」に置き換えます。

その際に、変数を「”（ダブルクォーテーション）」の外へ出してやります。

文章の説明を理解するよりも、まずは実際にVALUES内の値を変数に置き換えてみたものを見てみましょう。

以下のテーブルにそれぞれ値を追加してみます。

テーブル名「商品マスタ」
商品コード→B3
商品名→サーモン
価格→200
出荷元→北海道

【VALUES内に値を入れたときの記述】

vSQL = "INSERT INTO 商品マスタ VALUES('B3','サーモン',200,'北海道');"

VALUES内の値は、文字列は「’（シングルクォーテーション）」で囲み、一方で数字は囲みません。

【変数に置き換えたときの記述】

henSu1 = B3
henSu2 = サーモン
henSu3 = 200
henSu4 = 北海道
vSQL = "INSERT INTO 商品マスタ VALUES('" & henSu1 & "','" & henSu2 & "'," & henSu3 & ",'" & henSu4 & "');"

SQL自体は文字列なので「”（ダブルクォーテーション）」で囲みます。

一方で変数は「”」で囲むと文字列とみなされてしまいます。そこで、文字列を連結する＆演算子を利用しています。

つまり、上記のコードは以下のように分解できます。

vSQL = "INSERT INTO 商品マスタ VALUES('"
& henSu1 & 　←ここに商品コードB3が入る
"','"
& henSu2 & 　←ここにサーモンが入る
"',"
& henSu3 & 　←ここに200が入る
",'"
& henSu4 & 　←ここに北海道が入る
"');"

このように分解できます。

INSERT INTOに変数を使うコツ

それぞれの値を変数に置き換えるコツは以下のとおりです。

最初に値を入れたSQLを作成する。
それぞれの値を変数に置き換える。
変数は＆演算子を使って「 & 変数 & 」になる。
変数の前後は「”（ダブルクォーテーション）」で区切る。

この工程で、比較的スムーズにINSERT INTO文に変数を入れることができます。

それでは、INSERT INTO文に変数を使ったサンプルコードを以下に示します。

【INSERT INTO文に変数を使ったサンプルコード】

Sub INSERTINTOに変数を使う()
  Dim cn As ADODB.Connection
  Dim henSu1 As String '商品コードの値
  Dim henSu2 As String '商品名の値
  Dim henSu3 As String '価格の値
  Dim henSu4 As String '出荷元の値

  'Connectionオブジェクトを作成
  Set cn = New ADODB.Connection
  'データベースを開く
  cn.Open _
  "Provider=Microsoft.ACE.OLEDB.16.0;" & _
  "Data Source=" & ThisWorkbook.Path & "\テスト.accdb;"

  Dim vSQL As String
  henSu1 = "B3"
  henSu2 = "サーモン"
  henSu3 = 200
  henSu4 = "北海道"

  vSQL = "INSERT INTO 商品マスタ VALUES('" & henSu1 & "','" & henSu2 & "'," & henSu3 & ",'" & henSu4 & "');"
  'SQLを実行
  cn.Execute vSQL

  cn.Close
End Sub

【実行結果】

まとめ

今回は、INSERT INTO文に変数を使う方法についてお伝えしました。

INSERT INTOに変数を使うには、以下のように記述します。

"INSERT INTO テーブル名 VALUES('" & 変数1 & "');"

それぞれの値を変数に置き換えるコツは以下のとおりです。

最初に値を入れたSQLを作成する。
それぞれの値を変数に置き換える。
変数は＆演算子を使って「 & 変数 & 」になる。
変数の前後は「”（ダブルクォーテーション）」で区切る。

INSERT INTO文に変数を使うことは比較的多いですので、ぜひ覚えておいてくださいね☆