Access VBA Macro Programming

RecSet.AddNew

RecSet!Company = "MyCompany"

RecSet![Last Name] = "Shepherd"

RecSet![First Name] = "Richard"

RecSet.Update

RecSet.Close

Set RecSet = Nothing

End Sub

Again, aRecordsetobject is created, but this time we do not need to iterate through it.

TheAddNewmethod is used and the fields for the new record are populated with data. Not

all are required fields, so for the purposes of this example only three are populated. The

recordset is then updated and the record is added to the table. Note that you do not need to do

anything with the ID field since this is an autonumber field and is updated automatically.

You have probably noticed by now how much code references the “tree” of objects. For

example, you can start with theApplicationobject, reference the CurrentDb underneath it,

then reference aTableDefwithin theTablesDefscollection, and finally reference a Field.

TheApplicationobject is the root, theCurrentDbandTableDefobjects are the

branches, and theFieldobject is the leaves. This can become somewhat laborious if you’re

working with many lines of code and you have to keep writing out this enormous reference to

identify a particular value. As a shortcut, you can refer to the TablesDef name, for example:

MsgBox CurrentDb.TableDefs("Employees").Fields.Count

This will work, but fortunately, there is a way to cut down the amount of referencing and

keep the integrity of the code by using theDimstatement to create aTableDefobject in

memory.

Using 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. There could also be a power

outage that might 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’s integrity

has been compromised—with some records having been processed, and others 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")

188 Microsoft Access 2010 VBA Macro Programming