Could Not Find installable ISAM

[kirkm]

Hi I'm getting this error on both remmed out lines

Dim cv As ADODB.Connection
Dim rr As ADODB.Recordset
Dim theName As String, sql As String
Set cv = New ADODB.Connection
Set rr = New ADODB.Recordset

theName = "\\Mach2\e\ListBE.mdb" 'Networked drive e on Mach2
sql = "Select * From Tracks Where Tcat= 1009"
'cv.Open "Provider=Microsoft.ACE.OLEDB.12.0; DataSource =" & theName & ";"
'cv.Open "Provider=Microsoft.Jet.OLEDB.4.0; DataSource =" & theName & ";"
With rr
    .Open sql, cv, adOpenKeyset, adLockReadOnly  'Guessing a bit with these values. Tosort when I get this far.
Stop
End With

Googling for it shows some complex stuff, and one problem is a missing DLL.
Can anyone assist in simpler terms? I'm also a bit a sea using Access 2007 whereas normally it's Access 2003.
Is is likely to be a missing reference. I'm copying something from Access 2003 where the code is working OK. Thanks for any help.

 

[Ranman256]

Instead of code, did you try linking the table via the menu,External data?

 

[kirkm]

>Instead of code, did you try linking the table via the menu,External data?

That's a good idea, thanks, and it seems to have worked. I can view the linked table OK.

But I'll still like to fix this ISAM thing just to know what caused it. Is anyone able to answer that please ?

 

[MarkK]

Try adding a space in "DataSource"...

cv.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & theName & ";"
[COLOR="Green"]'not[/COLOR]
cv.Open "Provider=Microsoft.ACE.OLEDB.12.0; DataSource =" & theName & ";"

For connection strings you can always check www.connectionstrings.com for reference.

 

[kirkm]

Thank you MarkK. I'd have spent all day looking for that !
But I have stuck another glitch I'd like to sort out. If I use criteria =, all is ok but using 'Like' gives zero results. My sql is ok (works in query design mode) so I suspect it's something with

.Open sql, cv, adOpenKeyset, adLockReadOnly
Also tried adOpenForwardOnly butthat also gave zero results.

Can you see my mistake ? Thanks.

 

[MarkK]

What is the exact SQL you are using?

Keep in mind that when you execute SQL in the Access query designer you are executing it as a DAO.QueryDef. The code you posted, by contrast, executes the SQL under an ADO.Connection to open an ADO.Recordset, which is a completely different processing environment. Success with the DAO.QueryDef does not guarantee success under ADO.

 

[kirkm]

I remembered the problem... I was using * instead of %.

All Ok now. Thanks for helping.