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.

 

[Norie]

Did you register the driver?

 

[Harry Flashman]

I am not sure what that means. I assumed I had installed it properly because I was able to solve my earlier problem (writing from Excel to Access). That said this link ...
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center
... gave instructions on installing the driver, but did not mention anything about registering the drver.

What do I need to do to register the driver? Thanks.

 

[Harry Flashman]

I found a couple of errors in my typing. This works.

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\TestDB.xlsm;" & _
            "Extended Properties=Excel 12.0"

This is trouble when trying learn to write code, when you are learning it is hard to tell if errors occur because you don't know how to write the code (misunderstood certain concepts) or if is something a simple as a typo.

Any problem solved. Cheers.

Here is the full code that actually works. It is pretty handy being able to run an SQL query on a worksheet.

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 Source=C:\TestDB2.xlsm;" & _
            "Extended Properties=Excel 12.0"
                        
MySQL = " Select * From [Dest$] Where ID = 2"

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

Worksheets("Dest2").Select
ActiveSheet.Cells.Clear

'create field headers
    i = 0
    With Worksheets("Dest2").Range("A1")
       For Each fld In MyRecordset.Fields
           .Offset(0, i).Value = fld.Name
           i = i + 1
       Next fld
    End With

ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

End Sub

 

[Norie]

Ah, a missing ;.

I sometimes avoid using connection strings and use a Connection object, setting the Provider, DataSource etc directly.

Something like this.

Dim MyCN As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

    Set MyCN = CreateObject("ADODB.Connection")
    
    MyCN.Provider = "Microsoft.ACE.OLEDB.12.0"
    MyCN.Properties("Data Source") = "C:\TestDB2.xlsm"
    MyCN.Properties("Extended Properties") = "Excel 12.0"

                            
    MySQL = " Select * From [Dest$] Where ID = 2"
    Set MyRecordset = Create("ADODB.Recordset")

    MyRecordset.Open MySQL, MyCN, adOpenStatic, adLockReadOnly

    ' get headers and data

    MyRecordset.Close

    Set MyRecordset = Nothing

    MyCN.Close

    Set MyCN = Nothing

 

[Harry Flashman]

That looks neater. Thanks very much I'll try that approach.

 

[Harry Flashman]

I have one question about this line:

 Set MyCN = CreateObject("ADODB.Connection")

This is an example of late binding I believe. I was under the impression that with late binding you don't need to set references, but I have followed your example and I am still getting the message User-type not defined. Have I misunderstood late binding, or have I misunderstood your example? Did you need to set references?

 

[Fazza]

You're correct. Late binding - no references needed. However, the code uses ADO constants, hence the error message.

MyRecordset.Open MySQL, MyCN, adOpenStatic, adLockReadOnly

Without the reference, these constant won't be known. Set them to their numeric values,

MyRecordset.Open MySQL, MyCN, 3, 1

FYI, if wanting only a recordset object it will implicitly create its own connection in the background so the connection object isn't required. I'll post again with some sample code.

regards

 

[Fazza]

something like

'untested. If there is a problem, please check this connection string first
Const str_Connection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Dim MyRecordset As Object
Dim MySQL As String

MySQL = "SELECT * FROM [Dest$] Where ID = 2"

Set MyRecordset = Create("ADODB.Recordset")

MyRecordset.Open MySQL, Replace$(str_Connection, "filename", "C:\TestDB2.xlsm"), 3, 1

' get headers and data

MyRecordset.Close
Set MyRecordset = Nothing

 

[Harry Flashman]

You're correct. Late binding - no references needed. However, the code uses ADO constants, hence the error message.

MyRecordset.Open MySQL, MyCN, adOpenStatic, adLockReadOnly

Without the reference, these constant won't be known. Set them to their numeric values,

MyRecordset.Open MySQL, MyCN, 3, 1

FYI, if wanting only a recordset object it will implicitly create its own connection in the background so the connection object isn't required. I'll post again with some sample code.

regards

But even without those ADO constants I am still needing to set the reference for Microsoft ActiveX Data Objects 6.1 Library

For example the code below throws up the error message User-Type not defined (ie Dim MyCN As ADODB.Connection) unless I set the above reference. Does that sound right? I must admit I am not experienced at using late binding.

Sub QuotesDataFromAccess()
Dim MyCN As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String
Dim fName As String, fPath As String, dSource As String

fName = ThisWorkbook.Name
fPath = ThisWorkbook.Path
dSource = fPath & "\" & fName

Set MyCN = CreateObject("ADODB.Connection")

MyCN.Provider = "Microsoft.ACE.OLEDB.12.0"
MyCN.Properties("Data Source") = dSource
MyCN.Properties("Extended Properties") = "Excel 12.0"

End Sub