我正在尝试通过 sql 查询通过 vba 从访问数据中获取数据

Question

Sub Access()
Dim filepath As String
Dim oConn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim ssql As String
Dim strMyPath As String, strDBName As String, strDB As String
'Getting workbook path
Path = Application.ActiveWorkbook.Path
'Finding complete address of the database
filepath = Path & "TBS.accdb"
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                             "Data Source=" & filepath & ";" & _
                             "Jet OLEDB:Engine Type=5;" & _
                             "Persist Security Info=False;"
 'Open a connection.
 Set oConn = New ADODB.Connection
 oConn.Open sConn
 ssql = Range("query ") & Sheets("backened").Range("F3")
'query is a named range containing sql query
MsgBox (Sheets("backened").Range("F3"))
Set RS = New ADODB.Recordset
            Set RS.ActiveConnection = oConn
             RS.Open ssql, oConn, adOpenStatic, adLockReadOnly, adCmdText
With RS
               Sheets("sheet3").Select
                Sheets("Sheet3").Range("F10").CopyFromRecordset RS
                .Close
End With
End Sub

在上面的代码中，文件路径与数据库名称一起存储在"文件路径"中

每当查询是"从中选择*"时，它运行良好，但是当我选择特定列或按操作执行任何组时，它会抛出错误 -运行时错误"-2147467259（8000405）"对象"_recordset"的方法"打开"失败

Answer 1

您的字段名zone是 sql-92 标准中的保留字。对于 SQL，请改用[zone]。请参阅微软错误说明。

通常，最好将字段名称放在方括号内[ ] SQL 查询中。这允许您使用保留字、日期和字符串（包括空格）作为字段名称。