2

I use odbc driver to load data from an MS Access database file.

PHP code:

$connStr ='odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' . 'Dbq=C:\\inetpub\\wwwroot\\dir\\dir.accdb;';

$dbh = new PDO($connStr);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql="my_sql_query";
$sth = $dbh->prepare($sql);
$params=array();
$sth->execute($params);

In a local computer this works, but when I uploaded to the main server, I get this message when trying to access site:

Error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified' in C:\inetpub\wwwroot\dir\index.php:424 Stack trace: #0 C:\inetpub\wwwroot\dir\index.php(424): PDO->__construct('odbc:Driver={Mi...') #1 {main} thrown in C:\inetpub\wwwroot\dir\index.php on line 424

System:

  • PHP Version: 5.6.3
  • Server: Windows Server 2008 R2 with IIS 7.5

I have installed 64bit ODBC Driver 11 for SQL Server and extensions are enabled in php.ini file. I saw a few tutorials indicating that I have to open ODBC Data Source Administrator, go to System DSN tab, and create a System Data Source. If i have to do this, at Server to connect what should I select, because I am actually using this driver for accessing an Access database file. Also in test environment, I didn't have to do something like this.

Any ideas?

Thank you in advance

EDIT OK. I did the following: 1) I installed MS Visual C++ 2015 because i was missing a dll file for Microsoft Access Database engine 2016(This adds the driver missing in ODBC). 2) Installed MS Access Database engine 2016. 3) After those 2 installations i created a DSN and pointing it to my access file

When i try to run the php file i get this: 

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY009]:                                                                          
Invalid use of null pointer: 0 [Microsoft][ODBC Microsoft Access Driver]Invalid use of null pointer  (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206)' in C:\inetpub\wwwroot\dir\index.php:434
Stack trace:
#0 C:\inetpub\wwwroot\dir\index.php(434): PDO->prepare('')
#1 {main}
  thrown in C:\inetpub\wwwroot\dir\index.php on line 434

Any ideas??

Spiris
  • 69
  • 2
  • 9
  • Your question is not touching SQL Server but MS Access -two different DBMS's though same company. – Parfait Jan 31 '18 at 21:10

1 Answers1

0

You need to specify the correct ODBC driver in the connection string. If your PHP works in 32-bit mode - you have to use 32b drivers.

Second, drivers for SQL Server and drivers for MS Access are two very different things. If you need to work with .MDB - you need to install MS Access.

Creating System DSN in Data Source Administrator can be useful, it greatly simplify the connection string in your program (to the point of "DSN=mydsn" and nothing else). It also allows you configure the DSN (choose a different version of the driver, change location of the file, etc) in a convenient way. It also allows you to connect to the same database from different applications by simply: "DSN=mydsn". The only case when creating DSN could be disadvantageous is if you, by some reason, cannot create the DSN. For example if you do not have enough permissions on the machine. But in all other cases - create the DSN, it will save a lot of time and effort.

White Owl
  • 28
  • 5
  • Thank you very much for your answer! Regarding php mode where do i see if it runs on 32 or 64bit? Also, what i actually need is to connect to a ms access database file not a sql server, do i need ms access installed to my server or a specific driver? Regarding the latest you recommend just creating a DSN? If yes, how do i use it in php code using code i wrote in original post? – Spiris Jan 31 '18 at 19:29
  • [Checking if your code is running on 64-bit PHP](https://stackoverflow.com/q/5423848/1422451) – Parfait Jan 31 '18 at 21:13
  • You can install Access ODBC drivers without installing (or needing a license for) Access. As with everything else ODBC, make sure you match 32/64 bit ODBC drivers with PHP. Also be aware that the inetpub folder has some special permissions issues; you are probably better off putting your database somewhere else. The ODBC drivers do not need the file to be web accessible. – SunKnight0 Jan 31 '18 at 21:37
  • I saw that PHP runs in 32 bits but server is 64bit, so when i try to install the 32bit driver system does not let me due to the incompatibility – Spiris Feb 01 '18 at 06:00
  • I also placed my file in C:\ drive directly in case of permissions issue. I created a System DSN and i just entered a name without messing with other settings. Now the question is how to use this data source in my php code?In my connection string?What is the syntax? – Spiris Feb 01 '18 at 06:10
  • I realised that in ODBC i did not have drivers supporting .accdb files but only .mdb files. I installed Microsoft Access Database Engine 2016, created a DSN and voila! Thank you for your assistance – Spiris Feb 01 '18 at 11:45