SQL query an Excel DB: error: Could not find installable ISAM

[Harry Flashman]

I am trying to perform an SQL query on an Excel worksheet, as per one of the examples in Michael Alexander's book "Excel and Access Integration: With Microsoft Office 2007" and I am encountering an error message.

Could not find installable ISAM

To clear I am trying to pull data from one Excel workbook to another using SQL.

I am using Excel Excel 2010 64 bit.
This is the code I am running.

Sub GetData_From_Excel_Sheet()
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Course=C:\TestDB.xlsm" & _
            "Extended Properties=Excel 12.0"
            
MySQL = " Select * From [Data$]"

Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

Sheets("Dest").Select
ActiveSheet.Cells.Clear

ActiveSheet.Cells.Clear

ActiveSheet.Range("A2").CopyFromRecordset MyRecordset


End Sub

I have have Googled this problem and one of the bits advice I have some across a few times that relates specifically to Excel 2010 64 bit is that I need to install the following driver

Microsoft Access Database Engine 2010 Redistributable

However I already have this driver (I installed when I was trying to figure out how to write from Excel to Access).

Does anyone have any other suggestions why am receiving this message. It occurs when my code reaches this line:

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

Any suggestions would be greatly appreciated.

 

[Fazza]

Yes, perfectly correct. That error message is 100% consistent with : no reference set, dim object as ADODB.whatever

It was wrong of me to not notice that earlier. The specific error message does not relate to what I wrote about in post #8. Apologies for creating confusion.

See the code I gave? Which assumes no references. There is : dim MyRecordset as object
That avoids the error message you're seeing.
Then late binding with : set MyRecordset = CreateObject("ADODB.Recordset")

regards

 

[Harry Flashman]

Yes, perfectly correct. That error message is 100% consistent with : no reference set, dim object as ADODB.whatever

It was wrong of me to not notice that earlier. The specific error message does not relate to what I wrote about in post #8. Apologies for creating confusion.

See the code I gave? Which assumes no references. There is : dim MyRecordset as object
That avoids the error message you're seeing.
Then late binding with : set MyRecordset = CreateObject("ADODB.Recordset")

regards

I see... I overlooked Dim MyRecordset As Object

Thanks very much late binding working perfectly now. Greatly appreciate your advice and your patience. Cheers.

 

[Fazza]

BUT I overlooked your question first.

We're all helping each other. Thank you too. regards