PHP ODBC Connection to Local Networked MS Access(.mdb) File

Ross Turner
Ross Turner used Ask the Experts™
on
Hi EE,
I’ve been having a devil of a job trying to connect my local intranet site to my access db (.mdb).
Just to keep it clear here is the set up.

Server1 (WebServer): IP:10.0.0.105
Wamp 2 installed with service log-on rights assigned.

Server2 (DataServer):IP:10.0.0.106
Access Database (.mdb), shared location and have checked sharing rights.
Path:\\10.0.0.106\data\servicedb-data.mdb

Ok, i wasn’t sure how the connection should be so i checked
PHP odbc_connect and found the DSN-less connections Example below.

Brilliant or so I thought.....


// Microsoft Access
$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password); 

Open in new window


So using the code above i created the php code below (.mdb has Default Standard Security which I checked here connection string details  
)
<?php
//Path to AccessDB File
$mdbFilename ="\\10.0.0.106\data\servicedb-data.mdb";
//Username for AccessDB 
$user = "Admin";
//Password for AccessDB 
$password = "";
// Microsoft Access
$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password); 

?>

Open in new window


However when i run the above script it alway come up with the error

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides., SQL state S1009 in SQLConnect in C:\wamp\www\ServiceDB\mdb_conn.php on line 9

Am i missing something..... because i'm starting to go a little bonkers with the whole thing.

I've got nearly exactly the same code for sql connection and it works perfect

Thank you for your help in advance.

Ross
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Try changing this:
$mdbFilename ="\\10.0.0.106\data\servicedb-data.mdb";
to:
$mdbFilename ="\\\\10.0.0.106\\data\\servicedb-data.mdb";

Commented:
Just a friendly piece of advice, Access is terrible when it comes to handling multiple users. Almost 10 years ago, I helped develop an Access-based application with a similar approach, and I ended up having to rewrite a ton of code after we were forced to move to SQL (not to mention the migration scripts) because we started getting more users than originally anticipated and Access was having performance trouble with that many users and hitting different limitations.

It seems like every time I've heard about an Access-based application, people are lamenting the choice because it seemed like a good choice at the beginning but it just doesn't scale well. So if you need something for a personal database, then go for it (although in that scenario, I'd recommend the superior-and-free SQLite solution), but the fact that you have a separate dataserver implies you might be preparing for some larger loads. If you're not already trying to move to a SQL based solution (MySQL, MSSQL, etc..), it might be worth starting the plans.
Ross TurnerManagement Information Support Analyst

Author

Commented:
Hi gr8gonzo,

That solution that you provided was spot I can't believe that it was as simple as \\ :)

In reference to your last post I know exactly where you are coming from, I've just finished rewriting the whole system for sql, however I wanted the new intranet site to feed into the old one for the moment till I make the switch-over, just taking the first steps with my users they don't like change at all......

Thank you for all your help and advice

Ross

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial