Access ODBC cannot read tables with multiple double quotes in their names

Question

I have an Access 2003 db with a huge number of tables with weird names. I need to read tables' records using a PHP script and ODBC.

Some tables' names have double quotes inside them like this:

Weird """""" Table_Name

ODBC connection works fine, select using an "ordinary" file names works fine. While using odbc_exec($dbh, 'SELECT * FROM [Weird """""" Table_Name]')

it gives:

Warning: odbc_exec(): SQL error: [Microsoft][Driver ODBC Microsoft Access] Parentesi non valide nel nome 'Weird ['., SQL state 37000 in SQLExecDirect in ...(php file).

I understand that the ODBC driver translates ' " ' in ' [ ', how can I escape ' " '?

The tables are ok when used from every dbms tool, they belong to a used Access-based application.

Gord Thompson Gord Thompson · Accepted Answer · 2014-07-10T16:05:54

That error appears to be an idiosyncrasy in the Access ODBC driver. I get the same result when trying to read that table via ODBC from C#.

In any case, PHP and Access ODBC don't get along very well at the best of times. If at all possible I would suggest that you use COM to manipulate the Access database from PHP like this:

<?php
// this code requires the following php.ini directive:
//
// extension=php_com_dotnet.dll

$path = "C:\\Users\\Public\\db1.mdb";
$con = new COM("ADODB.Connection"); 
$con->Open(
        "Provider=Microsoft.Jet.OLEDB.4.0;" .
        "Data Source=$path");

$rst = new COM("ADODB.Recordset");
$sql = 'SELECT * FROM [Weird """""" Table_Name]';
$rst->Open($sql, $con, 3, 3);  // adOpenStatic, adLockOptimistic

while (!$rst->EOF) {
    echo $rst['TextField']->Value . "<br/>";
    $rst->MoveNext;
}

$rst->Close();
$con->Close();

I just tried that and it does indeed work. In my opinion, avoiding the combination of PHP and Access ODBC will likely save you a fair bit of frustration in the long run.

Access ODBC cannot read tables with multiple double quotes in their names

1 Answers