>
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. |
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 SubExample (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