Solved

PDO with Access

Posted on 2014-10-19
13
992 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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 …

749 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