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

• Microsoft Access version 2.0

SYMPTOMS

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

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

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 11, "Working with Sets of Records," page 251