Connection error with MS Access 97 database

[JPimp]

I have used this connection string many times in the past for a database/VB code, but for some reason I keep getting this error now:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified


This is the connection string

Dim cmdLookupMachines As New ADODB.Command
Dim rsMachinesIdLookup As New ADODB.Recordset


cmdLookupMachines.ActiveConnection = "'Driver={Microsoft Access Driver (*.mdb)};Dbq=\\servname\sharedname\folder1\folder2\folder 3\Stripette Database.mdb;"[\CODE]


Any ideas why it would fail to connect??

Thanks,

Kai-What?

 

[genomon]

Maybe something like

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourPathName.mdb"

will work for you. For Access 97 you will probably need to change the Jet provider to whatever you are using (3.1 or 3.2?).

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[dilettante]

It's a rare day when I try using ODBC to get to any Jet database, but I suspect the problem could be the unquoted value used for DBQ. Either:

cmdLookupMachines.ActiveConnection = _
    "Driver={Microsoft Access Driver (*.mdb)};" _
    "Dbq=""\\servname\sharedname\folder1\folder2\folder 3\Stripette Database.mdb"";"

... or:

cmdLookupMachines.ActiveConnection = _
    "Driver={Microsoft Access Driver (*.mdb)};" _
    "Dbq='\\servname\sharedname\folder1\folder2\folder 3\Stripette Database.mdb';"

... might do the job.

Using the Jet 4.0 OLE DB Provider instead of the ODBC Driver via the implied shim Provider should work too. When in doubt specify the old Engine Type:

"Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;Data Source=C:\YourPathName.mdb"

 

[dilettante]

Oops! Be sure to quote the name there too in case it contains spaces, etc.