在具有 ACE OLEDB 的 Excel 工作表上使用具有列名'Insert Into' SQL 语句失败

Question

所以，我想了解如何将数据存储到工作表中，并希望使用SQL OLEDB为Excel和Standard SQL语句提供。至少对我来说，使用列名插入不起作用。一些代码证明了问题。期望这里显示的这两种形式都可以在W3学校工作SQL插入到声明

Option Explicit
Sub MinimalCompleteVerifiableExample()
    'Tools->References "Microsoft ActiveX Data Objects 2.8 Library"
    Dim wsNew As Excel.Worksheet
    Set wsNew = ThisWorkbook.Worksheets.Add
    wsNew.Cells(1, 1) = "TimeStamp"
    wsNew.Cells(1, 2) = "Path"

    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    Debug.Assert UBound(Split(ThisWorkbook.Name, ".")) > 0  '* Workbook needs to be saved
    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & ThisWorkbook.FullName & ";" & _
           "Extended Properties='Excel 12.0 Macro'"

    Dim rsTestRead As ADODB.Recordset
    Set rsTestRead = New ADODB.Recordset
    rsTestRead.Open "Select * from [" & wsNew.Name & "$] AS P", oConn, adOpenStatic
    Debug.Assert oConn.Errors.Count = 0
    Debug.Assert rsTestRead.Fields.Item(0).Name = "TimeStamp"
    Debug.Assert rsTestRead.Fields.Item(1).Name = "Path"
    Dim sSQL As String
    sSQL = "insert into [" & wsNew.Name & "$] (TimeStamp,Path) VALUES ('31-Dec-2015','C:temp');"  'DOES NOT WORK
    'sSQL = "insert into [" & wsNew.Name & "$]  values ('25-Dec-2015','C:temp')" 'works
    Stop
    oConn.Execute sSQL
    Debug.Assert oConn.Errors.Count = 0
    Stop
End Sub

on获取"插入到语句中的语法错误"的错误消息。

Answer 1

ah。

似乎有人在列名称

周围添加了方括号

    Dim sSQL As String
    sSQL = "insert into [" & wsNew.Name & "$] ([TimeStamp],[Path]) VALUES ('31-Dec-2015','C:temp');"