PHP and MS Access

Question

How can we connect a PHP script to MS Access (.mdb) file?

I tried by including following PHP code:

$db_path = $_SERVER['DOCUMENT_ROOT'] . '\WebUpdate\\' . $file_name . '.mdb';
$cfg_dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . $db_path;
$odbcconnect = odbc_connect($cfg_dsn, '', '');

But it failed and I received following error message:

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:\web\WebUpdate\index.php on line 41

Answer 1

Here's a sample for a connect and a simple select...

<?php
$db_conn = new COM("ADODB.Connection");
$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./Northwind.mdb").";";
$db_conn->open($connstr);
$rS = $db_conn->execute("SELECT * FROM Employees");
$f1 =  $rS->Fields(0);
$f2 =  $rS->Fields(1);
while (!$rS->EOF)
{
    print $f1->value." ".$f2->value."<br />\n";
    $rS->MoveNext();
}
$rS->Close();
$db_conn->Close();
?>

Answer 2

In the filename, I'm looking at '\WebUpdate\' - it looks like you have one backslash at the beginning at two at the end. Are you maybe missing a backslash at the beginning?

Answer 3

$db_path = $_SERVER['DOCUMENT_ROOT'] . '\WebUpdate\' . $file_name . '.mdb';

replace the backslashes with slashes use . '/WebUpdate/' .

Answer 4

it looks like a problem with the path seperators. ISTR that you have to pass backslashes not forward slashes

The following works for me - with an MDB file in the webroot called db4

    $defdir = str_replace("/", "\\", $_SERVER["DOCUMENT_ROOT"]);
    $dbq    =    $defdir . "\\db4.mdb";
if    (!file_exists($dbq)) { die("Database file $dbq does not exist"); }

    $dsn = "DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=25;DefaultDir=$defdir;DBQ=$dbq";
    $odbc_conn = odbc_connect($dsn,"","")
        or die("Could not connect to Access database $dsn");

Answer 5

Tengo el mismo problema he probado lo que uds dicen y nada, tambien probe con odbc pero la conexion y lectura de bd solo se puede realizar cuando la bd de access esta en la maquina local donde esta el servidor apache, php y mysql, pero cuando la base de datos es movida a otro servidor, la conexion ya no se puede realizar, supongo q en este caso el unico cambio q tendria q hacerse es cambiar la ruta "//10.1.6.55/mibd.mdb", sin embargo ya no funciona he probado cambiando los backslash y nada, el mesanej q me sale es este

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][Controlador ODBC Microsoft Access] El motor de base de datos Microsoft Jet no puede abrir el archivo '(desconocido)'. Está abierto en modo exclusivo por otro usuario o bien necesita permiso para ver sus datos., SQL state S1000 in SQLConnect in C:\xampp\htdocs\asisofp\prueba.php on line 72

Warning: odbc_exec(): supplied argument is not a valid ODBC-Link resource in C:\prueba.php on line 76 Error en odbc_exec

Se ha hecho un reporte de trafico y el firewall no bloquea nada, la base de datos no tiene password, el usuario q tengo es administrador, por ahi lei, que es un bug de php q no puede conectarse a bd de access remotamente si alguien resolvio esto le agradeceria me responda gracias.

Answer 6

Sorry i wrote in spanish i have the same problem i try all your solutions but the same problem. When i have php mysql y apache in a local machine, it can connect to the mdb, but when the bd is in a diferent server the conecction didn`t realize

I obtained a traffic report of the firewall but there aren't problem

I try changing the backslash, my user is administrator, the bd don't have password

if someone found the solution answer please

Answer 7

I'm not certain if this is a violation of best practices or security, but I would like to throw out this suggestion:

set up an ODBC connection and include the database's password in the odbc advance settings. give the odbc conn a DSN name then save.

in your code, just set up the connection like:

try {
  $conn = @odbc_connect("DSNName", "", "", "SQL_CUR_USE_ODBC");
  // un and pw parameters are passed as empty strings since the DSN 
  // has knowledge of the password already.
  // 4th parameter is optional

  $exec = @odbc_exec($conn, $insert) or die ("exec error");
  echo "success!";
}
catch (Exception $e) {
  echo $e->getMessage();
} // end try catch