ACC2: Help Topic "Converting Code" Sample Code Incorrect

ID: Q114732

The information in this article applies to:

Microsoft Access 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()

To correct the code, make the following two changes:

Change the line that reads
```
      Q.ReturnsRows = True 
```
to be:
```
      Q.ReturnsRecords = True 
```
The correct name for the property is ReturnsRecords.

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.

The corrected code should be as follows:


   ' 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.

Additional query words: documentation

Keywords : kbusage DcmOthr
Version : 2.0
Platform : WINDOWS
Issue type :