Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

755

ODBC and client-server applications are in high demand, so you have to find an acceptable solu-

tion to these performance problems. You can do a couple things to speed performance when using

data sources other than native Access tables:

l Use SQL pass-through. SQL pass-through allows you to send a SQL statement (or a

stored procedure name) to a host database to let it execute the request, returning only the

result set (instead of the entire recordset) for local processing. This ability takes advantage

of the host platform’s capabilities, and it keeps network traffic down. The disadvantage to

SQL pass-through is that you must use the host database’s native SQL syntax instead of

letting Access generate the SQL request for you. This makes your application less portable

to other database platforms and doesn’t let you take advantage of Access’s Query by

Example (QBE) facility; plus, you can’t use parameters in your queries.

Note
A SQL pass-through query is not SQL Server specific. Any server database engine that understands SQL
(Structured Query Language) can be the target of a SQL pass-through query.

l Use transactions. Transactions (the BeginTrans and CommitTrans methods of an

ADO Connection object or DAO Workspace object) allow you to cache reads, edits,

and updates in local memory instead of reading from or writing to disk or your external

data source. If you know that you’ll be doing several updates or reads within a VBA proce-

dure, enclose the updates in transaction statements.

Better performance is achieved because you don’t have to wait on your request to be sent

back around the network to your data source, and because all writes are done at one time

(when the CommitTrans method is executed) instead of each time the Update method

is executed.

Listing 21.1 shows the use of transactions within a VBA procedure. (In the code editor,

choose Tools ➪ References to open the References dialog box, and then make sure that the

Microsoft Office 14.0 Access database engine Object Library is selected.)

LISTING 21.1

Demonstrating DAO Transactions

Public Sub Transaction_DAO()

Dim db As DAO.Database

Dim ws As DAO.Workspace

Dim rs As DAO.Recordset

On Error GoTo HandleError

Set db = CurrentDb

Set ws = DBEngine.Workspaces(0)

Set rs = db.OpenRecordset(“Employees”, dbOpenTable)

‘ Begin transaction:

ws.BeginTrans

rs.MoveFirst

continued