PDO: How to access a Microsoft Access file on Linux?

Question

I was able to read the data of a Microsoft Access file (.accdb) on Windows using PDO, but I had a problem working on Linux (CentOS). I see that the modules are installed:

[ root@rapid host]# php -m | grep PDO PDO PDO_ODBC [ root@rapid host]# php -m | grep odbc odbc 

the code:

 <?php try{ $dbhAccess = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=/root/access/data.accdb;Uid=Admin"); } catch(PDOException $e){ echo $e->getMessage(); exit(); } 

I get erro when executing a PHP file (CLI):

 [ root@rapid host]# php access.php SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified 

Answer 1

You must install the MDB driver.

I have no way to try it now, but I think that MDB Tools (in particular the ODBC driver ) can do what you are interested in.

Answer 2

I have never worked with a Microsoft Access database, but I regularly connect to DB2 servers (on IBM AS / 400 servers) and MS SQL from Linux servers (Ubuntu). Your error probably indicates that you do not have the MS Access driver installed. The only thing I know about is: http://www.easysoft.com/products/data_access/odbc-access-driver/index.html

The error message also says “No data source name found” - in order for me to connect to DB2 or MSSQL, I need to add some information to /etc/odbc.ini and / etc / odbcinst.ini.

/etc/odbcinst.ini - here you describe where to find drivers for ODBC. Here is an example of what I use for DB2 and MSSQL:

 [iseries] Description = iSeries Access for Linux ODBC Driver Driver = /usr/lib/libcwbodbc.so Setup = /usr/lib/libcwbodbcs.so NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's, NOTE2 = the following Driver64/Setup64 keywords will provide that support. Driver64 = /usr/lib/lib64/libcwbodbc.so Setup64 = /usr/lib/lib64/libcwbodbcs.so Threading = 2 DontDLClose = 1 UsageCount = 1 # Define where to find the driver for the Free TDS connections. [freetds] Description = MS SQL database access with Free TDS Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so UsageCount = 1 [ODBC] Trace = no TraceFile = /tmp/odbc.log 

In this last section ([ODBC]) I currently have Trace = no - if you change this to Trace = yes, you will get some useful debugging information in the /tmp/odbc.log file.

/etc/odbc.ini - here you define data sources. Here is an example of what I use for DB2 and MSSQL:

 [primary] Description = primary Driver = iseries System = XXX.XXX.XXX.XXX UserID = XXXXXXXXXX Password = XXXXXXXXXX Naming = 0 DefaultLibraries = QGPL Database = MYLIB ConnectionType = 0 CommitMode = 2 ExtendedDynamic = 0 DefaultPkgLibrary = QGPL DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512 AllowDataCompression = 1 LibraryView = 0 AllowUnsupportedChar = 0 ForceTranslation = 0 Trace = 0 # Define a connection to the MSSQL server. # The Description can be whatever we want it to be. # The Driver value must match what we have defined in /etc/odbcinst.ini # The Database name must be the name of the database this connection will connect to. # The ServerName is the name we defined in /etc/freetds/freetds.conf # The TDS_Version should match what we defined in /etc/freetds/freetds.conf [mssql] Description = MSSQL Server Driver = freetds Database = MyDatabase ServerName = mssql TDS_Version = 8.0 

I saw a few questions here about how StackOverflow talks about using MSFT Access databases from a Linux machine - and it seems like there is never a happy ending. If you can transfer data to another, better supported database system (for example, MySQL) in any way, I think you will get rid of some headaches. Good luck

Answer 3

You cannot use {Microsoft Access Driver (* .mdb, * .accdb)} as part of your data source because Microsoft does not create the ODBC driver for MS Access for Linux. As far as I know, there are 2 ODBC drivers for MS Access. MDB Tools and Easysoft ODBC-Access Driver .

If you install any driver, you can either use the DSN that you install in the odbc.ini file, or as you prefer the DSN-Less connection. Below is an example of connecting Easysoft DSN-Less to MS Access database

PDO ("odbc: Driver = {Driver = Easysoft ODBC-ACCESS}; Dbq = / root / access / data.accdb");

More information on connecting and receiving data using PDO-ODBC can be found in the Easysoft PHP manual . talks about PDO-ODBC.