Automation Error when Attempting to Query Access Database Using VBA

Question

I've made the following ADODB object declarations in code.

Dim OConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Set OConn = New ADODB.Connection
Set rs = New ADODB.Recordset

I would like to use the following code to read from a table on a MS Access database file and generate a recordset, rs.

'Get the table name from the search results.
tableName = ThisWorkbook.Sheets("PLC Module Data").Cells(2, 9).Value

'Set the SQL string.
strSql = "SELECT Code, Points, Type, Description, Rating " & _
"FROM " & tableName

'Set the connection string and open the connection to the Access DB.
OConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Q:\AutoCAD Improvements\PLC IO Utility Docs\PLC IO Spreadsheet     
App\PLC IO App\ace_plc.mdb"

OConn.Open

'Open the recordset and error out if nothing is returned
Set rs = OConn.Execute(strSql)
If rs.EOF Then
    MsgBox "No matching records found."
    rs.Close
    OConn.Close
    Exit Sub
End If

I've checked the query statement within the Access file itself and it works fine. I always get the error

Run-time error'-2147217900 (80040e14)': Automation Error

on the line,

Set rs = OConn.Execute(strSql)

If anyone could take a look over my code and determine why this is happening it would be much appreciated. I've looked at similar examples online and it seems like this should be correct.

Have you checked that tableName = ThisWorkbook.Sheets("PLC Module Data").Cells(2, 9).Value is the right table name? What does it return? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Mar 21, 2019 at 15:57
What does the SQL resolve to? Also, have you tried, rs.open strSQL,oconn.connection — Nathan_Sav
– Nathan_Sav, Commented Mar 21, 2019 at 15:59
Just in case, I'd put the field names in []: "SELECT [Code], [Points], [Type], [Description], [Rating] " — Rory
– Rory, Commented Mar 21, 2019 at 16:01
Try this for your SQL string: strSql = "SELECT a.Code, a.Points, a.Type, a.Description, a.Rating FROM [" & tableName & "] AS a" Then add Debug.Print strSql on the next line. If you still get an error using this version, please show us what Debug.Print shows you. — HansUp
– HansUp, Commented Mar 21, 2019 at 16:05
check that oConn is actually open by using If Oconn.state <> adstateopen then msgbox "Not Open" — Harassed Dad
– Harassed Dad, Commented Mar 21, 2019 at 16:14

Jdeloz828 · Accepted Answer · 2019-03-26 12:08:38Z

3

I added the brackets around the tableName string and it works now. Thanks for all the feedback.

'Set the SQL string.
strSql = "SELECT Code, Points, Type, Description, Rating " & _
"FROM [" & tableName & "]"

answered Mar 26, 2019 at 12:08

Jdeloz828

538 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automation Error when Attempting to Query Access Database Using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related