"insert into" Excel 语句出现语法错误

Question

执行"insert into"语句时遇到问题

我尝试在Excel VB:中运行

insertQ = "INSERT INTO [Sheet1$] (Stockgroup, Stockcode, transdate, LastUpdate, time) VALUES ('990000', 'birthday', '21/12/2101', '','" & Time & "')"

但它显示语法错误。

如果我去掉最后两个字段，它就会起作用

insertQ = "INSERT INTO [Sheet1$] (Stockgroup, Stockcode, transdate) VALUES ('990000', 'birthday', '21/12/2101')"

你以前见过这个吗？@@

Answer 1

这对我有效：

Sub sof20321516Insert2Excel()
  Dim strSQL
  Dim cnn, rst
  On Error GoTo ErrHandler
  Set cnn = CreateObject("ADODB.Connection")
  cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" _
    & ";Data Source=" & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;HDR=YES"""
  cnn.Open
'
  strSQL = "INSERT INTO [Sheet1$] (Stockgroup, Stockcode, transdate, LastUpdate, `time`) VALUES ('990000', 'birthday', '21/12/2101', '','" & Time & "')"
  Set rst = cnn.Execute(strSQL)
'
' close ADO objects:
'
' rst.Close
  Set rst = Nothing
  cnn.Close
  Set cnn = Nothing
  Exit Sub
ErrHandler:
  MsgBox Err.Description
  Set cnn = Nothing
End Sub

使用ADO时，字段名time应该是反引号。在注释中，关键字time不能用作SQL字符串中的空字段名称。