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?
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.
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.
Read this link:
Accessing Microsoft SQL Server from PHP on Ubuntu using PDO, ODBC and FreeTDS
follow these instructions to access SQL Server databases from PHP on your Ubuntu server:
Install the packages freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbc and unixodbc. This provides the libraries you need.
Copy the contents of /usr/share/doc/freetds-common/examples/odbcinst.ini into /etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC layer.
That's it! You should now be up and running. Try testing the database connection with something like this: