1

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
4
  • 1
    Can you share the exact error message you're getting? Commented Jun 5, 2017 at 16:26
  • '-2147217900: Syntax error in INSERT INTO statement.' Commented Jun 5, 2017 at 16:35
  • 2
    What does the SQL show if you print your cmd text? Commented Jun 5, 2017 at 16:37
  • If you provide ? in your sql statement, I think the mystery will be solved. Double check the data types when you enter the values. Commented Jun 5, 2017 at 16:44

1 Answer 1

2

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 (?,?,?,?,?,?,?,?);"
Sign up to request clarification or add additional context in comments.

2 Comments

That was the issue. Thanks!
Great! Glad to help. Please accept solution to confirm resolution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.