Using an Access Database with PHP

A previous client of mine had an Access database of products they sell, which they had been using offline. But increasingly this working arrangement had proven to be limited. There had been too many instances when they needed to use the database but weren’t near the computer where they kept their database.

Wouldn’t it have been better for them to able to maintain their database where ever they were? Of course, but how? Simple – put it online. In addition, having their product database online opens the door to using it as the basis of a website for their business.

There’s another advantage to having an online database, but which is a significant – multi-user access. That is: storing the database on a server means that any authorized person be able to use the company’s product database using nothing more than a browser. And all as a result of moving an existing Access database file from an offline to an online location.

To make the transition to online databases easier, the existing Access database can be kept unchanged and simply uploaded to a suitable Windows host. (Linux hosting is possible too, but that’s slightly more complicated.) The hosting is not enough in itself, though, because you also need to build an interface allowing them to read and write to the database. Building an interface may sound daunting, but it needn’t be. In effect, what you’ll be doing is replacing the forms listed under the Forms tab in Access.

In this article I’ll focus on the essential elements of PHP you’ll need to use an existing Access database online. One other item that’s standard with a Windows installation is the availability of ODBC drivers. These are essential for the use of Access from PHP.

Front-end and Back-end

First of all, let’s clarify how the client’s Access database would be opened. Normally they’d use Microsoft Access on their PC, perhaps by double-clicking the icon for the database file. With the database online, however, they will be opening the database in a browser.

How can a browser be used to open, view, and edit an Access database? Well, think of the MS Access application as a user interface. As a UI, it has been designed to be used within Windows to read and write an Access database file. Remember, an Access database file is completely separate from the MS Access application software. Thus it is possible to build an alternative UI to read and write to an Access database file. This one just happens to be run using a web browser.

Before getting into the nuts and bolts of the UI, it is important to keep in mind that the data and the user interface are distinct aspects of a system. Let’s take a moment to reinforce a key aspect of using a browser to access a database hosted on a server.

When using MS Access on a PC to use an Access database (that is, offline), the application software is the front-end and the database file (.mdb) is the back-end. Similarly, when going online, the browser is the front-end and the database file is the back-end. In other words, the relationship hasn’t changed: it is still a front-end/back-end relationship. The front-end connects to the back-end, and the back-end stores the data.

The Database

In the example I’ve chosen for this article, we’ll use a very simple database that consists of three tables: a product table, a category table, and a linking product-category table.

The Microsoft Access database contains all the data and table definitions that are relevant to this article. As per best practice, the data is separated into distinct tables. For example, the list of products is stored in the product table, and the list of categories is stored in the category table. The Access database is self-contained, with no links to external tables.

Many products can be of any given category, and any given product can belong to more than one category. For example, if the database purpose is to store details of silver antiques, a pair of 19th-Century silver cuff-links can be categorised as both 19th-Century and Gifts for Men.

This relationship between the data is shown diagrammatically using Access’s own Relationships option.

That is, there is a many-to-many relationship between products and categories. Hence the need for the link table.

Connection to Database

Before reading or writing a database, a connection must be made to it. Windows hosts have a similar folder hierarchy to a Windows PC, so the location of the files on a Windows server will likely have a path of the form C:inetpubvhostsexample.comhttpdocs. You should be able to extract the value of the path to the root folder of your host using the PHP superglobal $_SERVER["DOCUMENT_ROOT"]. The code needed to connect to the Access database then would be similar to:

<?php
$dbName = $_SERVER["DOCUMENT_ROOT"] . "products\products.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");

A successful connection will allow SQL commands to be executed from PHP to read or write the database. If, however, you get the error message “PDOException Could not find driver” then it’s likely that the PDO ODBC driver is not installed. Use the phpinfo() function to check your installation for references to PDO.

If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache, and then try to connect to the database again.

With the driver installed, the output from phpinfo() should include information like this:

PDO_ODBC in phpinfo() output

SQL Commands

The four basic commands used from PHP are the same as those used within MS Access, namely SELECT, UPDATE, INSERT, and DELETE. Thus, the following SQL examples should be easy to follow. Unless, that is, you normally use Access’s Design View, in which you may need an SQL refresher course.

SELECT row(s)

Suppose you need to get the price for a specific product. This is readily achieved using the id of the product to select a single field from the product table.

