I am trying to set up VBA code in excel to query an access database and return the results in a recordset. I have the following code but it is giving me and error on the rs.Open line.
I get the following error on the rs.Open line:
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\MyFolder\myDB.accdb;Persist Security Info=False"
sSQL = "Select top 2 from Table1"
Set rs = New ADODB.Recordset
rs.Open sSQL, cn
ActiveWorkbook.Sheets("Control").Cells(6, 1).CopyFromRecordset rs
Set rs = Nothing
Set cn = Nothing
Run-time error '-2147217900 (80040e14)': Automation error