Solved

PDO with Access

Posted on 2014-10-19
13
959 Views
Last Modified: 2014-10-20
I'm trying to open an access database using PDO. I have enabled "extension=php_pdo_odbc.dll" in php.ini. I'm using the code:-

<?php
    $dbName = 'd:\\temp\\meysey\\QrySampl.mdb';
    print 'Path: ' . $dbName;
    if (!file_exists($dbName)) {
        die("Could not find database file.");
        }
        $connStr = 
            'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
            'Dbq=' . $dbName;
        $dbh = new PDO($connStr);
?>

Open in new window


I'm getting in the browser:-

Path: d:\temp\meysey\QrySampl.mdb
Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' in D:\temp\Meysey\test.php:10 Stack trace: #0 D:\temp\Meysey\test.php(10): PDO->__construct('odbc:Driver={Mi...') #1 {main} thrown in D:\temp\Meysey\test.php on line 10

Open in new window


I would welcome help in solving this.
0
Comment
Question by:cescentman
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40390808
Are you sure the driver is loaded?
What does phpinfo(); tell you
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40390816
Do you have a recent version of Microsoft Office on that machine?  The Access driver comes with it.  If not, download the driver from here: http://www.microsoft.com/en-us/download/details.aspx?id=13255
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40390843
Seems I had to download that driver myself.  I did and this works for me (using my own local database).
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>PDO Access</title>
</head>
<body>
<h1>PDO Access</h1>
<?php
// DB configuration
$dbtable = "websitelist";  // the table we're using

		$dbName = "C:\\Inetpub\\wwwroot\\db\\OldWList.mdb";
    print 'Path: ' . $dbName . '<br>';
    if (!file_exists($dbName)) {
        die("Could not find database file.");
        }
        $connStr = 
            'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
            'Dbq=' . $dbName;
        $dbh = new PDO($connStr);

    $sql = "SELECT TOP 13 ent_num, DisplayName, Sortname, WebSite, Descript, Cat, Approved FROM $dbtable";
    foreach ($dbh->query($sql) as $row) {
        print $row['ent_num'] . "\t";
        print $row['DisplayName'] . "\t";
        print $row['Cat'] . "<br>\r\n";
    }
				
?>
</body>
</html>

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Author Comment

by:cescentman
ID: 40391410
Thanks for the speedy responses. As suspected the access driver wasn't shown on phpinfo. I do have Office 2010 on the system but downloaded the driver and installed it as per link above. Nothing changed no error and phpinfo still shows:-

PHPINFO/PDO
0
 
LVL 1

Author Comment

by:cescentman
ID: 40391417
Should have mentioned I rebooted the box too.
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 200 total points
ID: 40391460
You need to edit 'php.ini' to enable "extension=php_pdo_odbc.dll" and then restart Apache or IIS.
PDO drivers
0
 
LVL 1

Author Comment

by:cescentman
ID: 40391807
Bingo I thought; I've been editing the wrong php.ini. I duly located the correct one and enabled "extension=php_pdo_odbc.dll". I re-jigged my code to use yours:-

<?php phpinfo(); ?>
<!DOCTYPE html>
<html>
    <head>
        <title>PDO Access</title>
    </head>
    <body>
        <h1>PDO Access</h1>
        <?php
            // DB configuration
            $dbtable = "Customers";  // the table we're using
            $dbName = 'd:\\temp\\meysey\\QrySampl.mdb';
            print 'Path: ' . $dbName . '<br>';
            if (!file_exists($dbName)) {
                die("Could not find database file.");
                }
                $connStr = 
                    'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
                    'Dbq=' . $dbName;
                $dbh = new PDO($connStr);

            $sql = "SELECT TOP 10 City FROM $dbtable";
            foreach ($dbh->query($sql) as $row) {
                print $row['City'] . "<br>\r\n";
            }
        ?>
    </body>
</html>

Open in new window



Imagine my Chagrin when I get this:-

PDO Access
Path: d:\temp\meysey\QrySampl.mdb

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 
          D:\temp\Meysey\test.php:19 Stack trace: #0 D:\temp\Meysey\test.php(19): 
          PDO->__construct('odbc:Driver={Mi...') #1 {main} thrown in D:\temp\Meysey\test.php on line 19

Open in new window


... PHPINFO now shows ODBC in PDO:-

PDO with ODBC
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 40391948
Please take a look at the connection string
http://www.connectionstrings.com/access/
0
 
LVL 1

Author Comment

by:cescentman
ID: 40391954
Ok I've looked but I'm at a loss as to what you are suggesting I try?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 40391964
How about this connection string :
$connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" . $dbName;

Open in new window

0
 
LVL 1

Author Comment

by:cescentman
ID: 40391986
Nope produces:-

Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' 
          in D:\temp\Meysey\test.php:21 Stack trace: #0 D:\temp\Meysey\test.php(21): 
          PDO->__construct('Provider=Micros...') #1 {main} thrown in D:\temp\Meysey\test.php 
          on line 21

Open in new window

0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 300 total points
ID: 40391993
OK, how about that one ?
$connStr = 'odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=' . $dbName;
$dbh = new PDO($connStr);

Open in new window

0
 
LVL 1

Author Comment

by:cescentman
ID: 40392014
Bingo it works. Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question