title: SQL statement against Access 2010 DB not working with ODBC
body: <p>I'm attempting to run a simple statement against an Access DB to find records.</p>
  
  <p>Data validation in the records was horrible, and I cannot sanitize it.  Meaning, it must be preserved as is.</p>
  
  <p>I need to be able to search against a string with white space and hyphen characters removed.  The following statement will work in Access 2010 direct:</p>
  
  <pre><code>select * from dummy where Replace(Replace([data1],' ',''),'-','') = 'ABCD1234';
  </code></pre>
  
  <p>Running it from an ODBC connection via PHP will not.  It produces the following error:</p>
  
  <pre><code>SQL error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression., SQL state 37000 in SQLExecDirect
  </code></pre>
  
  <p>Creating a query in the database that runs the function and attempting to search its values indirectly causes the same error:</p>
  
  <pre><code>select * from dummy_indirect where Expr1 = 'ABCD1234';
  </code></pre>
  
  <p>I've attempted to use both ODBC drivers present.  ODBCJR32.dll (03/22/2010) and ACEODBC.dll (02/18/2007).  To my knowledge these should be current as it was installed with the full Access 2010 and Access 2010 Database Engine.</p>
  
  <p>Any ideas on how to work around this error and achieve the same effect are welcome.  Please note, that I cannot alter the database in way, shape, or form.  That indirect query was created in another mdb file that has the original tables linked from the original DB.</p>
  
  <p><strong>* Update *</strong></p>
  
  <p>OleDB did not really affect anything.</p>
  
  <pre><code>$dsn= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\dummy.mdb;";
  </code></pre>
  
  <p>I'm not attempting to use it as a web backend either.  I'm not a sadomasochist.</p>
  
  <p>There is a legacy system that I must support that <em>does</em> use Access as a backend. Data gets populated there from other old systems that I must integrate into more modern systems.  Hence, the creation of an API with Apache/PHP that is running on the server supporting the legacy system.</p>
  
  <p>I need to be able to search a table that has an alphanumeric case identifier to get a numeric identifier that is unique and tied to a generator (Autonumber in access). Users have been using it a trash box for years (inconsistent data entry with sporadic notations) so the only solution I have is to strip everything except alphanumeric out of both the field value and the search value and attempt to perform a LIKE comparison against it.</p>
  
  <p>If not replace() which is access supported, what ODBC compatible functions exist that I can use do the same kind of comparison?</p>