ACC: Ver. 1.x SQL Pass-Through Query Code Doesn't Run in 2.0

• Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use the Access Basic code that you used for SQL pass-through queries in Microsoft Access version 1.x for applications in Microsoft Access version 2.0, the code does not work.

CAUSE

In Microsoft Access 1.x, to use pass-through queries, you use Access Basic code to make calls to the MSASP110.DDL file. The SQL-specific query functionality in the Microsoft Access 2.0 user interface eliminates the need for such calls. As a result, Access Basic code that makes calls to the MSASP110.DDL file does not work in Microsoft Access 2.0.

MORE INFORMATION

To use SQL pass-through queries in Microsoft Access 1.x, you call three functions from the MSASP110.DLL file: CreateRMTQueryDEF() (to open a query session), CloseRMTQueryDEF() (to close a query session), and RMTQueryExecute() (to run the pass-through query).

In Microsoft Access 2.0, however, the CreateRMTQueryDEF() and CloseRMTQueryDEF() functions are not necessary. Also, the RMTQueryExecute() function call is replaced by the new SQL-specific pass-through query. Therefore, calls to the MSASP110.DLL file cannot be used with Microsoft Access 2.0. Error handling (using the error messages log table) has also changed. Errors can be trapped in Access Basic in Microsoft Access 2.0.

To convert a Microsoft Access 1.x application that uses a pass-through query so that it works in Microsoft Access 2.0, follow these steps:

1. Remove all references in your Access Basic code to the MSASP110.DLL file and its related structures and functions.

2. Remove all CreateRMTQueryDEF() and CloseRMTQueryDEF() function calls.

3. Reformat any connect string so that it is compatible with the Microsoft Access 2.0 Connect property syntax.

4. Modify your error-handling code accordingly.

5. Convert each transaction to a standard Microsoft Access pass-through query with a Connect property setting, and make sure that the following assignments are true:

       - The Connect property is the first property assigned after the
         CreateQueryDef object.
   

       - The SQL property is the last property assigned before running
         the query.
   

REFERENCES

For more information about pass-through queries, search for "pass-through query," and then "Creating a Pass-Through Query" using the Microsoft Access Help menu.

For more information about the Connect property, search for "Connect," and then "Connect Property (Data Access)" using the Microsoft Access Help menu.