Solved

PDO with Access

Posted on 2014-10-19
13
1,039 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
embadded search engine in website 4 46
Help installing Laravel app on MAMP on MAC 7 56
PHP processing webform 25 45
Format Date 7 28
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

738 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