Microsoft KB Archive/124341

= ACC2: "Invalid Operation" Error Msg. Running QueryDef Example =

Article ID: 124341

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q124341



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you run the sample QueryDef code on page 251 of the Microsoft Access version 2.0 "Building Applications" manual, you receive the error message "Invalid operation."



CAUSE
This error occurs because the Append method is used incorrectly with the QueryDefs collection. Although the Append method is a valid method for the QueryDefs collection, it is used incorrectly in the sample code.

The CreateQueryDef method creates a new QueryDef object and appends it to the QueryDefs collection if it is supplied with a valid name. If you try to append the QueryDef object again using the Append method, you receive an error message because the QueryDef object has already been appended to the collection.



RESOLUTION
The sample code on page 251 should read as follows.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

Dim MyDB As Database, MyQuery as QueryDef Set MyDB = DBEngine.Workspaces(0).Databases(0)

' Create QueryDef. Set MyQuery = MyDB.CreateQueryDef("All Cust", "SELECT * FROM _  Customers;")

' Set SQL property. MyQuery.SQL = "UPDATE DISTINCTROW Products SET _  Products![Supplier ID] = 2 WHERE Products![Supplier ID] = 1;"

MyQuery.Execute         ' Invoke query. MyQuery.Close           ' Close query.



STATUS
This behavior no longer occurs in Microsoft Access version 7.0.



MORE INFORMATION
You can use the Append method with the QueryDefs collection when the CreateQueryDef method does not assign a name to the QueryDef object, as in the following example:

Dim MyDB As Database, MyQuery As QueryDef Set MyDB = DBEngine.Workspaces(0).Databases(0)

' Create QueryDef object without a name. Set MyQuery = MyDB.CreateQueryDef MyQuery.Name = "All Cust" MyQuery.SQL = "SELECT * FROM Customers;" MyDB.QueryDefs.Append MyQuery

