I would like to be able to open a connection between an Excel 2007 file and an Access 2007 file to bring in a table from an already established Access database. I know that to do so, you need certain references enabled in VBA but I am not sure which ones I need. I suspect that is why I get a runtime error 3706 when I try to run the following code. Can anyone point me in the right direction of which references need to enabled in order to connect Excel 2007 to Access 2007?
Code:
sub macro1(control As IRibbonControl)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Connection
Dim sQRY As String
Dim strFilePath As String
strFilePath = "H:\Tracker Database\Database.accdb"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.12.0;" & _
"Data Source=" & strFilePath & ";"
sQRY = "SELECT tblclstrack.*FROM tblclstrack"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("B2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub