Access VBA Macro Programming

Set WrkSp = DBEngine.Workspaces( 0 )
WrkSp.BeginTrans
Do Until RecSet.EOF

If RecSet![First Name] = "Nancy" Then

RecSet.Edit

RecSet![Company] = "Northwind"

RecSet.Update

End If

RecSet.MoveNext

Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
WrkSp.CommitTrans
Else
WrkSp.Rollback
End If
RecSet.Close
WrkSp.close
Set RecSet = Nothing
Set WrkSp = Nothing
End Sub

In this example, there is a newWorkspaceobject called WrkSp. This is set to the current
workspace. Before your code is run to edit the recordset, aBeginTransstatement is used.
This defines the beginning of the transaction and ensures that nothing can happen to the data
until aCommitTransstatement is issued.
When the code has iterated through the recordset, a message box is displayed to ask to
save all changes. If the user selects Yes, then aCommitTransstatement is made, which
saves all the changes to the table. If the user chooses No, then aRollbackstatement is made
which erases all the changes.

Creating an Object in Memory

You can use this methodology to deal with any objects, but this example works with TableDefs
and Fields.
When you create aTableDefobject in memory, you define a variable to represent that
table by dimensioning a variable with theDimstatement. You can call your variable anything
you want as long as it has not already been used in your code and is not a reserved word (see
Chapter 2).
The advantage of creating aTableDefobject is that it can be set to represent a particular
table with aSetstatement. After that, you can use that variable to reference that table, and the
automatic list boxes showing the underlying properties, methods, and collections will still
work with it. You can work without theSetstatement, but it means working without the

Chapter 14: The Access Object Models 189