Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PDO with Access

Posted on 2014-10-19
13
Medium Priority
?
1,254 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 84

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 84

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
Industry Leaders: 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 800 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 1200 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
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 …
Suggested Courses

926 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