MS EXCEL to MS ACCESS .accdb Database from VBA SQL Syntax error

Question

I am completely stuck and pulling out my hair on this one..

From Excel VBA I have two sets of code:

1- To Create a table is MS Access via a SQL statement

2- Populated newly created table with a For loop, also using SQL

The first set of code works perfectly, so I know that my connection string is working properly.

Here is the first set:

Sub Create_Table()
    'Add Reference to Microsoft ActiveX Data Objects 2.x Library
    Dim strConnectString        As String
    Dim objConnection           As ADODB.Connection
    Dim strDbPath               As String
    Dim strTblName              As String


    Dim wCL                     As Worksheet
    Dim wCD                     As Worksheet


    Set wCL = Worksheets("Contract List")
    Set wCD = Worksheets("Contract Data")

    'Set database name and DB connection string--------
    strDbPath = ThisWorkbook.Path & "\SpreadPrices.accdb"
    '==================================================
    strTblName = wCL.Range("TableName").Value
    strConnectString = "Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDbPath & ";"

    'Connect Database; insert a new table
    Set objConnection = New ADODB.Connection
   On Error Resume Next
    With objConnection
        .Open strConnectString
        .Execute "CREATE TABLE " & strTblName & " (" & _
                 "[cDate] text(150), " & _
                 "[Open] text(150), " & _
                 "[High] text(150), " & _
                 "[Low] text(150), " & _
                 "[Last] text(150), " & _
                 "[cChange] text(150), " & _
                 "[Settle] text(150), " & _
                 "[cVolume] text(150), " & _
                 "[OpenInterest] text(150))"

    End With

    Set objConnection = Nothing

End Sub

Mentioned before that code works perfectly. The bug is on the following set of code used to populate the table.

Here it is:

Sub InsertSQL()

'Add Reference to Microsoft ActiveX Data Objects 2.x Library
    Dim strConnectString        As String
    Dim objConnection           As ADODB.Connection
    Dim strDbPath               As String
    Dim strTblName              As String
    Dim lngRow                  As Long
    Dim strSQL                  As String


    Dim wCL                     As Worksheet
    Dim wCD                     As Worksheet


    Set wCL = Worksheets("Contract List")
    Set wCD = Worksheets("Contract Data")

    'Set database name and DB connection string--------
    strDbPath = ThisWorkbook.Path & "\SpreadPrices.accdb"
    '==================================================
    strTblName = wCL.Range("TableName").Value
    strConnectString = "Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDbPath & ";"

    'Connect Database; insert a new table
    Set objConnection = New ADODB.Connection
    'On Error Resume Next
    With objConnection
        .Open strConnectString

         For lngRow = 2 To Range("NumberRows").Value

            strSQL = "INSERT INTO " & strTblName & " (" & _
            "cDate, Open, High, Low, Last, cChange, Settle, cVolume, OpenInterest)" & _
            " VALUES ('" & _
            wCD.Cells(lngRow, 1) & "' , '" & _
            wCD.Cells(lngRow, 2) & "' , '" & _
            wCD.Cells(lngRow, 3) & "' , '" & _
            wCD.Cells(lngRow, 4) & "' , '" & _
            wCD.Cells(lngRow, 5) & "' , '" & _
            wCD.Cells(lngRow, 6) & "' , '" & _
            wCD.Cells(lngRow, 7) & "' , '" & _
            wCD.Cells(lngRow, 8) & "' , '" & _
            wCD.Cells(lngRow, 9) & "')"

         wCL.Range("A1").Value = strSQL

        .Execute strSQL
        Next lngRow

    End With

    Set objConnection = Nothing

End Sub

The error I receive is:

Run-time error, Syntax error in INSERT INTO statement.

Ok, so at first thought I think there must be a error in my SQL string. So I take the exact SQL string and toss it into Access Query Builder and run the SQL command and it imports into the table just fine.

What am I missing?

Can you Debug.Print strSQL right above .Execute strSQL to see exactly what the broken INSERT statement says. — Newd
Perhaps it helps to put the table and column names into [].. something like strSQL = "INSERT INTO [" & strTblName & "] (" & _ "[cDate], [Open], [High], [Low], [Last], [cChange], [Settle], [cVolume], [OpenInterest])" & _... ? — Peter Schneider
The problem could also be with the values. You do not check if they contain a single quote... so the best would be to see the strSQL as @Newd proposed! — Peter Schneider
Debug.Print returns: INSERT INTO CLN2015 (cDate, Open, High, Low, Last, cChange, Settle, cVolume, OpenInterest) VALUES ('5/5/2015' , '60.19' , '62.2' , '59.91' , '58' , '1.34' , '61.5' , '149025' , '249452') If I run this string in SQL the command works perfectly. — Aaron Soderstrom

HansUp HansUp · Accepted Answer · 2015-05-06T16:24:55

The problem may be due to field names. There is a function named CDate. Open and Last are both Jet reserved words. See Problem names and reserved words in Access.

Enclose those problem field names in square brackets to avoid confusing the database engine:

"[cDate], [Open], High, Low, [Last], cChange, Settle, cVolume, OpenInterest)"

The brackets may be enough to get your INSERT working. However consider renaming the fields if possible.

That linked page also mentions Allen Browne's Database Issue Checker Utility. You can download that utility and use it to examine your database for other problem names. It can also alert you to other issues which may not affect the current INSERT problem, but could cause trouble in other situations.

MS EXCEL to MS ACCESS .accdb Database from VBA SQL Syntax error

1 Answers