CreateQueryDef Method

Applies To Connection object, Database object.

Description

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

Syntax

Set querydef = object.CreateQueryDef(name, sqltext)

The CreateQueryDef method syntax has these parts.

Part	Description

querydef	An object variable that represents the QueryDef object you want to create.
object	An object variable that represents an open Connection or Database object that will contain the new QueryDef.
name	Optional. A Variant (String subtype) that uniquely names the new QueryDef.
sqltext	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.

Remarks 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.

See Also Append method, Connect property, CreateProperty method, Delete method, DROP statement, LogMessages property, Name property, OpenRecordset method, QueryDef object, ReturnsRecords property, SELECT...INTO statement, SQL property, Type property, Value property.

Example

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        ' Create temporary QueryDef.
        Set qdfTemp = .CreateQueryDef("", _
            "SELECT * FROM Employees")
        ' Open Recordset and print report.
        GetrstTemp qdfTemp
        ' Create permanent QueryDef.
        Set qdfNew = .CreateQueryDef("NewQueryDef", _
            "SELECT * FROM Categories")
        ' Open Recordset and print report.
        GetrstTemp qdfNew
        ' Delete new QueryDef because this is a demonstration.
        .QueryDefs.Delete qdfNew.Name
        .Close
    End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

    Dim rstTemp As Recordset

    With qdfTemp
        Debug.Print .Name
        Debug.Print "    " & .SQL
        ' Open Recordset from QueryDef.
        Set rstTemp = .OpenRecordset(dbOpenSnapshot)

        With rstTemp
            ' Populate Recordset and print number of records.
            .MoveLast
            Debug.Print "    Number of records = " & _
                .RecordCount
            Debug.Print
            .Close
        End With

    End With

End Function

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 reference to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Employees WHERE [HireDate] >= #1-1-93#"
    ' Create new query.
    Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
    DoCmd.OpenQuery qdf.Name
    Set dbs = Nothing
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 to 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