CreateQueryDef Method

Applies To

Database Object.

Description

Creates a new QueryDef object in a specified database.

Syntax

Set querydef = database.CreateQueryDef([name][, sqltext])

The CreateQueryDef method syntax has these parts.

Part	Description

querydef	A variable of an object data type that references the QueryDef object you want to create.
database	A variable of an object data type that references the open Database object that contains the new QueryDef.
name	A string expression identifying the new QueryDef.
sqltext	A string expression (a valid SQL statement) that defines 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.

Remarks

If you include name but omit sqltext or include both name and sqltext, the QueryDef is automatically saved when you create or change it.

If the object specified by name is already a member of the QueryDefs collection, a trappable error occurs.

You can create a temporary QueryDef by setting the Name property of a newly created QueryDef to a zero-length string ("") or by using a zero-length string for the name argument when you execute the CreateQueryDef method. You can't append a temporary QueryDef to any collection because a zero-length string isn't a valid name for a permanent QueryDef. You can set the Name and SQL properties of the newly created QueryDef and subsequently append the QueryDef to the QueryDefs collection where it's stored in the database. 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.

To remove a QueryDef object from a QueryDefs collection, use the Delete method on the collection.

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

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

See Also

Append Method, Connect Property, CreateProperty Method, Delete Method, LogMessages Property, Name Property, OpenRecordset Method, QueryDef Object, ReturnsRecords Property, SQL Property, Type Property, Value Property.

Example

This example creates a QueryDef named All Pubs with an SQL statement, and then creates a dynaset-type Recordset based on the query.

Dim dbsBiblio As Database, qdfAllPubs As QueryDef
Dim rstAllPubs As Recordset
' Open a database.
Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
' Create a QueryDef.
Set qdfAllPubs = dbsBiblio.CreateQueryDef("All Pubs", _
    "SELECT * FROM Publishers;")
' Create Recordset from query.
Set rstAllPubs = qdfAllPubs.OpenRecordset
...

Example (Microsoft Access)

The following example creates a new QueryDef object, then opens the query in Datasheet view.

Sub NewQuery()
    Dim dbs As Database, qdf As QueryDef, strSQL As String

    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Employees WHERE [HireDate] >= #1-1-95#"
    Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
    DoCmd.OpenQuery qdf.Name
End Sub

Example (Microsoft Excel)

This example creates a new query based on the Customer recordset in the NWINDEX.MDB database. The query selects a snapshot of all customers in the state of Washington and then copies it onto Sheet1.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, qDef As QueryDef, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set qDef = db.CreateQueryDef("WA Region")
qDef.SQL = "SELECT * FROM Customer WHERE [Region] = 'WA';"
Set rs = db.OpenRecordset("WA Region")
numberOfRows = Sheets("Sheet1").Cells(1, 1).CopyFromRecordset(rs)
Sheets("Sheet1").Activate
MsgBox numberOfRows & " records have been copied."
rs.Close
db.Close