0

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.

June7
  • 19,874
  • 8
  • 24
  • 34
Jdeloz828
  • 53
  • 7
  • 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 Mar 21 '19 at 15:57
  • What does the SQL resolve to? Also, have you tried, `rs.open strSQL,oconn.connection` – Nathan_Sav Mar 21 '19 at 15:59
  • Just in case, I'd put the field names in []: `"SELECT [Code], [Points], [Type], [Description], [Rating] "` – Rory Mar 21 '19 at 16:01
  • 2
    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 Mar 21 '19 at 16:05
  • 1
    check that oConn is actually open by using If Oconn.state <> adstateopen then msgbox "Not Open" – Harassed Dad Mar 21 '19 at 16:14
  • Is there anything going on in this database, like compacting/repairing deleting/reimporting data? – Nathan_Sav Mar 21 '19 at 16:25
  • Cannot reproduce issue. Please use proper [error handling](https://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling) and report specific runtime error and message. – Parfait Mar 21 '19 at 17:02
  • Thanks for all the feedback. @HansUp, it turns out all I needed were the brackets around the tableName string as you suggested. – Jdeloz828 Mar 21 '19 at 18:59

1 Answers1

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 & "]"
Jdeloz828
  • 53
  • 7