Unanswered: Import Table Into Excel Using Excel VBA
Hey guys, got one for you. I am trying to copy the data from an Access query to a sheet in Excel. Here is the code I am using to connect to the database:
dbLoc = wsHome.Range("b6").Value
'Create the connection instance for the database
Set dbcnDatabase = New Connection
'Make the connection to the database
dbcnDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info = False; Data Source = " & dbLoc
'Open the connection to the database
'Create the recordset instance
Set rsData = New Recordset
'Set the parameters of the recordset
rsData.Source = "SELECT * From [Query]"
I believe that the connection is working because the macro is able to write the fieldnames from the query. But for some reason the macro can't see the records in the query. The query works fine in Access, but it shows up with 0 records when accessed through the Excel VBA.