could not find installable ISAM vba

Question

I read many articles about this subject but cannot find any solution to my problem. What I needed to do is to get values from a table in Sheet1 in my active Workbook using SQL. When I run the code below I get "could not find installable ISAM" error message. There seems to be a problem in my connection string.

I am using Excel 2010. To solve my problem I activated "Microsoft ActiveX Data Objects 6.1 Library"(Which is the latest version).

I am a starter in Macros that is why I might be missing a simple point. If so I am sorry for consuming your time.

Thanks

Cagri

My VBA code;

Sub GC_Button_Click()

Dim Giris_Zamani As Recordset
Dim Cikis_Zamani As Recordset
Dim StrGiris_Zamani As String
Dim StrCikis_Zamani As String
Dim Donem As Integer
Dim Gun As Integer
Dim Personel As String
Dim RowCount As Long
Dim CalismaSaati As Integer

Dim Conn As ADODB.Connection
Dim SQL_Giris As String
Dim SQL_Cikis As String
Dim RowNumber As Integer
Dim DayNumber As Integer
Dim strWorkbook As String

strWorkbook = Application.ActiveWorkbook.FullName
RowCount = Sheets(2).UsedRange.Rows.Count
Sheets(1).Select
Range("A2:A900000").Copy
Sheets(2).Select
Cells(8, 1).Select
ActiveSheet.Paste

Range("A8:A900000").RemoveDuplicates Columns:=Array(1)
Range("A8:A900000").Sort Key1:=Range("A8"), Order1:=xlAscending,    Header:=xlNo

Sheets(2).Select
Donem = Cells(2, 8)

Set Conn = New ADODB.Connection
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strWorkbook & "; Extended Properties =  Excel 12.0 Macro; HDR=YES"
.Open
End With

For RowNumber = 8 To RowCount
For DayNumber = 1 To 31

Personel = Cells(RowNumber, 1)
Gun = DayNumber

SQL_Giris = "SELECT [ZAMAN] FROM [Sheet1] WHERE [Personel Adi Soyadi]= '" + Personel + "' AND [Giris / Cikis]='Giris' AND [DÖNEM]=" + CStr(Donem) + " AND [GÜN]=" + CStr(Gun) + ""
SQL_Cikis = "SELECT [ZAMAN] FROM [Sheet1] WHERE [Personel Adi Soyadi]= '" + Personel + "' AND [Giris / Cikis]='Cikis' AND [DÖNEM]=" + CStr(Donem) + " AND [GÜN]=" + CStr(Gun) + ""

Set Giris_Zamani = Conn.Execute(SQL_Giris)
Set Cikis_Zamani = Conn.Execute(SQL_Cikis)

StrGiris_Zamani = Giris_Zamani.Fields(0).Value
StrCikis_Zamani = Cikis_Zamani.Fields(0).Value

CalismaSaati = Hour(TimeValue(StrCikis_Zamani) - TimeValue(StrGiris_Zamani))
Cells(RowNumber, DayNumber + 1).Value = CalismaSaati
Next DayNumber
Next RowNumber

End Sub

Answer 1

Your connection string is not formatted correctly. The Extended Properties value should be enclosed in quotes ="Excel 12.0 Macro; HDR=YES", as both settings are part of the extended properties. The full connection string should be formatted:

"Data Source=<file path>; Extended Properties=""Excel 12.0 Macro; HDR=<YES/NO>"""

And your code should be:

"Data Source=" & strWorkbook & "; Extended Properties=""Excel 12.0 Macro; HDR=YES"""

*Note that HDR should be Yes only if you have headers.

Answer 2

I had this error popping up when attempting to connect from Excel VBA to MySQL DB through MySQL 8.0 ODBC Connector. In that same application, I’m also using the JET engine (or ACE – depending on the version) to send SQL over my Excel tables. So, I connect either to the Jet/ACE engine or to the ODBC driver as per the application needs. In my case, it turned out to be that the connection object was left set in between such calls, so that I ended up trying to use the previously set-up connection object for the Jet/ACE, with a new connection attempt to the ODBC driver. So, the solution was very simple, in this case: Set the connection object to Nothing every time before I initiate a new connection, regardless:

Set ConnDB = Nothing

So that later I end up defining a new connection object every time here:

If (ConnDB Is Nothing) Then
  Set ConnDB = New ADODB.Connection
End If