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

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.