Article ID: 244719 - View products that this article applies to.
This article was previously published under Q244719
When executing a parameterized query that contains a subquery, when using the Microsoft Open Database Connectivity (ODBC) Driver for Access, various unexpected problems occur.
Reported problems include unexpected results (no records returned or the wrong records returned) or other errors indicating a type mismatch or a numeric value out of range.
This problem is caused by a bug in the underlying Microsoft Jet 4.0 engine that is included with the Microsoft Data Access Components (MDAC) versions noted above.
Parameters markers in ODBC are typically mapped from left to right with no regard as to whether they are in the main query or a subquery. When you have a main query and a subquery, Jet maps the subquery parameters first and maps the main query parameters next. Therefore, in most cases, the parameters are mapped in a different order than expected. This parameter mapping order means that unexpected parameter values are passed in to one or more of the parameters and can result in unexpected query results or type mismatch errors (for example, if a text value is passed into a numeric parameter).
For example, the following SQL query has four parameters for fields A, B, C, and D:
Normally in ODBC the programmer expects the parameters to be bound in the order A, B, C, D; however, because of this particular bug in Microsoft Jet, the parameters are bound in the order C, D, A, B.
A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next service pack for Microsoft Data Access Components that contains this fix.
To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
This problem was corrected in Microsoft Jet 4.0 Service Pack 4.
For additional information about how to download the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/239114/ )How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
Steps to Reproduce BehaviorThe following VBA code reproduces the parameter mismatch problem. Note that your VBA project requires a reference to the "Microsoft ActiveX Data Objects 2.1 Library."
Article ID: 244719 - Last Review: April 7, 2006 - Revision: 4.1