使用VBA访问Excel中的Access数据库以从电子表格传输批量数据 - 查询中出错

I can't seem to figure out what is wrong with the from clause in the following statement:

我似乎无法弄清楚以下语句中的from子句有什么问题:

INSERT INTO [tblFYPNameStudents] ([FypID], [Title], [StudentName]) SELECT * FROM [Excel 12.0;HDR=YES;Data Source='C:\Users\Lenovo\Documents\testwb.xlsm'].Sheet1$A5:C26

It results in 'Error 80040e14, syntax error in From Clause'

它导致'错误80040e14,From Clause中的语法错误'

The code is as follows : (I'm new to VBA and all this but i couldn't figure out what is wrong. I think it has to do with the bit in the from clause where I specify excel and the data source)

代码如下:(我是VBA的新手,所有这一切,但我无法弄清楚有什么问题。我认为这与我指定excel和数据源的from子句中的位有关)

Sub AccImport(tblName As String, _
    hasf As Boolean, _
    tblrange As String)
Set cn = CreateObject("ADODB.Connection")
sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDB_Path
strFile = Workbooks(1).FullName
dsh = tblrange
cn.Open scn
ssql = "INSERT INTO " & tblName
MsgBox ssql
ssql = ssql & "SELECT * FROM [Excel 12.0;HDR=YES;Data Source='" & dbWb & "']." & dsh
MsgBox ssql
cn.Execute ssql
End Sub

1 个解决方案

#1

Your syntax isn't quite right - it should read:

你的语法不太正确 - 它应该是:

ssql = ssql & "SELECT * FROM [Excel 12.0;HDR=YES;Database=" & dbWb & "].[" & dsh & "]"