How to connect PHP with Microsoft Access database

Question

I am currently faced with a new challenge to develop a site using Microsoft Access as the primary database instead of mysql. I have not used MS Access before and I would like guidiance on how to go about it, I have looked up the w3c website on W3schools but the code gives error

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in C:\Users\NNALI\Desktop\root\test.php on line 2

and this error

Warning: odbc_exec() expects parameter 1 to be resource, boolean given in C:\Users\NNALI\Desktop\Breweries\root\test.php on line 4

I am stuck and do not know what to do, I would appreciate all help on this.

<?php
    $conc = odbc_connect("northwind", "","");
    $sql  = "Select * From customers";
    $rs   = odbc_exec($conn, $sql);
?>

Above is the code I used

**Do not confuse w3c with w3schools** - they are completely different things. Also - show the code you currently have. Looks like PHP can't find the database. — naththedeveloper, Nov 06 '13 at 08:24
A simpler solution would be to ignore the "requirements" and export the database as CSV and import it into MySQL. I realize that wasn't the question, but PHP handles CSV files natively (e.g. fgetcsv()) and Access is a really annoying database to work with. Also, Microsoft stopped supporting their Access ODBC driver back in 2010. — CubicleSoft, Jul 04 '15 at 03:39
*"Microsoft stopped supporting their Access ODBC driver back in 2010"* - That is simply not true. The ODBC driver that ships with the newer Access Database Engine (a.k.a. "ACE") - `Microsoft Access Driver (*.mdb, *.accdb)` - is a supported product. — Gord Thompson, Nov 13 '16 at 17:48
I have installed 64 bit Xampp, 64 bit php, 64 bit odbc driver and am trying to use accdb. It doesnt work and the reason why? phpinfo shows ODBC library Win32 So the issue is when using 64 bit code its still loading a 32 bit ODBC driver when we need 64 for accdb.... — Theresa Forster, Dec 03 '19 at 11:48

Gord Thompson · Answer 1 · 2020-04-07T16:12:36.513

15

If you are just getting started with a new project then I would suggest that you use PDO instead of the old odbc_exec() approach. Here is a simple example:

<?php
$bits = 8 * PHP_INT_SIZE;
echo "(Info: This script is running as $bits-bit.)\r\n\r\n";

$connStr = 
        'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
        'Dbq=C:\\Users\\Gord\\Desktop\\foo.accdb;';

$dbh = new PDO($connStr);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = 
        "SELECT AgentName FROM Agents " .
        "WHERE ID < ? AND AgentName <> ?";
$sth = $dbh->prepare($sql);

// query parameter value(s)
$params = array(
        5,
        'Homer'
        );

$sth->execute($params);

while ($row = $sth->fetch()) {
    echo $row['AgentName'] . "\r\n";
}

NOTE: The above approach is sufficient if you do not need to support Unicode characters above U+00FF. If you do need to support such characters then neither PDO_ODBC nor the old odbc_ functions will work; you'll need to use the solution described in this answer.

edited Apr 07 '20 at 16:12

answered Nov 06 '13 at 09:41

Gord Thompson

116,920
32
215
418

I see the problem, I am using USBwebserver, it does not have pdo extension included, so it does not run, i think that is the problem – Nov 06 '13 at 11:48
USBWebserver 8.6 has PDO enabled by default, but only for `mysql, pgsql, sqlite`. Try going into `settings\php.ini` and uncommenting the line `;extension=php_pdo_odbc.dll`, then restart USBWebserver. – Gord Thompson Nov 06 '13 at 12:14
So why not upgrade to 8.6? It's free. (Also, I just tried 8.5 and its PDO_ODBC support is broken. It works fine in 8.6.) – Gord Thompson Nov 06 '13 at 12:47
Sorry please, but the apache of the USBwebserver is refusing to go online, I do not know what the problem is I have been working on that since yesterday, I would appreciate any help you can render thanks – Nov 07 '13 at 09:15
@GeorgeJames Strange, 8.6 worked flawlessly for me: I downloaded it, I unzipped it, I ran it, it worked. Do you get any error messages? Anything show up in the log files or in the Windows event viewer? – Gord Thompson Nov 07 '13 at 13:10

score 5 · Answer 2 · edited Nov 13 '16 at 14:53

5

The problem is a simple typo. You named your variable 'conc' on line 2 but then referenced 'conn' on line 4.

edited Nov 13 '16 at 14:53

Bhargav Rao

50,140
28
121
140

answered Nov 13 '16 at 14:42

JoelG

129
2
4

score 3 · Answer 3 · edited Jul 04 '16 at 08:20

<?php
    $dbName = $_SERVER["DOCUMENT_ROOT"] . "products\products.mdb";
    if (!file_exists($dbName)) {
       die("Could not find database file.");
    }
    $db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");

A successful connection will allow SQL commands to be executed from PHP to read or write the database. If, however, you get the error message “PDOException Could not find driver” then it’s likely that the PDO ODBC driver is not installed. Use the phpinfo() function to check your installation for references to PDO.

If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache, and then try to connect to the database again.

With the driver installed, the output from phpinfo() should include information like this:https://www.diigo.com/item/image/5kc39/hdse

MSR974 · Answer 4 · 2013-11-06T08:52:33.250

Are you sure the odbc connector is well created ? if not check the step "Create an ODBC Connection" again

EDIT: Connection without DSN from php.net

// Microsoft Access

$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdbFilename", $user, $password);

in your case it might be if your filename is northwind and your file extension mdb:

$connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=northwind", "", "");

score 1 · Answer 5 · edited Apr 15 '20 at 10:39

1

If you need to install then refer to:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

edited Apr 15 '20 at 10:39

Mike Doe

16,349
11
65
88

answered Apr 15 '20 at 08:45

user13319093

11
1

score 0 · Answer 6 · edited May 16 '20 at 19:26

0

If you are struggling with the connection in the XAMPP environment I suggest uncommenting the following entry in the php.ini file.

extension = odbc

I received an error without it: Uncaught pdoexception: could not find driver

edited May 16 '20 at 19:26

JBES

1,512
11
18

answered May 16 '20 at 14:30

Hu3

1

How to connect PHP with Microsoft Access database

6 Answers6

Linked