Microsoft Access Database using Linux and PHP
Recently I had a project in which I needed to allow Microsoft Access MDB files to be uploaded via a web screen and then parse the data in those files and import selected records into another database (MySql). As I began looking into this problem I began to realize that not many people needed to do this on Linux and that far more people were just convinced this was an impossible task. So I decided to try to make it work, hence this post.
You will need the following:
Linux ( I’m running RedHat 9.0)
PHP
Apache
UnixODBC
MDBTools
INSTRUCTIONS
1) Download the UnixODBC RPM, found here. I installed unixODBC version 2.2.5.1.
rpm -ivh unixODBC-2-2.5-1.i386.rpm
2) Download the MDBTools rpm, found here. I installed mdbtools version 0.5.1. Read limitations!
rpm -ivh mdvtools-0.5-1.i386.rpm
3) Download the MDBTools ODBC driver rpm. Again I installed version 0.5-1.i386.rpm. Read limitations!
rpm -ivh mdbtools-odbc-0.5-1.i386.rpm
4) Add the mdbtools driver to your unixODBC config.
Create a new text file. NON-LINUX user: Beware do not do this on windows as you might get werid new lines, use vi.
[MDBToolsODBC]
Description = MDB Tools ODBC drivers
Driver = /usr/lib/libmdbodbc.so.0
Setup =
FileUsage = 1
CPTimeout =
CPReuse =
NOTE: The driver may be in /usr/local/lib/libmdbodbc.so.0. This happens if you build from source and use the make install command. The RPM puts it in /usr/lib.
Now install the driver using the file you created. This is called a template file.
odbcinst -i -d -f template.file
5) Define the datasource name. This is done in the /etc/odbc.ini file. So pull up this file in vi or another text editor and add the following lines.
[Dogs]
Description = Microsoft Access Database of Dogs
Driver = MDBToolsODBC
Database = /var/data/my_dog_db.mdb
Servername = localhost
UserName =
Password =
port = 5432
That’s it you should now have an odbc connection available. I will demonstrate using php, this assumes that your php is compiled with UnixODBC support, the version that ships with Redhat 9 does if yours does not then you can learn how here.
So I will write a quick php script to query my dogs database and print out the names and weights of my dogs.
$myDB = odbc_connect(”Dogs”,”",”");
$query = “select name, weight from dog_list”;
$result = odbc_exec($myDB, $query);
while (odbc_fetch_row($result)) {
print “Name = ” . odbc_result($result,”name”);
print “
Weight = ” . odbc_result($result,”weight”);
}
If you get a php error that says odbc_connect is not a function then see if you have php-odbc installed. Do rpm -qa php-odbc. If you see php-odbc returned then you have it if not install it., the rpm is available on the redhat discs.
Limitations:
- As of the time of writing this entry MDBTools did not support write access to the mdb files. This was fine for my purposes as I was reading data in and sticking it into a mysql database.
- There is a bug in MDBTools v0.5 which does not allow you to query tables or columns with an underscore. This was a bug I hit early on, but it has been fixed in new version 0.6 but that has not been released as of the time of writing this article. So I recompiled the 0.5 source code with the fix from the 0.6 CVS repository. I have bundled it into a 0.5 release and have the two rpms mentioned above here:
mdbtools-0.5-1.i386.rpm
mdbtools-odbc-0.5-1.i386.rpm
I would check the official download site before using my hacked version as I’m sure this bug will be fixed in 0.6 (plus rumor has write access will be present as well).
References:
Using UnixODBC from command line
Installing MDBTools
November 18th, 2003 at 10:46 am
Will check if i get this also working on my system. hope it will.
first i could say, that it is a great tutorial
November 28th, 2003 at 9:18 pm
My configuration is working but when i use the function odbc_tables have problems. Do you know anything about this problem?.
December 2nd, 2003 at 4:24 pm
I do not know anything about odbc_tables. I will note that the MDBTools driver has “limited” functionality therefore it might be that this is just one of the drivers limits.
December 9th, 2003 at 4:55 pm
Do you know about of problems with field’s name that use “_” character?.
December 10th, 2003 at 6:53 pm
Thanks Byran, this is brilliant. I’ve been trying to get something like this put together for quite a while now. I can’t wait to give it a go.
December 14th, 2003 at 8:54 am
The underscore problem is fixed in the mdbtools 0.6 code. As I noted in the original post, I recompiled the mdbtools 0.5 with that fix. I also posted an rpm of “my” 0.5 version. Happy hacking.
December 16th, 2003 at 9:57 am
Hi Bryan,
I looked around a lot trying to query an mdb file from java code. I downloaded mdbtoos and mdbodbc rmps from tyou site and configured odbc.ini and odbcinst as in your post but the system doesn’t work at all. Java can’t connect to the odbc source and if - for example - I try to connect to the odbc source via openoffice I can’t see the tables and can’t make any query. Do you have anything to suggest???? If it works with php as in you post i should work with java as well.
mdb-sql works fine and it is possible making query.
isql (to test odbc connection) works partially. some query work while others return a segmentation fault or unknown table in db.
Can you help me ?
December 17th, 2003 at 3:36 am
Hi Bryan,
I tried answering your email but i got a delay delivery error from my isp’s smtp.
Was your email account right??
Thanks
Bye
December 31st, 2003 at 2:42 am
hi bryan, I have a problem and your help would be very apreciated. I have this proyect in wich i have to update a database in a linux server(RaQ4) with an MSaccess database in an NT cumputer, and the information in the database has to be visible using php for the client. the linux server comes preloaded with Interbase. it not necesary to use an interbase database, but i want to know if you could give me a hand with this.
thank you very much
bye
January 11th, 2004 at 8:47 am
Can i connect a Microsoft access database directly with php web pages?I want to build a website using php and i just wanted to know whether it is possible 2 store data in Microsoft Access database because whenever i search for php tutorials, i find only mySQL databases.
January 11th, 2004 at 8:49 am
Please help me out bryan. thx beforehand.:) I’ll also be glad 2 have the codes 4 odbc connection
January 16th, 2004 at 5:01 am
There is a commercial alternative too - the Easysoft ODBC-ODBC bridge ( http://www.easysoft.com/products/2002/main.phtml ) which supports inserts/updates etc. This solution requires a windows box (to host the MDB).
January 19th, 2004 at 12:38 pm
I seem to be having trouble with where clause statements, is there a syntax guide I should be able to find somewhere??
January 20th, 2004 at 12:28 pm
Hi everybody!
I just want to know how to connect a MS acces database with a php script , i just want to send some data from a form to the the database(Ms ACCESS) I see a lot of ways to do for MY sql can anybody help me out and we areusing DSN less connection so please reply soon.
thanks a lot
January 29th, 2004 at 10:01 am
hey there. im an asp developer turning php. can someone gimme a quick and easy php connection string to an MDB?
February 2nd, 2004 at 6:12 pm
I’m getting this error … Warning: odbc_connect(): SQL error: [unixODBC]Could not find Database parameter, SQL state 08001
Oddly enough, it works fine if I run …
php filename.php
from the command line. It’s only when run as the webserver I get the error.
February 4th, 2004 at 10:04 am
Found the problem.
It appears it doesn’t like the odbc.ini file to be anywhere but /etc/odbc.ini (mine was /etc/unixODBC/odbc.ini). If yours isn’t there just make a symlink to it and it works.
ln -s /etc/unixODBC/odbc.ini /etc/odbc.ini
February 4th, 2004 at 12:28 pm
Your tutorial is very helpful. jdbc setup instructions will help a lot of people. Thanks
- Kishore
February 15th, 2004 at 9:47 pm
Thanks for this usefull Info!! Great help
February 20th, 2004 at 10:53 am
Great job. But I’ve a problem using it.
This is the error that I obtain.
August 5th, 2004 at 3:52 am
Have newest comments been lost?
September 16th, 2004 at 10:13 pm
Thanks!!, just what I was looking for and it worked!