CODEWITHSUNDEEP
sqlexcelvbams-access
Mike D
Mike D

Reputation: 33

Excel - SQL 'INSERT INTO' Query Syntax Error

I'm attempting a simple insertion query to an MS Access database with Data from Excel. I'm using a parameterized query with hardcoded values for testing. Running the query directly in Access works, but attempting to execute it via VBA results in a syntax error. The query is currently inserting every field except for the auto-generated ID in the Access table.

Sub Test_SQL()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim result
    Dim id As Integer

    Dim year As Integer
    Dim month As String
    Dim combocode As String
    Dim credamt As Double
    Dim qty As Integer
    Dim itemcode As String
    Dim sepbundleamt As Double
    Dim prodname As String

    '*********query variables********************
    year = 2017
    month = "July"
    combocode = "COMBCOD1"
    credamt = 420
    qty = 21
    itemcode = "ITEMCOD1"
    sepbundleamt = 12
    prodname = "Test Product"

    '********open DB connection and execute insertion******
    On Error GoTo ErrorHandler

    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0;"
        .ConnectionString = [redacted]
        .Open
    End With

    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdText
        .CommandText = "INSERT INTO SB_DSLR (Year, Month, ComboCode, CreditAmt, Qty, ItemCode, SepBundleAmt, ProductName) VALUES (?,?,?,?,?,?,?,?);"
    End With
    With cmd.Parameters
        .Append cmd.CreateParameter("Year", adInteger, adParamInput, 4, year)
        .Append cmd.CreateParameter("Month", adWChar, adParamInput, 10, month)
        .Append cmd.CreateParameter("ComboCode", adWChar, adParamInput, 10, combocode)
        .Append cmd.CreateParameter("CreditAmt", adDouble, adParamInput, 10, credamt)
        .Append cmd.CreateParameter("Qty", adInteger, adParamInput, 10, qty)
        .Append cmd.CreateParameter("ItemCode", adWChar, adParamInput, 8, itemcode)
        .Append cmd.CreateParameter("SepBundleAmt", adDouble, adParamInput, 10, sepbundleamt)
        .Append cmd.CreateParameter("ProductName", adWChar, adParamInput, 100, prodname)
    End With

    cmd.Execute 
    conn.Close
    Set cmd = Nothing
    Exit Sub

ErrorHandler:
    MsgBox (Err.Number & ": " & Err.Description)
    If conn.State = adStateOpen Then
        conn.Close
    End If
    Set cmd = Nothing
End Sub

Upvotes: 1

Views: 774

Answers (1)

Parfait
Parfait

Reputation: 107642

There are multiple reasons for that general error but the fact that same query works in the MSAccess.exe suggests reserved words is the cause.

Indeed, Month and Year are Jet reserved words and Year is an MS Access reserved word. Both are named functions and parameters to other methods. Sometimes the .exe can be lenient whereas ODBC connections are more restrictive. Therefore, consider escaping such words with backticks or square brackets, or altogether avoiding such words.

.CommandText = "INSERT INTO SB_DSLR (`Year`, `Month`, ComboCode, CreditAmt, Qty, ItemCode, SepBundleAmt, ProductName) VALUES (?,?,?,?,?,?,?,?);"

.CommandText = "INSERT INTO SB_DSLR ([Year], [Month], ComboCode, CreditAmt, Qty, ItemCode, SepBundleAmt, ProductName) VALUES (?,?,?,?,?,?,?,?);"

Upvotes: 2

Related Questions