<?php
$sql  = "SELECT price FROM product";
$sql .= " WHERE id = " . $productId;

$result = $db->query($sql);
$row = $result->fetch();

$productPrice = $row["price"];

After choosing a category from a pull-down list, you can use the category id to query the link table to obtain a list of products that have been assigned to that category.

<?php
$sql  = "SELECT p.name, p.description, p.price";
$sql .= "  FROM product p, product_category pc";
$sql .= " WHERE p.id  = pc.productId";
$sql .= "  AND pc.category_id = " . $categoryId;
$sql .= " ORDER BY name";

$result = $db->query($sql);
while ($row = $result->fetch()) {
    $productName        = $row["name"];
    $productDescription = $row["description"];
    $productPrice       = $row["price"];
}

UPDATE row

Using details provided by an HTML form, you can update a product’s details.

<?php
$sql  = "UPDATE product";
$sql .= "   SET description = " . $db->quote($strDescription) . ",";
$sql .= "       price       =  " . $strPrice . ",";
$sql .= "       sale_status = " . $db->quote($strDescription);
$sql .= " WHERE id = " . $productId;

$db->query($sql);

A troublesome character to be wary of is the apostrophe. The apostrophe can’t be stored in an Access table simply as an apostrophe, rather it has to be escaped by another apostrophe. This ensures that Access knows it is to be stored as an apostrophe and not as the apostrophe that delimits the string. Fortunately, PHP’s PDO library contains a method that prepares strings for storing in a database, quote().

INSERT row

You can add a new product to the product table, using details entered in an HTML form.

<?php
$sql  = "INSERT INTO product";
$sql .= "       (name, description, price, sale_status) ";
$sql .= "VALUES (" . $db->quote($strName) . ", " . $db->quote($strDescription) . ", " . $strPrice . ", " . $db->quote($strStatus) . ")";

$db->query($sql);

DELETE row

If a product has been entered by mistake, or is no longer needed in the database, you can delete it from the product table.

<?php
$sql  = "DELETE";
$sql .= "  FROM product";
$sql .= " WHERE id = " . $productId;

$db->query($sql);

It could be argued that deleting a product is unlikely as it’s better to have a suitable status to indicate a product has been sold or has been archived, etc. Thus, the details would be retained for future reference.

Summary

The above is the bare bones of what is needed to use an Access database from PHP. I hope it shows how little PHP is needed for the essential nitty-gritty of reading and writing an Access database, and how easy it is to understand these basic elements of database interaction using PHP.

Reviewing the above SQL, it’s clear there were only three PDO ODBC commands required to allow PHP to access Access, namely, $db = new PDO(), $db->query(), $db->query(), and $db->quote(). Of course, this is the bare minimum. More complicated databases, and more complicated websites, will require more SQL than shown in this article, but you have now the basics to put your own – or your client’s – Access database online using PHP.

