MS Access 2010, Windows 7, 64 bit - ODBC error 3146 (ODBC--Call Failed) after 8192 SQL Update requests
I am consistently receiving an error:
3146 (ODBC--Call Failed) ...
after 8,192 successful SQL update requests from an MS Access 2010 front-end VBA procedure to a target table on the MySQL backend. I have tried this with three different target tables and consistently receive the same result.
I am using MS Access 2010 on a Windows 7, 64 bit Intel i7 machine (new computer, new installation) with MySQL as a backend. MS Access and MySQL are both running on the same computer, as this is a development environment. MySQL and the ODBC connector are both the latest version. I have three very large tables (18 M rows, 3 M rows, and 1 M rows) loaded in MySQL that I am trying to update using an MS Access Visual Basic for Applications (VBA) procedure.
My Access VBA procedure iterates through the target table collecting data (adOpenForwardOnly and adOpenReadOnly) and then issues an "UPDATE <table name> SET <target field> = <value> WHERE id= <primary key>" SQL request. This works correctly until the procedure reaches the 8,193rd record, when I get the error.
I have tried this with each target table several times and consistently see this failure. Because it consistently fails after the 8192nd attemps, I am thinking this may a configuration problem, but I cannot figure out how to fix it.
Can anyone tell me if this is a configuration error or a bug? Any assistance is greatly appreciated.
Chuck