ODBC headache connecting to Access database via ODBC

SpeedBird

Attempting to connect via ODBC on 2K3 Server and XP Pro I get the following (using CLI):

Firstly, a pop-up error message: The instruction at "[hex]" referenced memory at "[hex]". The memory could not be "written". Click on OK to terminate the program Click on CANCEL to debug the program

After clicking OK a second pop-up error appears: The instruction at "[hex]" referenced memory at "[hex]". The memory could not be "read". Click on OK to terminate the program Click on CANCEL to debug the program

(the [hex] addresses change each time).

The following is in the application log: Faulting application php.exe, version 5.2.1.1, faulting module msrd3x40.dll, version 4.0.6508.0, fault address [hex].

Followed by: Faulting application php.exe, version 5.2.1.1, faulting module ntdll.dll, version 5.2.3790.1830, fault address [hex].

Here is an example of the code that I'm trying to execute. It is a slightly complicated script that queries data from a table in an Access database and updates an identical table in a MySQL database with that data:

<?php

/*
Connect to Access database
*/
$db1 = odbc_connect('dsn','','');
if (!$db1)
  {exit("Connect failed: " . $db1);}

/*
Query data and prepare array for MySQL insertion
*/
$cust_data = '';  

$cust_query = "SELECT customerid, salutation, firstname, lastname, premise, street, district, town, county, postcode, phonenumber, phonenumber1, email, note, creationdate FROM tblcustomer ORDER BY customerid";
$res = odbc_exec($db1,$cust_query);
while($r = odbc_fetch_array($res)){
$cust_data .= "('".$r['customerid']."', '".$r['salutation']."', '".$r['firstname']."', '".$r['lastname']."', '".$r['premise']."', '".$r['street']."', '".$r['district']."', '".$r['town']."', '".$r['county']."', '".$r['postcode']."', '".$r['phonenumber']."', '".$r['phonenumber1']."', '".$r['email']."', '".$r['note']."', '".$r['creationdate']."', )";
}
$cust_data = substr($cust_data,0,-1);

if (!$cust_data)
  {exit("SQL error retrieving customer data!");}

odbc_close($db1);

/*
Connect to MySQL database
*/
mysql_connect('127.0.0.1:3306', 'user', 'pass') or
   die('Could not connect: ' . mysql_error());
mysql_select_db('db2');

/*
Populate/update table in MySQL database
*/
$cust_insert = "INSERT INTO tblcustomer (customerid, title, firstname, lastname, buildingname, streetaddress, district, town, county, postcode, landline, mobile, email, linenotes, creationdate) Values $cust_data ON DUPLICATE KEY UPDATE title=VALUES(title), firstname=VALUES(firstname), lastname=VALUES(lastname), streetaddress=VALUES(streetaddress), district=VALUES(district), town=VALUES(town), county=VALUES(county), postcode=VALUES(postcode), landline=VALUES(landline), mobile=VALUES(mobile), email=VALUES(email), linenotes=VALUES(linenotes), creationdate=VALUES(creationdate)";
mysql_query($cust_insert) or die(mysql_error());

?>

Roger_Ramjet

Well, that is basically a protected memory error: php.exe is trying to use an address outside its allocated address range. Now this can be for any number of reasons but I would start with the bug reports for the named php modules and versions. It is also worth making sure that XP updates and patches are not missing, or if applied, are not the cause - they often are. You will have to check your XP update history and then read the ms knowledge base to see if this the case.

Of course, the 'quick' fix is a rebuild which in the long run will probably be less trouble, and certainly take less time. I would personally do I rebuild and only get into bug exploration if the problem persists afterwards.

SpeedBird

I've tried running the script on clean installs of XP Pro and 2K3 Server, both with the same result. So the problem exists on XP Pro (no service packs/updates), 2k3 Server (no service packs/updates) and on both XP Pro and 2k3 Server with all service packs and updates applied.

From the Googling I've done I've found out that msrd3x40.dll is part of MS Jet, and ntdll.dll is a Windows' system file (although that was fairly obvious anyway). There's nothing in bugs.php.net detailing a problem with using ODBC on either operating system, so I'm guessing that this is a problem with either Jet or ODBC. I can't find anyone who has had the exact same problem I'm having though.

Roger_Ramjet

Nasty one.

Just a thought, but have you updated Office as well? Jet 4 definitely needs ser pac 8, and there are probably some other updates that apply. Come to that, what version is the Access database?

SpeedBird

All the PC's here use Office 2003 (all updated centrally, so should all be up-to-date). Checking the version number of msrd3x40 shows 4.0.6508.0, which is the Jet 4 SP8 version.

The Access database I'm trying to connect to is Access 97 format.

Roger_Ramjet

That is it then. Access 97 format is Jet 3.? and Jet 3 had all sorts of problems especially with odbc. Just save it out as ver 4 file format ->Tools->DB->Convert DB->Access 2000 format will do.

SpeedBird

Roger Ramjet wrote:
Just save it out as ver 4 file format ->Tools->DB->Convert DB->Access 2000 format will do.

Believe me I'd love to, trouble is this database is part of a third-party application written in VB6 so I cannot upgrade it without breaking the application (and our sales staff rely on it heavily). This is also the LATEST version of their software. Early on I suspected there might be something in their database structure preventing me from accessing it via ODBC, but I've spoken to them numerous times and they insist it's nothing but a run-of-the-mill Access '97 database.

I seem to have made some small headway the last few days though. At first I tried a DSN-less connection:

$db = 'C:\\path\\database.mdb';
$conn = new COM('ADODB.Connection') or exit('Cannot start ADO.');
$db1 = $conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db") or exit('Cannot open with driver');

Without success. Then I tried adding and replacing MS Jet's files with older ones in an attempt to roll back to a previous (3.5) version. Now I get the following error:

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Microsoft Access Driver]Gen
eral error Not enough information to connect to this DSN with SQLConnect. Use
SQLDriverConnect., SQL state S1000 in SQLConnect in c:\path\synch.php on line 16

I've tried Googling this but not found anything useful yet.

Roger_Ramjet

Pity about that. Actually, I would have thought that upgrading the file would not break a VB6 app as backwards compatibility was very good with mdb files, but you can't argue with them even though they are probably only covering their arses. (Bout time they upgraded it themselves though)

Anyway, msrd3x40.dll is the MS Jet 3.51 ISAM driver and if you search msdn for it you'll find all lots of problems related to the jet 3.5 bugs I mentioned. Not a lot that explains your problems though except perhaps http://support.microsoft.com/kb/306129. Now although this is about Access 2000 it may be the same problem you are facing, especially as I found, and dismissed to soon, a discussion about IntelliShield installs that had the same sort of Access reg key fix.

Worth investigating anyway: can't use the dll cos you don't have the reg key read access, being an anonymous user I guess. Typical MS sort of problem.

Only other thing I can think of is that your OS and Office versions are using much higher versions of MDAC than the 2.5 this dll originates with. No telling what that could lead to with MS products.