Wednesday, June 13, 2012

PHP and Microsoft Access Database Connection

We all know that MySQL database connection is the best way for PHP. This make us so strong during coding. But what I have to say is that is not the only way. We have some opinions about connecting to databases. These may be between PHP&Access, PHP&Sqlite and PHP&PostgreSQL etc. But here, we interested in the first one, PHP&Access.

Access is one of the most popular databases in the world. It was made by Microsoft Corporation. You can find here more information about Access. A person who uses the Access can create, update, delete, etc tables on databases without using SQL.  That's why we can see easily how important  the interface is. In this respect this is so simple and useful.


An Example on Access : Getting data from access


<?php
$conn = new COM("ADODB.Connection") or die("ADODB Oops!");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Users\UserName\Desktop\ MyDatabaseFile .mdb");
$data = $conn->Execute("SELECT * FROM myTable ORDER BY users ASC");

print "<TABLE border='1'><TR><TD colspan='6'>DATA</TD><TR>";
while (!$data->EOF)
{
print "<tr>";
print "<td>" . $ data ->Fields[0]->value . " </td>";
print "<td>" . $ data ->Fields[1]->value . " </td>";
print "</tr>";
$ data ->MoveNext();
}
echo "</TABLE>";


Just save code above as access.php and run it. It's going to be like the screen belown.
Screen View
If you try to figure that out, codes belown will be helpful for you.

$conn = new COM("ADODB.Connection") or die("ADODB Opps!");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Users\UserName\Desktop\MyDatabaseFile.mdb");

We try to connect access database with php here.

odbc_connect(‘dsn name’, ‘user name’, ‘password’);

There is a problem with this! When you look at the code belown, can realize user_name and password field. What are these ones? How can we build on these? That's the point on this article actually. Just go to the localhost and this page:

phpinfo.php


<?php
Phpinfo();
?>



When run phpinfo.php file, need look into ODBC properties

ODBC with phpinfo.php
You must implement your user name and password for working on it. By the way, it is easier to make this operations with codes. That's why i show you as code, not screen views.

Well, let's code then :)

$conn = new COM("ADODB.Connection") or die("ADODB Opps!");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Users\UserName\Desktop\MyDatabaseFile.mdb");

$data = $conn->Execute("SELECT * FROM myTable ORDER BY users ASC");

print "<TABLE border='1'><TR><TD colspan='6'>DATA</TD><TR>";
while (!$data->EOF)
{
print "<tr>";
print "<td>" . $ data ->Fields[0]->value . " </td>";
print "<td>" . $ data ->Fields[1]->value . " </td>";
print "</tr>";
$ data ->MoveNext();
}
echo "</TABLE>";

This is my result page with php, html and sql.

See you guys next article!

17 comments:

  1. I get this error Fatal error: Class 'com' not found in /var/www/index.php on line 6

    why?

    ReplyDelete
  2. I get this error Fatal error: Class 'com' not found in /var/www/index.php on line 6

    ReplyDelete
    Replies
    1. Go to php.ini (click on the wamp icon in the tray, place your mouse on php then opne phpp.ini file), scroll down to where it says "Dynamic Extensions" then as the last entry enter 'extension=php_com_dotnet.dll' without the quotes and do not put a semi colon before it.
      Save the file and resatrt Wampserver.

      Delete
    2. Please check this link
      http://stackoverflow.com/a/12050332/2152350

      Delete
  3. Hi guys,Linux not support COM object

    ReplyDelete
  4. Hi,
    Am unable to connect to an Access 2007 .accdb file from Windows - error msg

    Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
    Description: [Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.' in C:\xampp\htdocs\accsc1.php:3 Stack trace: #0 C:\xampp\htdocs\accsc1.php(3): com->Open('DRIVER={Microso...') #1 {main} thrown in C:\xampp\htdocs\accsc1.php on line 3

    Please help.

    ReplyDelete
    Replies
    1. Convert your db to mdb format

      Delete
    2. A different driver (ACE) is needed for access 2007 and later:
      After editing php.ini file as suggested by Nanjaya, modify the connection string as follows:

      $conn = new COM("ADODB.Connection") or die("ADODB Opps!");

      $conn->Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\UserName\Desktop\MyDatabaseFile.mdb");

      Delete
  5. I get ? mark instead of UTF-8 symbol :(

    ReplyDelete
    Replies
    1. can you check your data out? or share with me?

      Delete
  6. execute($query19);

    if($rs19){ /// problem is here . i am trying to validate the query but it always goes inside the if statement even $bno is not present in the table ///
    $itm=explode(',',$rs19['item']);
    $qty=explode(',',$rs19['qty']);
    $r=count($itm);


    for($i=0;$i<=$r;$i++) {
    if(isset($itm[$i]) && isset($qty[$i])) {
    echo "item".$itm[$i];
    echo "qty".$qty[$i];
    echo "
    ";
    }
    }

    }
    else {
    echo "no records found";
    }
    }

    else {
    echo "enter bill no";
    }
    ?>

    ReplyDelete
    Replies
    1. I want to be sure that you connected Microsoft Access database as first?

      Delete
    2. ya i connected with ms access2007
      the code is running smoothly if bno is in table but if bno is not in table
      if(isset($_GET['b_no']))
      { $bno=$_GET['b_no'];

      include("connect.php");
      $query19="select * from billing where `b_no`=$bno;";

      $rs19=$conn->execute($query19);


      rest of the code

      Delete
  7. Gone are the days when you make all records in the notebooks but when i had Microsoft Access Training Now i am able to make records on Ms Access.

    ReplyDelete
  8. Hi, I use this example, works fine when use SELECT. but any time I use UPDATE or DELETE gave me error. Please give working example for add/edit/delete ..so far I tried like "UPDATE tableName SET userName='foo' WHERE userId=1" or "DELETE FROM tableName WHERE userId=1"

    ReplyDelete
  9. Save mdb file in Access 2000 format (*.mdb) for best compatibility.

    ReplyDelete

Thanks