Microsoft KB Archive/114732

= ACC2: Help Topic "Converting Code" Sample Code Incorrect =

Article ID: 114732

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q114732





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.

