Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

756

LISTING 21.1 (continued)

Do While Not rs.EOF

rs.Edit

rs![ReviewDate] = DateAdd(“yyyy”, 1, rs![HireDate])

rs.Update

rs.MoveNext

Loop

‘ Commit transaction:

ws.CommitTrans

ExitHere:

rs.Close

Set rs = Nothing

Exit Function

HandleError:

‘Rollback transaction:

ws.Rollback

GoTo ExitHere

End Sub

Considering the Options for Opening a

Database

By default, every time a user opens an Access database, other users are able to open and make

changes to the data in the same database. The Shared mode of opening Access databases is great

for most users because everyone is able to work with the data as if no one else were using the same

tables and records. However, this Shared mode also leads to update conflicts when more than one

user wants to simultaneously change the same record.

It’s possible, however, to open the Access database for exclusive access, which prevents others

from opening the database (and hence prevents update conflicts). While this may be necessary

sometimes to prevent contention (say, if you’re doing a large number of updates, or trying to

replace the data in a table by importing from external data sources), in general it does make the

database less usable, since users must now “take turns” using the database.

Note
Later in this chapter, I discuss the common practice of splitting an Access database application into front-end
and back-end databases. The front-end contains all the user interface (forms and report) and the code and
macros used by the application, while the back-end database (which is usually located on a file server) contains
the tables. As you’ll see in the “Splitting a Database for Network Access” section, a split database model offers
significant advantages for multiuser environments. In the meantime, however, the current discussion refers
only to a default, non-split .accdb or .mdb file.