Connect to MS Access remote .mdb file from php on linux

Question

I have been digging internet for couple days, reading very old information, that leads to very old and nonexisting sites, still, I understood, what is needed to achieve my goal.

1. We have a file.mdb on server running WindowsXP, so I need to add it to ODBC data sources. I do that with simple steps, ending up with "System DSN", that allows access to that .mdb file
2. I need to install on this same server some sort of ODBC bridge, that would allow me to create remote connection to this server, making that bridge connect to servers ODBC DSN, and query out my stuff (could not find any free ODBC bridge)
3. On UNIX (FreeBSD) machine, I need to install unixODBC and php5-odbc packages, enabling connections to ODBC (already installed)
4. To connect to remote ODBC and use MS Access db driver, I need to have such a driver for unixODBC, in .so file, that is sitting inside UNIX machine (could not find any free MS Access drivers)
5. Connect to that server using PHP odbc_connect(DSN,user,password), and in DSN I need to give some connection information and driver, which I need to use (MS Access driver).

Correct me, if I'm mistaken and please give me more advice, how to achieve such a connection.

Convery

Why are you set on using a Jet/ACE data store? – Communicable

There is Win software, that is generating those mdb files, very serious and OLD software, that I need to create data integration ability - use data into reports. – Convery

Maybe a software component should be created to run on Windows that converts the MDB to a more portable format, before the data ever leaves the Windows environment. – Communicable

Sadly, not possible with my resources. – Convery

But that software component to run on Windows could be as simple as a VBScript that uses DAO to write data tables out to a bunch of CSV files, for instance. – Communicable

Answer 1

Finally, I found solution.

1. Set up on Win server FreeSSHd, configure connection account and set directory to one, you need
2. Set up on unix server sshfs

3. Mount Win server directory with .mdb files

   sshfs {user}@:/ {unix mount point} -o workaround=rename,allow_other

4. Set up on unix server mdbtools

So, I used default PHP code from docs and write this PHP script:

$rows = $cols = array();
if (($handle = popen('/usr/bin/mdb-export {unix mount point}/{file}.mdb {table} 2>&1', 'r')) !== FALSE) {
    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        $num = count($data);
        if ($row == 1) { for ($c=0; $c < $num; $c++) { $cols[] = $data[$c]; } }
        else { for ($c=0; $c < $num; $c++) { $rows[$row][$cols[$c]] = $data[$c]; } }
        $row++;
    }
    pclose($handle);
}
print_r($rows);

• Path to /usr/bin/mdb-export should be path to your mdb-export file (use find / -name "mdb-export", if you can't find yours).
• Mount point {unix mount point} should be an empty file folder (I used /usr/home/remotemdb)
• Table {table} should be the table name inside mdb file. Query all possible tables inside mdb file with command mdb-tables {unix mount point}/<file>.mdb

There is no need for drivers, configuration or other stuff, just plain mdbtools and access to file, in this case, achieved with remote connection through ssh. In you want, you can install fuse package, to autmatically mount remote directory, but that is another question.

Hope someone this helps.

Convery

Answer 2

You don't connect to a "server dsn". DSN's are a local thing only. They're not exposed for remote connections at all. If you want a machine to connect to a database, you need to have a DSN configured on that machine - you won't be able to use a DSN specified elsewhere.

For PHP ODBC, that'd be

$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=/network/path/to/your/access/database.mdb", $user, $password);

Forint

The question was, what software (prefered freeware) exacty do I need, to enable connections to those local DSN's? – Convery

Answer 3

You are correct insomuch that you require an ODBC to ODBC Bridge.

At OpenLInk we refer to a Multi-tier ODBC to ODBC Bridge...

This is Multi-tier in the sense that it has a client/server architecture as follows --

Linux Client -- ODBC Application OpenLink Generic ODBC Driver

Windows Server -- 32bit OpenLink request Broker 32bit OpenLink ODBC Agent 32bit Microsoft Access ODBC Driver (with pre configured DSN) Microsoft Access Database file.

Erasmoerasmus

Thats commercial too. I would like some freeware bridge, as I'm not dealing with commercial stuff. And BTW, that software is outdated! Theres only old FreeBSD 5.4 avilable, currently using 8th version. Cmon, and you want me to pay for outdated product? :( But still, thanks for info... – Convery

Answer 4

Its commercial, so possibly not of interest, but Easysoft have an ODBC driver for Access that's available on Most *nix's. No bridge required. There isn't a build on FreeBSD at the moment, but I could get one built for you on Monday if it's of any interest.

There is the open source MDB tools that may have enough for what you want, but it is lacking in quite a lot of functionality.

Easysoft Access ODBC Driver

MDB tools

Confinement

That is quite the same, as I mentioned in my answer above... But thanks for offer! – Convery

Answer 5

Use PDO with MDBTools:

install:

apt-get install libodbc1

apt-get install libmdbodbc1

apt-get install php5-odbc

(restart apache)

Sample:

$query = 'SELECT * FROM Table';
$mdb_file = 'file.mdb';
$driver = 'MDBTools';
$dataSourceName = "odbc:Driver=$driver;DBQ=$mdb_file;Uid=user;Pwd=pass;";
$connection = new \PDO($dataSourceName);
$result = $connection->query($query)->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Lumpfish