MS EXCEL到MS ACCESS .accdb数据库来自VBA SQL语法错误

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

我完全卡住了,拔掉了我的头发。

From Excel VBA I have two sets of code:

从Excel VBA我有两组代码:

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

1-通过SQL语句创建表是MS Access

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

2-使用SQL填充新创建的表,使用For循环

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.

INSERT INTO语句中的运行时错误,语法错误。

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.

好的,所以起初我认为我的SQL字符串中一定有错误。因此,我将确切的SQL字符串放入Access Query Builder并运行SQL命令,然后将其导入到表中就好了。

What am I missing?

我错过了什么?

1 个解决方案

#1

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.

问题可能是由于字段名称。有一个名为CDate的函数。 Open和Last都是Jet保留字。请参阅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.

括号可能足以让您的INSERT正常工作。但是,如果可能,请考虑重命名字段。

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.

该链接页面还提到了Allen Browne的Database Issue Checker Utility。您可以下载该实用程序并使用它来检查数据库中的其他问题名称。它还可以提醒您可能不会影响当前INSERT问题的其他问题,但在其他情况下可能会导致问题。

#1