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

Visby

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. – Philana

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. – Anesthetist

"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. – Algerian

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.... – Medawar

Answer 1

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.

Algerian

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 – Visby

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. – Algerian

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.) – Algerian

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 – Visby

@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? – Algerian

Answer 2

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

However

Answer 3

<?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

Guerrilla

Answer 4

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", "", "");

Hexavalent

Answer 5

If you need to install then refer to:

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

Dosimeter

Answer 6

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

Thimblerig