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.

Answer 1

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);

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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);