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

14,281

Solution 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.

Solution 2

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.

Solution 3

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);
Share:
14,281
Deele
Author by

Deele

I'm a nice guy :) I like so many different specialities, that I don't know which one leave in background, as time is short for all of them. Speaking about computer world interests, I'm interested in web development, user interface, PC gaming, Artifficial Intelligence, robotics and bio-robotics. I'm bachelor grade computer systems analyst and working now as a web programmer, and when time allows, I'm developing some web stuff. As I have been playing PC games for about 15 years already, I'm interested in changing my main speciality and get into game development industry, maybe, as an QA tester. That would allow me to express my ideas and my point of view in this field.

Updated on June 05, 2022

Comments

  • Deele
    Deele over 1 year

    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.

  • Deele
    Deele over 12 years
    The question was, what software (prefered freeware) exacty do I need, to enable connections to those local DSN's?
  • Deele
    Deele over 12 years
    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...
  • Deele
    Deele over 12 years
    That is quite the same, as I mentioned in my answer above... But thanks for offer!