MyField must be a numeric value. The method will return the average of all the MyField
values within the table. You can use an optional criterion as follows:
MsgBox DAvg("MyField", "MyTable","MyName='Richard'")
Notice that single quotes are used within the string parameter to denote that the MyName
criterion is a string.
DCount
This method allows DCount lets you extract the number of records from a specified table. An
example is:
MsgBox DCount("MyField", "MyTable")
You can use an optional criterion as follows:MsgBox DCount("MyField", "MyTable","MyName='Richard'")
Notice that single quotes are used within the string parameter to denote that the MyName
criterion is a string.
DbEngine
DbEngineis a top-level DAO(Data Access Object) object. You can use this object to work
with transactions.
One of the problems with processing data through VBA is that an unexpected error may
be encountered, perhaps due to the data type being incompatible. A power outage could also
stop your program. When this happens, some records will have been processed, but not all of
them.
The question is how far your code has processed records. At this point, the data integrity
has been compromised with some records having been processed, but others are still waiting,
and you have no idea where the break happened.
If you are running a large process, it is a good idea to use a Transaction. This allows your
code to amend the recordset, but it does not actually get written back to the table until a
Commitstatement is made. TheCommitstatement is the final VBA statement, so if for any
reason your procedure fails part way through, the table remains as it was before.
The code is similar to previous examples:
Sub EditData()
Dim RecSet As Recordset, WrkSp as Workspace
Set RecSet = CurrentDb.OpenRecordset("Employees")
Set WrkSp = DBEngine.Workspaces( 0 )
WrkSp.BeginTrans
Do Until RecSet.EOF
Chapter 15: The Main Objects 195