PHP – Reading an Access .MDB database using a DSN-less connection.

So, you wanted to create a bit of dynamic content on your web site but you were too cheap to pay for that hosting package with the free databases were you? Well you’re in good company then!

Here I’ll show you how to read from an Access .MDB file when running on a Windows host web server using PHP.

Firstly here is a bit of code to get you started:

// Set up the connection
if (!$conn = new COM(“ADODB.Connection”))
exit(“Unable to create an ADODB connection”);
$strConn = “DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=” . realpath(“mydatabase.mdb”);
$conn->open($strConn);

Basically this defines and then opens the connection to the database and is probably the bit of code you’ve been searching for.
Next, define your SQL statement and execute it against the connection you defined earlier.

$strLocationSQL = “SELECT Location_ID, Location_Name, Location_Coords_Top, Location_Coords_Left, Location_Filename, Location_GotoURL FROM tblLocation”;
$rs = $conn->execute($strLocationSQL);

Now you’ve got what VBers would call a “RecordSet”. OK fair enough, so will we. So we want to make sure we’re at the very first record in our RecordSet then we’ll get the data into a friendly named variable, write out our data then move to the next record and repeat.

$rs->MoveFirst();

while (!$rs->EOF){

// Assign the values in each column to a variable with a *friendly* name. ie. one we can identify 6 months later when we are debugging.
$locid = $rs->Fields(0);
$locname = $rs->Fields(1);
$loccoordtop = $rs->Fields(2);
$loccoordleft = $rs->Fields(3);
$locfilename = $rs->Fields(4);
$locurl = $rs->Fields(5);

// Do something with the data we’ve retrieved. In this example just write it out.
echo $locid->value;

// Go to the next record in our RecordSet
$rs->MoveNext();

}

Once you’re done you need to remember to close all record sets and connections.

$rs->Close();
$conn->Close();
$rs = null;
$conn = null;

And that is it! Easy huh?

Comments are closed.