General Form for Deleting Data

This With and Do...Loop structure can be used to scan a recordset and delete records using the Delete method:

With rsDAO
    .FindFirst strCriteria
    Do Until .NoMatch
        .Delete
        .FindNext strCriteria
    Loop
End With

where strCriteria is a string containing the specified deletion criteria.

General Form for Inserting Rows

Use the AddNew method to insert a new row:

With m_rsDAO
    .AddNew
    .Fields("MyField") = "Some new string value."
    'More fields can be specified. Nulls will be inserted
    'in unspecified fields or an error will be generated
    'if the Required property is set to True for any field
    'not assigned a value here.
    .Update
End With

Note that omitting Update and then moving off of the row will cause data to be lost.

Creating Query Definitions

Appending a compiled query to the QueryDefs collection should be done when the query (even is if is a parameter query) needs to be built on the fly and run several times. This will increase application performance. For more information see the CreateQueryDef method in Microsoft's DAO reference.

Referencing Stored Queries

If the query does not need to be built on the fly then use the QDE grid in Access 97 to design and store the query. Now the general form for referencing and opening a stored SELECT query without parameters is:

'Set the module-level QueryDef type variable m_qdDAO.
Set m_qdDAO = m_dbDAO.QueryDefs("MyStoredQry")

'Now open a recordset based on the query.
Set m_rsDAO = m_qdDAO.OpenRecordset()

The general form for referring to and running a stored action query is:

Set m_qdDAO = m_dbDAO.QueryDefs("MyStoredQry")
m_qdDAO.Execute

Recall that the style here is to use module-level variables and share them among the procedures in the module where logically possible. This requires that these variables be set to nothing after the procedure runs where logically possible.