CreateQueryDef Method


Creates a new QueryDef object in a specified Connection or Database object.


Set querydef = object.CreateQueryDef (name, sqltext)

The CreateQueryDef method syntax has these parts.




An object variable that represents the QueryDef object you want to create.


An object variable that represents an open Connection or Database object that will contain the new QueryDef.


Optional. A Variant (String subtype) that uniquely names the new QueryDef.


Optional. A Variant (String subtype) that is an SQL statement defining the QueryDef. If you omit this argument, you can define the QueryDef by setting its SQL property before or after you append it to a collection.



In a Microsoft Jet workspace, if you provide anything other than a zero-length string for the name when you create a QueryDef, the resulting QueryDef object is automatically appended to the QueryDefs collection. In an ODBCDirect workspace, QueryDef objects are always temporary.

In an ODBCDirect workspace, the sqltext argument can specify an SQL statement or a Microsoft SQL Server stored procedure and its parameters.

If the object specified by name is already a member of the QueryDefs collection, a run-time error occurs. You can create a temporary QueryDef by using a zero-length string for the name argument when you execute the CreateQueryDef method. You can also accomplish this by setting the Name property of a newly created QueryDef to a zero-length string (""). Temporary QueryDef objects are useful if you want to repeatedly use dynamic SQL statements without having to create any new permanent objects in the QueryDefs collection. You can't append a temporary QueryDef to any collection because a zero-length string isn't a valid name for a permanent QueryDef object. You can always set the Name and SQL properties of the newly created QueryDef object and subsequently append the QueryDef to the QueryDefs collection.

To run the SQL statement in a QueryDef object, use the Execute or OpenRecordset method.

Using a QueryDef object is the preferred way to perform SQL pass-through queries with ODBC databases.

To remove a QueryDef object from a QueryDefs collection in a Microsoft Jet database, use the Delete method on the collection. For an ODBCDirect database, use the Close method on the QueryDef object.