I am trying to get data from access databse via vba through sql query

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

In the above code filepath along with database name is stored in "filepath"

Whenever the query is "select * from " it runs fine but when I select a particular columns or perform any group by operation it throws an error - Run time error '-2147467259(8000405)' method 'open' of object '_recordset' failed

Answer 1

Your fieldname zone is a reserved word in the sql-92 standard. For your SQL, use [zone] instead. See the microsoft error description.

In general, it's good practice to put fieldnames inside square brackets [ ] in your SQL query. This allows you to use reserved words, dates and strings including blanks as fieldnames.