ODBC for MS Access Setup

Question

I'm working on getting an ODBC connection in place but would like some assistance in making sure the setup is correct. I'm also hoping I can get some help in writing the PHP code to verify the connection to the DB is working.

On a Windows Server 2008 R2 machine, I browse to C:\Windows\SysWOW64 and run odbcad32. (This is where I start getting confused...most pages I've looked at give pretty basic information for this.) The DB is a MS Access file. Which tab do I choose...User DSN...System DSN...File DSN?

Once I choose the correct tab, am I correct in entering anything for the Data Source Name? (For instance, even though the file is 'it.accdb', can I put name the data source 'Employees'? 'Employees.accdb'? Or does it have to match the actual file name?

Finally, what would the PHP code be to connect to the DB? The DB is currently D:\Temp\IT.accdb. I've got the ODBC currently configured in the System DSN tab with a Data Source Name of 'Employees' and pointing to the DB. I've tried the following code:

<?php
$dbName = "Employees";

if (!file_exists($dbName)) {
   die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb,*.accdb)}; DBQ=$dbName; Uid=; Pwd=;");

I've tried changing $dbName to be a variety of things but everything I've tried results in not finding the database file.

Can someone help me with this?

Answer 1

There are two ways to access an ODBC datasource across a network. You can either mount a network drive containing the database, and give the system user read/write access to it (not something I recommend); Or use an ODBC brigde, like OpenLink.

If you want to move the DB to the same server as the PHP code, then I think that this sitepoint guide should be of assistance.

The latter one is the recommended, and easiest way, to go about it. For this you need to set up the ODBC source as a "System-DSN" on the server containing the database, naming it whatever you like. The "system database" should be checked on "none", and I recommend setting up a username and password. Then install the ODBC request broker pointing to this DSN.
On the client side you set up the ODBC client bridge, configure it to use the DSN on the remote. Then all you need to do, is to create a DSN-string like this in PHP:

$dsn = "odbc:dsn_name";

That's it.

Note that the broker does take a little care to set up, but as long as you read the guides it's pretty straight forward. OpenLink also has a trial version, so that you can figure out whether or not it'll work for you. To figure out which parts you need, they also have a Software selection wizard.

There are also other providers for this kind of software, but I have only experience with OpenLink.