CreateQueryDef Method and SQL Property Example

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 _
    .DefaultFilePath & "\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)
MsgBox numberOfRows & " records have been copied."
rs.Close
db.Close