Programming DAO: Data Definition Language - Access Database Design and Programming - page 241

object. Let us illustrate. The code in Example 15-5 creates a new select query and
displays the record count for the resulting recordset.
Example 15-5. A CreateQueryDef method example
Sub exaCreateSelect( )
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String
Dim rs As Recordset
Set db = CurrentDb
' Create an SQL SELECT statement
strSQL = "SELECT * FROM BOOKS WHERE Price > 20"
' Create a new QueryDef object
Set qdf = db.CreateQueryDef("NewQuery", strSQL)
' Open a recordset for this query
Set rs = qdf.OpenRecordset
' Move to end of recordset
rs.MoveLast
' Show record count
MsgBox "There are " & rs.RecordCount & " books with price exceeding
$20"
End Sub
The code in Example 15-6 creates a new action query and executes it. The effect is to
raise the price of each book in the BOOKS table by 10%.
Example 15-6. A new action query example
Sub exaCreateAction( )
' Creates an action query and executes it
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb
' Create an SQL UPDATE statement
' to raise prices by 10%
strSQL = "UPDATE BOOKS SET Price = Price*1.1"
' Create a new QueryDef object
Set qdf = db.CreateQueryDef("PriceInc", strSQL)
qdf.Execute