• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1436
  • Last Modified:

PDO with Access

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
cescentman
Asked:
cescentman
  • 6
  • 3
  • 3
  • +1
2 Solutions
 
GaryCommented:
Are you sure the driver is loaded?
What does phpinfo(); tell you
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
Should have mentioned I rebooted the box too.
0
 
Dave BaldwinFixer of ProblemsCommented:
You need to edit 'php.ini' to enable "extension=php_pdo_odbc.dll" and then restart Apache or IIS.
PDO drivers
0
 
cescentmanAuthor Commented:
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
 
Lukasz ChmielewskiCommented:
Please take a look at the connection string
http://www.connectionstrings.com/access/
0
 
cescentmanAuthor Commented:
Ok I've looked but I'm at a loss as to what you are suggesting I try?
0
 
Lukasz ChmielewskiCommented:
How about this connection string :
$connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" . $dbName;

Open in new window

0
 
cescentmanAuthor Commented:
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
 
Lukasz ChmielewskiCommented:
OK, how about that one ?
$connStr = 'odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=' . $dbName;
$dbh = new PDO($connStr);

Open in new window

0
 
cescentmanAuthor Commented:
Bingo it works. Thanks
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now