【Access】VBAでSQLを実行した場合のみエラーが発生する場合の対処方法

Access上では実行されるSQLが、VBA上から実行した場合のみ「構文エラー」が発生する場合の対処方法について説明してきます。

現象再現コード

以下のSQLをExcelで実行する場合のコードです。

INSERT INTO stock (id,item_name,quantity,memo) VALUES (1,"カイロ",100,"特価品");

VBAコード

Sub Macro1()
    Dim filePath As String: filePath = ThisWorkbook.Path & "\Database1.accdb" 'データベースのファイルパス
    
     'Accessファイルに接続
    Dim adoCn As Object: Set adoCn = CreateObject("ADODB.Connection")
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";"
    
    'SQLを実行
    Dim addData As Variant: addData = Array(1, """カイロ""", 100, """特価品""")
    Dim strSQL As String: strSQL = "INSERT INTO stock (id,item_name,quantity,memo) VALUES (" & Join(addData, ",") & ");"
    Dim adoRs As Object: Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.Open strSQL, adoCn
    
    'コネクションのクローズ
    Set adoRs = Nothing
    adoCn.Close
    Set adoCn = Nothing
End Sub

SQLをAccessのクエリデザインから実行した場合は正常に実行されますが、ExcelVBA上で実行した場合は構文エラーが発生します。

対処方法

VBAで実行するSQLを修正する

VBAで実行するSQLのフィールド名を［］で囲むとエラーを回避できます。

INSERT INTO stock ([id],[item_name],[quantity],[memo]) VALUES (1,"カイロ",100,"特価品");

例えば、現象再現コードでは以下の個所のコードを

Dim strSQL As String: strSQL = "INSERT INTO stock ([id],[item_name],[quantity],[memo]) VALUES (" & Join(addData, ",") & ");"

以下のコードに置き換えればOKです。

フィールド名を修正する

Accessの予約語をフィールド名に使用している場合は、予約語以外の名称に変更することでエラーを回避できます。予約語は大文字小文字は区別されないようなので注意してください。ちなみに今回の例では、「memo」が予約語に当たります。

基本的にはAccessから予約語をフィールド名に使用する場合は警告が表示されます。ただし今回の「memo」のように警告が表示されない場合があるので注意が必要です。