MS-Access / Getting Started

---

Modifying a QueryDef

Once you have created a QueryDef, you can modify its properties as easily as you modify any other DAO property. Here's an example:

Public Sub ModifyQuery(strName As String, strNewSQL As String)
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    'Modify the QueryDef's properties
    dbs.QueryDefs(strName).SQL = strNewSQL

    Set dbs = Nothing
End Sub

Deleting a QueryDef

Deleting a QueryDef is simple. Just issue the Delete method against the QueryDefs collection:

dbs.QueryDefs.Delete strName

Executing Queries

Queries that insert, update, or delete queries are known as action queries. While these types of queries do not return records, it is common to run them using code.

There are three ways to programmatically execute a query: using the DoCmd.RunSQL method, the object.Execute method, and the OpenRecordset method. The query argument for any of the following methods can either be the name of a permanent or temporary QueryDef, or a string expression that equates to a query.

DoCmd.RunSQL

Although not part of the DAO object model, you can execute the RunSQL method of the DoCmd object to run an action query:

DoCmd.RunSQL "UPDATE Table1 SET Field1 = 123"

Running a query this way displays a message box to confirm that you want to make changes to the database. To eliminate that message box, set the DoCmd object's SetWarnings property to False prior to calling DoCmd.RunSQL, but remember to set it back when you've finished, or all warning messages will thereafter be disabled.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Table1 SET Field1 = 123"
DoCmd.SetWarnings True

Any errors raised while executing the query will display a message box. You can disable the message box as described previously, and you can trap the error using the On Error Goto construct. By default, the query is included in an existing transaction, but you can exclude it by setting the UseTransaction property to False:

DoCmd.RunSQL "UPDATE Table1 SET Field1 = 123", False

object.Execute

You can also use the Execute method of the QueryDef object or the Database object to run an action query:

qdf.Execute options
dbs.Execute "UPDATE Table1 SET Field1 = 123", options

With the Execute method, there is no need to call the SetWarnings method to disable change confirmation message boxes because none are displayed. The Execute method operates directly on its parent object.

There are several major benefits to using the Execute method rather than the DoCmd.RunSQL method:

• Execute runs faster than DoCmd.RunSQL does.
• Execute can be included in an existing transaction, like any other DAO operation, without needing to specify an option to do so.
• You can specify several options that change the way the method works.

The following table lists the various constants that can be supplied as options for the Execute method.

Constant	Description
dbDenyWrite	Denies write permission to other users (Microsoft Access workspaces only).
dbInconsistent	Executes inconsistent updates (Microsoft Access workspaces only).
dbConsistent	Executes consistent updates (Microsoft Access workspaces only).
dbSQLPassThrough	Executes an SQL pass-through query, which passes the query to an ODBC database for processing. (Microsoft Access workspaces only).
dbFailOnError	Rolls back updates if an error occurs (Microsoft Access workspaces only).
dbSeeChanges	Generates a runtime error if another user is changing data that you are editing (Microsoft Access workspaces only).

OpenRecordset

Last, you can execute a query when you open a recordset. To do so, specify the query name in the Database object's OpenRecordset method to run a select or action query:

Set rst = dbs.OpenRecordset("SELECT * FROM Table1")

Similarly, you can open a recordset based on a query, like so:

Set qdf = dbs.QueryDefs("qryMyQuery")
Set rst = qdf.OpenRecordset(dbOpenDynaset)

The following section on recordsets describes this in greater detail.

Working with Recordsets

When you need to access and manipulate data one record at a time, you must use a Recordset object. For this reason, recordsets are the workhorses of database programming. As you've already seen, four types of recordsets are available in DAO. The one you use depends on where the data comes from, and what you want to do with it.

Creating a Recordset

You can create a recordset by using the OpenRecordset method of the Database, TableDef, or QueryDef objects:

Set rst = dbs.OpenRecordset( Source, Type, Options, LockEdits )
Set rst = object.OpenRecordset( Type, Options, LockEdits )

The Source argument specifies the name of a table or query, or a string expression that equates to an SQL query. For recordsets opened using the dbOpenTable type argument, the Source argument can only be the name of a table.

The default recordset type that is opened if you omit the Type argument, depends on the type of table you're trying to open. If you open a Microsoft Access recordset on a local table, the default is a Table type. If you open a Microsoft Access recordset against a linked table or query, the default type is dynaset.

The Type argument values are specified by a number of constants. The following code examples demonstrate how to open different types of recordsets.

Opening a Recordset Based on a Table or Query

To open a Table type recordset or dynaset-type recordset, use code such as the following:

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

'Open a table-type recordset
Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable)

'Open a dynaset-type recordset using a saved query
Set rsQuery = dbs.OpenRecordset("qryMyQuery", dbOpenDynaset)

Opening a Recordset Based on a Parameter Query

Parameter queries accept criteria based on a parameter prompt. The parameter prompt can be a hardcoded name such as the prompt or you can supply its value based on a control.

You must provide the parameter values before opening a recordset based on this type of query. To do so, you can use the Parameters collection of the QueryDef object:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qdf = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter values
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a recordset based on the parameter query
Set rst = qdf.OpenRecordset()

Opening a Recordset Based on an SQL Statement

The following code shows how to open a snapshot-type recordset based on an SQL statement:

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'Open a snapshot-type recordset based on an SQL statement
strSQL = "SELECT * FROM Table1 WHERE Field2 = 33"
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Opening a Recordset That Locks Out All Other Users

The following code opens a dynaset-type recordset using a saved query and specifies the dbDenyRead argument to prevent other users from opening the query.

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset

Set dbs = CurrentDb

'Open a dynaset-type recordset based on a saved query
Set rsSQL = dbs.OpenRecordset("qryMyQuery", _
    dbOpenDynaset, dbDenyRead)