Connect PHP to Microsoft Access ODBC

Question

One of our employees created a Microsoft Access Database and have built a Joomla! module around it. It is currently running on a WAMP server, with an ODBC connection to the adb file. How can i create an ODBC connection on Ubuntu for the Access database? At this point, i'm open to having the DB live locally on my Ubuntu server, or on an SMB share somewhere. Moving it from Access to MySql has already been proposed. It may come down to that, but i'm really being pressured to find another option. Does anyone know if this is possible and how to accomplish it?

Solution 1

You can move from MS Access to MSSQL or MySQL or PostgreSQL, but you'll have to move it somewhere if you want PHP under Linux to be able to read it.

There are cookbook recipes all over the place for accessing Jet databases from PHP, but they're red herrings - they all assume you're running PHP on a Windows machine with MS Access installed, and use the Windows-only ODBC driver for opening Jet ("access") files which is bundled with Access. No good to you on Linux.

Solution 2

Your options for replacing the JET database engine running purely in Linux are slim at best. Remember access files are just flat files, and it is a piece of software provided by Microsoft that actually parses and runs the SQL commands. Microsoft unsurprisingly does not provide a Linux port.

Mysql and PHP are a match made in heaven, so you might consider the switch. If the PHP code is good, the switch will be painless.

Comments

[lordqwerty]

Hello this question maybe better placed in stackoverflow.

[Danimal]

Thank you, i will post it there as well. I wasn't sure, since my question was more specific to Ubuntu, and less specific to the programming aspect.

[Nanne]

Don't cross-post it, ask a mod to move it :)

[Danimal]

Thanks! The problem is that someone created the Access database, cuz that's what they knew how to do. I'm the only one who knows PHP, but it's not a part of my job so i was trying to avoid it. But i may have to be the one to convert it.... Thanks again!

[trognanders]

Access JET SQL is pretty watered down, and anything it can do MySQL can do better. I don't know much about PHP database drivers, but the conversion should be as simple as changing out the driver, and tidying up the SQL statements to run on MySQL.

[Danimal]

Thanks, this looks promising! As far as the FreeTDS is concerned, will Access DB's be treated the same as MSSql DB's?

[trognanders]

FreeTDS is for connecting to a Microsoft SQL Server instance, which is an entire database and server solution running on a Windows machine. It has no capability to read Access data files.