So recently I started using Microsoft (MS) Access which is the Database Management System (DBMS) in the MS Office Suite. While it presented a very interactive and pleasant graphical user interface (GUI), I started running into problems when I wanted to access the data online. While it was straightforward enough to do so with SharePoint, my team and I didn’t want to go down that road as we wanted the database to integrate well with other parts of web development and not be constrained to Sharepoint. That’s when a colleague of mine, Joanna Sirkoska landed upon ADOdb which allowed us to use PHP to connect to an Access Database.
In this post, I’ll briefly go through how you can connect to your chosen Access database and display the information within it using queries. The script to do so is quite straightforward and you can find it at this link. With this script you’ll either need to modify it to use your database or use the same database that I did which I have provided here.
I’ll run through some of the code here which should hopefully make things clearer if the comments in the script are not clear enough.
First of all you need to make sure you have the following line before trying anything else:
include(‘C:\wamp\www\adodb5\adodb.inc.php’);
To do so, check out the ADOdb link at the start and download the package. Then decide where you want to put it and make sure you change the line to reflect your path choice.
Then we need to connect to the chosen database:
$conn = new COM(“ADODB.Connection”) or die(“Cannot start ADO”);
$connStr = “Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Apache24\htdocs\DataCodes.accdb”;
$conn->open($connStr);
The first line creates an instance of the ADO connection object. You’ll need to edit the second line to match the database you choose to use. Finally the last line opens a connection to this database.
Next we have to execute a query:
$query = “SELECT * FROM tblEquipClass”;
$rs= $conn->execute($query);
I’ve chosen a fairly simple query which which get all the information contained within the table ‘tblEquipClass’. Then I execute the query storing the results within the variable $rs.
An easy way to view the information contained within $rs is printing out the information within it:
while (!$rs->EOF) {
print $rs->fields[2].'<BR>’;
$rs->MoveNext();
}
This while loop allows you to iterate through each line of data from the table. We then print only information from the third column out.
Finally we can close the connections made previously but that is optional.
There you have it! Now you can design your tables using the nice GUI MS Access provides but allow greater connection to your database using PHP. Leave a comment if you found this useful or have any queries.