英文:
Runtime error involving sQRY in VBA code to import AccessDB queries to Excel
问题
我一直在编写一个模块，用于将Access数据库查询（希望将来可以多个）导入Excel工作表，但始终遇到相同的错误：
运行时错误'-2147217900（80040e14）'：
FROM子句中的语法错误。
我用来导入数据库查询的代码如下：
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = "P:\DIRT V6-4 W10.accdb"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";"
sQRY = "SELECT * FROM [AIS jobs outstanding]"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
有任何解决方案吗？我已经有一段时间没有使用VBA/SQL了，任何帮助都会有用。谢谢。
英文:
I've been coding a module to import an access database query (hopefully several in the future) into an excel worksheet but keep on getting the same error:
Run-time error '-2147217900 (80040e14)':
Syntax error in FROM clause.
The code I am using to import the database query is below:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = "P:\DIRT V6-4 W10.accdb"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";"
sQRY = "SELECT * FROM AIS jobs outstanding"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub
Any idea what the issue solution could be? I have not used vba/sql in some time and any help would be of use. Thanks.
答案1
得分: 4
"AIS jobs outstanding" 导致错误，因为它包含空格。
sQRY = "SELECT * FROM [AIS jobs outstanding]"
英文:
AIS jobs outstanding is causing the error because it contains spaces.
sQRY = "SELECT * FROM [AIS jobs outstanding]"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论