database connection in php with MS Access on linux hosting

Question

Hi

I am running on php.
And in my local I am working on windows environment so it was easy to connect to MS Access Database using ODBC connector.

But on my live server environment there is Linux so there is a problem regarding the DB connection.

So what are the steps to connect to MS Access DB in Linux environment using php.

Thanks
Avinash

Answer 1

I have not worked with access in Linux but a quick search gave me this.

Microsoft Access Database using Linux and PHP

Answer 2

Personally I'd recommend porting to a DBMS that works on both Windows and Linux. But if you have to stick with access, you might check out:

http://www.unixodbc.org/ or http://www.openlinksw.com/

Answer 3

If you are on shared hosting the Linux server will most probably lack ODBC support. You will be better off using SQLite, since that will work on Linux and Windows (and OS X) without any major problems using PHP...

Answer 4

This isn't directly answering your question either, but it is another way to solve the problem of wanting to use Access to update web data:

You can port your Access database to MySQL on the live (web) server, and use Access as the frontend to that database for your user (basically flipping the roles of the website and Access):

You can connect to the MySQL database from Access over ODBC using the MySQL Connector driver, linking the tables into Access with the "External Data" tools. That way, you get a (more) serious database engine, optimized for the web, but retain your user's ability to manipulate the data with tools familiar to them.

Answer 5

The relationship between the web server and the client's network is not clear. If the Linux Server is on the client's local LAN and is running Samba, you could certainly store the Access/Jet/ACE database on the Linux server and the users could edit it in Access.

You'd then need one of the Jet emulators running on the Linux server (so far as I know, there is no emulation of the ACE available for Linux, so if their database is ACCDB format, you're out of luck, unless I'm just out of date in what I've heard about the emulators). Some of the Jet emulators are read-only, so be careful with that.

Now, I wouldn't recommend this scenario, since Jet/ACE is not designed for this kind of application. It works fine in a small office/workgroup environment with SMB networking, but I wouldn't use it as the back end of any website that wasn't read-only, proof of concept, or very, very low read/write volume. I certainly would never recommend having the same MDB serving as the website back end and being used simultaneously by interactive Access users.

If this scenario is correct, your users can still work with Access if you port the back end to a server database that runs on Linux. I use MySQL all the time, and since I'm an Access idiot, I depend on phpMyAdmin for my user interface to manage my MySQL databases. It should be fairly easy for an Access user to understand in order to muck about with the schema. Using MyODBC you can set up linked tables in Access and the reports and forms and such will work pretty much the same as they did with a Jet back end.

But I strongly doubt that your client is hosting their website on a Linux server connected to their office LAN. More likely is that the Linux server is shared web hosting, in which case, there will be no SMB networking available, so you won't be able to have a single Access database edited from both the website and by the interactive users. THIS IS A GOOD THING, as doing that would be inadvisable in the first place.

With shared hosting you may be allowed to open up a port to a server database (my website's hosting server provides both MySQL and PostgreSQL and but allows remote access only to MySQL and only via host name/IP address. That's likely not to work very well with an office LAN. Any web host that allows open unrestricted access to a database server from the wild and woolly Internet is probably not one that your client should be using. One option would be if they provide VPN support, in which case a VPN could be used. But in my experience, web hosts charge an arm and a leg for this (unreasonably so, in fact), so it's usually not a good option.

So, it's not likely that you'll be able to provide real-time connections to a server database running on the website. In that case, you're left with synchronizing the databases in some way. If the website is a complete slave of the Access database (i.e., no editing, additions or deletions on the website) then it can be very simple, as you could just write scripts to export the data to a file or files that you upload to the website and then process on the website to replace the existing data.

If you have a multi-master scenario (updates in both locations), it's a lot more complicated, especially if updates have to go both ways. I've programmed the Access end of exactly this scenario, i.e., synchronizing a MySQL database on a website with an Access database, and it's not a trivial task.

If you can get MySQL on both ends, it might be somewhat easier (assuming you could set up replication, which in a low-end web hosting scenario seems quite unlikely to me), but I wouldn't count on it.

My assessment from what little I know about the situation is that your client has to give the whole thing a rethink, as there's realistically no integration possible without a lot of workarounds.

One thing to consider if the website is not public but for supporting remote users is that Access 2010 is going to add some amazing support for integrating with Sharepoint Server 2010 that will allow publishing an Access database to a Sharepoint Server and running it in the web browser. On the other hand, if the circumstances are limited to remote users, the Access app could be run on a Terminal Server and save everybody a lot of trouble.