ACC2: Help Topic "Converting Code" Sample Code Incorrect (114732)

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

ACC2: Help Topic "Converting Code" Sample Code Incorrect (114732)

SYMPTOMS

RESOLUTION

STATUS

REFERENCES