Comments on this article are closed. Have a question about PHP? Why not ask it on our forums?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Bob

    Very helpful article. How would you connect PHP to an SQL Server database?

  • http://webascorpus.org Bill Fletcher

    You intrigued me by suggesting the Access database could reside on a Linux server. Could you point the way to resources on how to do that?

    Thanks,
    Bill

  • Damian
  • Adrian

    This doesnt works to me. When PHP executes: “$db = new PDO(“odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;”);” , it stops executing more lines. The database file exists already. What can I do?

    • Adrian

      OK, all works. Just need to put absolute path (SERVER ROOT) in $dbName.

  • Robert

    Hi David,

    I tried to implement your code with:

    I also uncommented the line extension=php_pdo_odbc.dll in php.ini and restart Apache. And I am doing this on my laptop

    The errors I received were:

    Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified’ in C:wampwwwjunkie-test-areamdb-test.php on line 6

    and

    PDOException: SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified in C:wampwwwjunkie-test-areamdb-test.php on line 6

    What am I doing wrong?

    Thanks for your help.

    Robert

    P.S. I enjoyed your interview on the SitePoint podcast.

    • David

      Robert, have you also checked in the PHP extensions folder on your laptop to ensure the driver file is present? On my laptop, running PHP 5.3.5, it’s in C:Program FilesPHPext

  • Robert

    Hi David,

    Thanks for getting back with me. I am using WAMPSERVER 2.2 (PHP version 5.3.10). I found the ‘php_pdo_odbc.dll’ file in folder C:wampbinphpphp5.3.10ext.

    I re-verified that I uncommented the reference in the php.ini file. I even rebooted the Laptop.

    I compared the PDO, pdo_mysql, and PDO_ODBC in the phpinfo() list with those in the article and I found they are basically the same.:

    – PDO drivers = mysql, odbc, sqlite
    – Client API version = mysqlnd 5.0.8-dev – 20102224 – $Revision: 321634 $
    – ODBC Connection Pooling = Enabled, strict matching

    I can also see the MS Access Database (Microsoft Access Driver (*.mdb, *.accdb) listed the User DSN tab within the ODBC Data Source Administrator Window.

    I am using Dreamweaver 5.5 and as you know when testing an application, DW creates and copies the required folders and files to the work folder I designated in the Severs panel in the Manage Sites panel…hence the folder C:wampwwwjunkie-test-area.

    With that said I noticed that the .mdb file had not been copied over, even though the php code made reference to the file located in a folder located within My Document.

    So a ‘shot in the dark’ … I copied both the php_pdo_odbc.dll and the .mdb file into the C:wampwwwjunkie-test-area folder. The test got the same error results.

    I then tried $dbName = “C:wampwwwjunkie-test-areatest.mdb”. That resulted in the same errors of:

    Fatal error: Uncaught exception ‘PDOException’ with message ‘could not find driver’ in C:wampwwwjunkie-test-areamdb-test.php on line 7

    PDOException: could not find driver in C:wampwwwjunkie-test-areamdb-test.php on line 7.

    I’m not sure where to go from here. What do you recommend?

    Thanks in advance for your help.

    Robert

    • G.R.

      Robert, I wonder if you ever solved this trouble? Mine is very similar to yours.

  • Charles

    I came across this free Access to MySQL converter in 2009. Worked perfectly then. Hope this helps.
    http://bullzip.com/products/a2m/info.php
    Charles

  • http://abc.com kendy dat

    i have problem,i connect file access success but run sql “SELECT * FROM MSysObjects WHERE ((TYPE=1) AND (PARENTID=251658241) AND (NAME not like ‘MSys*’))” not run,when query table in access ok

    • Jiip

      @kendy dat,
      Were you able to resolve your problem querying MSysObjects? I have the same problem. My query works in Access and other queries work in my PHP, but not for this table. Any ideas?

    • David Francis

      Have you checked the error returned by the query? Try using print_r($dbh->errorInfo()); to show an array of info (replace $dbh with your db connection variable). The response I got was “Record(s) cannot be read; no read permission on ‘MSysObjects'”. These tables are hidden by default in Access and do not have the required permissions for reading external to Access. The solution is as follows.

      In Access:

      1. Tools -> Options and select the View tab, and tick the “System objects” checkbox.
      2. Tools -> Security -> User and Group Permissions.
      3. Select Tables as the Object Type, ensure that the correct user is selected, then tick whichever permission your user requires. Then Apply/OK.

      Return to your PHP script and re-run.

      The above worked for me.

  • http://www.mang.web.id Komang Sveta

    I try to make some function like this one.
    =========================================
    function check_log($id, $date, $db)
    {
    $sqc =”SELECT PersonalCalendar.*, PersonalCalendar.PersonalCalendarDate
    FROM PersonalCalendar
    WHERE PersonalCalendarDate=#10/$tanggal/2012# AND FingerPrintID=’$id'”;
    $hsc =$db->query($sqc);
    $roc =$result->fetch();
    return $roc['PersonalCalendarStatus'];
    }
    ========================================================================
    But when i call this function, error message :
    Fatal error: Call to a member function query() on a non-object
    Please help me, solved this problem.

    Thanks

    • David Francis

      The SQL and query() statements look correct. However your function parameter has one called $date, but your SQL statement refers to $tanggal. Could this be a simple typo or programmer oversight?

      • http://www.mang.web.id Komang Sveta

        Thanks Mr.David Francis, this is my mistake posting.
        In my script, parameter and statement same ( $date ). But still error like that.

  • http://stackoverflow.com/users/1611779/jquery-ninja jquery ninja

    Hi I want to insert an image into an access database as an OLE object, is this possible using php?

    • David Francis

      I’ve just tested it successfully using base64_encode to store a GIF image in an OLE field in Access 2000.

      However, I’ve always been told that binary objects are best stored outside of a database, with the relevant database field instead containing a filepath or URL reference to the object. It’s more efficient and less troublesome.

      Do you have to store the object in the database?

  • http://stackoverflow.com/questions/13745682/cordova-2-1-0-use-php-to-attach-and-display-an-image-as-an-attachment-in-an-acce Attach an image to a record in a access database using php

    Hi
    Apparently with access 2007 -2010, images are added as attachments to a database, can this be done programmatically using php?

  • IT

    Hi,
    Can someone help me, to retrieve images from access file with PHP? I have one old access database, and images are saved in image field.

    • David Francis

      I have Access 2000 installed, so I can comment only on how it stores images. It doesn’t have a distinct ‘image’ field type, using instead a general purpose OLE field, but the principle outlined below may still apply to later versions of Access.

      Objects are stored in this field as hexadecimal strings. This makes it easy to display the contents of an OLE field. A crucial aspect of this string is that it’s wrapped in an OLE header and trailer. These need to be removed before the enclosed object (an image, in your case) can be extracted.

      For example, if a GIF89a image is stored in an OLE field, the hexadecimal string will contain the character sequence “474946383961”.

      Use PHP to remove the header up to this GIF identifier, then apply a similar process to remove the trailer. The hexadecimal string can now be converted to binary. PHP 5.4.0 onwards has the hex2bin() function. I use 5.3.5, but there’s a useful bespoke function on the hex2bin() page by ‘Johnson’.

      Convert the string to binary, then save to disc using a suitable filename including the ‘.gif’ extension.

      The image is now useable.

      This technique can be applied to any image type. Note that each image type has its own header, which can be viewed by opening an example image in a text editor or you could check online for the official image specification.

  • http://www.xandertaylor.co.uk Xander Taylor

    Hi David,
    Firstly, I want to say thanks for a fantastic article … I’m still learning a lot everyday! I’m working with a 10 year old application at the moment which still uses MDB, but I’m writing in PHP so this was VERY handy!

    Secondly and extremely important … I want to say a MASSIVE THANKS and WOW!! I never thought to write my SQL queries like you do above!
    01 $sql = “SELECT p.name, p.description, p.price”;
    02 $sql .= ” FROM product p, product_category pc”;
    03 $sql .= ” WHERE p.id = pc.productId”;
    04 $sql .= ” AND pc.category_id = ” . $categoryId;
    05 $sql .= ” ORDER BY name”;
    06 $result = $db->query($sql);

    By laying out the sequential flow of the query, it makes it SO much easier to read and understand! THANK YOU for showing this! :)
    This will make my life so much easier!
    (Can’t believe I never thought to do that!! LOL)

    • David Francis

      You are most welcome, Xander. I shall endeavour to keep any future articles I write to at least this standard. :-)

  • mr.d

    How to add UTF-8 support to pdo (odbc) ?
    Access database

  • http://www.old-record-shop.co.uk Can’t connect to my Access database..

    David, first of all thanks for the really helpful article.
    I’m new to PHP and am having trouble with the code to connect to my Access database which is hosted for me.
    My host has given me a DNS and told me no username or password is required.
    So at the moment my code looks like this:
    “$conn = odbc_connect(‘ORS-database’,”,”);”
    However surely I need more than the simple DNS in the code? For example, the IP of the host?
    I feel I am one small step away… but I couldn’t work out what I need from your article as I think you refer to database names rather than DNS.
    Having real trouble cracking this so any advice would be hugely welcomed!!
    Many thanks again for the article.
    Mark

    • David Francis

      Mark, you are indeed one small step away. Be sure your connection string specifies the correct driver for your database. If you’re not sure about odbc_connect(), check the odbc_connect entry in the PHP.net website where there are examples in the contributed notes.

  • Bryce

    Hi David,

    Thanks for your useful article. I have a trouble with a request because the column name have space like ‘Raison Sociale’ in the mdb file and I can not change that. Request return a null value then there is a correct match for this request. Do you have a trick to use à column name with space please ?

    my request :
    SELECT * FROM Client WHERE Raison Sociale LIKE ‘%vig%';

    Thanks in advance.
    Best regards.