ODBC issue with Windows[SQLSTATE[IM002] SQLConnect: 0 [Microsoft][ODBC Driver Manager] Data source. 1

[svar]

I get try this on a Windows 7Pro 64-bit and with 64bit office

try{ $link = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:/wamp/www/myproject/test.accdb");

with the odbc driver enabled in php.ini
and get

SQLSTATE[IM002] SQLConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Not sure what "Data source name "means
I checked on the web

here are actually two separate Microsoft ODBC Administrator apps, one for 32-bit and one for 64.

As this KB article explains:

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Nope, not a typo -- the 32-bit version is in the SysWoW64 folder and the 64-bit is in the System32 folder. ()

I was running a 64-bit web server which was checking the 64-bit ODBC library for the DSN, whereas I had it in the 32-bit. Downgrading the server did the trick.

I'm not sure what the issue is here (never used access before) and the only thing I found was to uninstall Office and install an executable. Not sure whether I need to reinstall afterwards.
Also, Administrative Tools->ODBC only knows SQL Server as additional driver; I doubt this is what is needed here.

 

[jpadie]

looks like you may not have the odbc driver for access installed.

i always find it easier (in the few instances i have had to use access in the last fifteen years) to use a system DSN within odbc.

 

[svar]

I'm confused. I thought odbc is a generic database connectivity method and access by default is odbc-compliant (as all MS product, I assume). By "system DSN" I guess you mean ControlPanel->Administrative Tools->ODBC and then System DSN_>Add

what does one add?

 

[jpadie]

MS products may be odbc compliant but they still need drivers.
you can create a dsnless connection directly in the connection string. but i have always found it easier to create a system dsn as said. yes - that can be accessed through the CP.

you need to make sure that the access jet db is in the list of odbc drivers, then create the system dsn, then you can use that in the DSN string for odbc_connect or pdo etc. that way the control panel plugin handles the actual intermediation and you can use the GUI to make sure that the connection is properly established.

 

[svar]

Thanks, but the only thing I am allowed to add to system DSN is SQL server, which is not what I want. I have no idea how to add accdb drivers which btw IS listed in user dsn as access drivers.... Then again this may not be a PHP question proper...

 

[jpadie]

Then you can't do it I believe. If you have not got the odbc drivers for jet (access) installed tgen you won't be able to use odbc via php. If you had them installed they would show in the control panel odbc snap in.

One potential trick is that the jet drivers may be 32 bit and if you are running a 64 bit version of windoze then the 32 bit drivers may not show up in the snap in. Try running the 32 bit version of the snapin instead (it is odbcad32.exe and is in the %windir%\syswow64 directory )

 

[svar]

That's exactly the case: 64bit Windoze with 32 bit office. This is the only windoze machine I could find, since I don't use it and had to borrow it. Why then use Win? Because the person this is for will use an access db on windoze.
The only thing left to try I think is along the lines you indicated:
If one right-clicks on odbc drivers, one gets the directory with all the drivers. So possibly if I get the odbcad32.exe from the 32-bit directory and put it there (and pray hard enough that the exe is standalone without other stuff from the 32-bit directory) it might work. I don't know if I can try dsn-less connection or how to do it.

 

[jpadie]

no no no !

the exe that i pointed you to is the snap-in. it is not a driver.

just browse to that directory and double click on the exe to launch it. then build the DSN with the JET driver if it is installed.

if not, then you will have to install the driver. or migrate from access (which is definitely advisable anyway)

 

[svar]

just browse to that directory and double click on the exe to launch it.

One can do that from DOS also. It brings up the odbc menu with which one cannot do much

then build the DSN with the JET driver if it is installed.

How does one do that? In user DSN there is a line saying
MS Access Database (*mbd,*accdb), but how does one create a system DSN out of that?

 

[jpadie]

don't use a user DSN.
use either a system or a file DSN. I prefer system DSN.

this may help you:

http://webcheatsheet.com/asp/dsn.php

 

[svar]

Regrettably this does not work: System DSN is empty. Clicking on 'Add' only shows a single option, namely an SQL Server. So this does not look good.

 

[jpadie]

if you cannot use the 32 bit snapin and create a system dsn with a jet driver then you have not installed the jet driver.

if you can't do this consider porting the db to a proper relational db like mysql. or if you need a flat file then sqlite might work.

 

[svar]

Since using this very helpful link led to a menu which doe snot let me add anything other than SQL server, I guess the proper wording is that it will not LET me install the jet driver, unless there's a way to bypass and work with DOS directly for example.

Of course I'd never use Win+Access if I didn't have to...

 

[jpadie]

sounds like you need to reinstall access and specify the jet driver installation too.

 

[svar]

Clear enough, thanks!