ACC2: Help Topic "Converting Code" Sample Code Incorrect

• Microsoft Access version 2.0

SYMPTOMS

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

The sample code for running a pass-through query in the Microsoft Access version 2.0 Help system topic "Examples of Converting Code to Version 2.0" is not correct. If you run this code, you will receive one of the following error messages:

   Object variable not set or Name not found in this collection

    -or-

   Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
   'SELECT', or 'UPDATE'

RESOLUTION

The incorrect sample code is as follows:

   ' Given open Database object MyDB.
   Dim Q As QueryDef
   Set Q = MyDB.CreateQueryDef("MYODBCQuery")
   Q.SQL = "Exec My_Stored_Procedure"
   Q.Connect = "ODBC; DSN=MyServer; UID=sa; PWD=hithere; DATABASE=pubs"
   Q.ReturnsRows = True

   Dim R As Recordset
   Set R = Q.OpenRecordset()

1. Change the line that reads

         Q.ReturnsRows = True
   

   to be:

         Q.ReturnsRecords = True
   

   The correct name for the property is ReturnsRecords.

2. Move the line that reads

         Q.SQL = "Exec My_Stored_Procedure"
   

   so that it is directly beneath the line that reads:

         Q.Connect = "ODBC; DSN=MyServer;..."
   

   The syntax "Exec My_Stored_Procedure" is only valid in a pass-through query. Setting the connect property changes the new query from a select query to a pass-through query.

   ' Given open Database object MyDB.
   Dim Q As QueryDef
   Set Q = MyDB.CreateQueryDef("MYODBCQuery")
   Q.Connect = "ODBC; DSN=MyServer; UID=sa; PWD=hithere; DATABASE=pubs"
   Q.SQL = "Exec My_Stored_Procedure"
   Q.ReturnsRecords = True

   Dim R As Recordset
   Set R = Q.OpenRecordset()

STATUS

This problem no longer occurs in Microsoft Access version 7.0.

REFERENCES

For more examples of converting code to version 2.0, search for "converting code," and then "Examples of Converting Code to Version 2.0" using the Microsoft Access Help menu.