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

End Sub

Note that once a QueryDef object exists, we may still use the OpenRecordset or Execute
methods to run the query. The Execute method can also be used on a Database object to
run an SQL statement. Here is an example that reduces the price of each book in the
BOOKS table by 10%:

Dim db As DATABASE
Set db = CurrentDb
db.Execute "UPDATE BOOKS SET Price = Price*0.9"

15.6.3 Properties of a QueryDef Object

When a QueryDef object is created or changed, Jet sets certain properties, such as
DateCreated, LastUpdated, and Type. (Note that the QueryDefs collection may need
refreshing before these properties can be read.) Some of the possible query types are
listed in Table 15-4.

Table 15-4. Poss i ble query-type constants
Constant

Query type Value

d bQSelect S elect 0

d bQAction A ction 2 40

d bQCrosstab C rosstab 1 6

d bQDelete D elete 3 2

d bQUpdate U pdate 4 8

d bQAppend A ppend 6 4

d bQMakeTable Make-table 8 0

The RecordsAffected property returns the number of records affected by the last
application of the Execute method. Let us illustrate.

Example 15-7 modifies the earlier action-query example to perform the action (10% price
increase) if and only if the increase will affect more than 15 books in the table. This is
done using the BeginTrans, Committrans, and Rollback properties of the current
Workspace object.

Example 15-7. A RecordsAffected property example

Sub exaCreateAction2( )

Dim ws As Workspace
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String

Set ws = DBEngine(0)
Set db = CurrentDb