Chapter 17
Chapter 17
Multiuser
Considerations
Today you'll look at some issues related to
designing and coding applications that serve multiple users. Multiuser applications pose
some unique challenges when it comes to database operations. These challenges are the main
topics of this chapter:
- Database locking schemes: You'll examine the locking
system used by the Microsoft Jet database engine, and you'll look at the differences
between optimistic and pessimistic locking schemes. You'll learn a scheme for performing
multitable locking of data tables in highly relational databases.
- Cascading updates and deletes: You'll learn how to
use these features of the Microsoft Jet database engine to enforce database relations
using the Cascading Updates and Deletes options.
- Transaction management: You'll see the process of
transaction management, as well as how to add transaction management to your Visual Basic
applications by using the BeginTrans, CommitTrans, and Rollback
methods. Transaction management using the SQL pass-through method with back-end databases
also is covered.
By the time you complete this chapter, you'll be
able to add transaction management to your Visual Basic applications, and you'll
understand using cascading updates and deletes to maintain the referential integrity of
your database. You also will know how to perform database-level, table-level, and
page-level locking schemes in your database applications.
Database
Locking Schemes
Whenever more than one person is accessing a single
database, some type of process must be used to prevent two users from attempting to update
the same record at the same time. This process is a locking scheme. In its simplest form,
a locking scheme allows only one user at a time to update information in the database.
The Microsoft Jet database engine provides three
levels of locking:
- Database locking: At this level, only one user at a
time can access the database. Use this locking level when you need to perform work on
multiple, related database objects (such as tables, queries, indexes, and relations) at
the same time.
- Table locking: At this level, only one user at a time
can access the locked table. Use this locking level when you need to perform work on
multiple records in the same table.
- Page locking: At this level, only one user can access
the page of records in the database table. This is the lowest locking level provided by
Microsoft Jet. Page locking is handled automatically by Visual Basic whenever you attempt
to edit or update a record in a dataset.
Database
Locking
Database-level locking is the most restrictive
locking scheme you can use in your Visual Basic application. When you open the database
using the Visual Basic data control, you can lock the database by setting the Exclusive
property of the data control to True. After you open the database by using Visual
Basic code, you can lock the database by setting the second parameter of the OpenDatabase
method to True. Here's an example:
Set db = DbEngine.OpenDatabase("c:mydb",True)
When the database is locked, no other users can open
it. Other programs cannot read or write any information until you close the database. You
should use database-level locking only when you must perform work that affects multiple
data objects (such as tables, indexes, relations, and queries). The Visual Basic CompactDatabase
operation, for example, affects all the data objects, so the database must be opened
exclusively.
If you need to perform an operation to update the
customer ID values in several tables and you also need to update several queries to match
new search criteria, you should use database-level locking.
Take a look at a Visual Basic project to see how
database-level locking works. Load Visual Basic and open a new project. Add a data control
to the form. Set its DataBaseName property to C:\TYSDBVB5\SOURCE\DATA\MULTIUSE.MDB
and its Exclusive property to True. Save the form as MULTIUS1.FRM and the project
as MULTIUS1.VBP. Now create an executable version of the project by choosing File
| Make MULTIUS1.EXE from the Visual Basic main menu. Use MULTIUS1.EXE as
the name of the executable file.
Now run the executable file. It loads and displays
the data control. Run a second instance of the executable file. This is an attempt to run
a copy of the same program. Because this second copy attempts to open the same database
for exclusive use, you see an error message when the second program starts (see Figure
17.1).
Figure 17.1. Attempting to
open a locked database.
Notice that the second program continues after the error occurs, even though the database
is not opened. You can check for the error when you first load the project by adding the
following code to the Error event of the data control:
Private Sub Data1_Error(DataErr As
Integer, Response As Integer)
If Err <> 0 Then
MsgBox Error$(Err)+Chr(13)+"Exiting
Program", vbCritical, "Data1_Error"
Unload Me
End If
End Sub |
Add this code to the Data1_Error event and
then recompile the program. Again, attempt to run two instances of this program. This
time, when you attempt to start the second instance, you receive a similar message, after
which the program exits safely. (See Figure 17.2.)
Figure 17.2. Trapping the
locked database error.
Table Locking
You can use table-level locking to secure a single
table while you perform sensitive operations on the table. If you want to increase the
sale price of all items in your inventory by five percent, for example, you open the table
for exclusive use and then perform the update. After you close the table, other users can
open it and see the new price list. Using table-level locking for an operation like this
can help prevent users from writing sales orders that contain some records with the old
price and some records with the new price.
Now modify the MULTIUS1.VBP project to
illustrate table-level locking. Reopen the project and set the Exclusive property of the
data control to False. This setting allows other users to open the database while your
program is running. Now set the RecordSource property to MasterTable and set the Options
property to 3. Setting the Options property to 3 opens the Recordset with the DenyWrite
(1) and DenyRead (2) options turned on. This prevents other programs from opening
MasterTable while your program is running.
Save and recompile the program. Start a copy of the
executable version of the program. It runs without error. Now attempt to start a second
copy of the same program. You see an error message telling you that the table could not be
locked because it is in use elsewhere--that is, by the first instance of the program (see
Figure 17.3).
Figure 17.3. Attempting to
open a locked table.
You can perform the same table-locking operation by using this Visual Basic code:
Sub OpenTable()
On Error GoTo OpenTableErr
`
Dim db As Database
Dim rs As Recordset
`
Set db =
DBEngine.OpenDatabase("C:\TYSDBVB5\SOURCE\DATA\MULTIUSE.MDB")
Set rs = db.OpenRecordset("MasterTable", dbOpenTable,
_dbDenyRead + dbDenyWrite)
`
GoTo OpenTableExit
`
OpenTableErr:
MsgBox Error$(Err) + Chr(13) + "Exiting Program", vbCritical,
"OpenTable"
GoTo OpenTableExit
`
OpenTableExit:
`
End Sub |
Notice the use of the dbDenyRead and dbDenyWrite
constants in the OpenRecordset method. This is the same as setting the Option
property of the data control to 3. Also notice that an error trap is added to the module
to replace the code in the Error event of the data control.
Page Locking
The lowest level of locking available in Visual
Basic is page-level locking. Page-level locking is handled automatically by the Microsoft
Jet engine and cannot be controlled through Visual Basic code or with data-bound control
properties. Each time a user attempts to edit or update a record, the Microsoft Jet
performs the necessary page locking to ensure data integrity. What Is Page Locking? A data
page can contain more than one data record. Currently, the Microsoft Jet data page is
always 2KB. Locking a data page locks all records that are stored on the same data page.
If you have records that are 512 bytes in size, each time Microsoft Jet performs a page
lock, four data records are locked. If you have records that are 50 bytes in size, each
Microsoft Jet page lock can affect 40 data records.
The exact number of records that are locked on a
page cannot be controlled or accurately predicted. If your data table contains several
deleted records that have not been compacted out by using the CompactDatabase
method, you have "holes" in your data pages. These holes do not contain valid
records. Also, data pages contain records that are physically adjacent to each
other--regardless of any index, filter, or sort order that has been applied to create the
dataset. Even though records in a dataset are listed one after another, they might not be
physically stored in the same manner. Therefore, editing one of the dataset records might
not lock the next record in the dataset list. Pessimistic and Optimistic Locking Even
though page-level locking is performed automatically by Microsoft Jet, you can use the
LockEdits property of a record set to control how page-locking is handled by your
application. Two page-locking modes are available: pessimistic locking (LockEdits=True)
and optimistic locking (LockEdits=False). The default locking mode is
pessimistic.
In pessimistic locking mode, Microsoft Jet locks the
data page whenever the Edit or AddNew method is invoked. The page stays
locked until an Update or Cancel method is executed. When a page is
locked, no other program or user can read or write any data records on the locked data
page until the Update or Cancel method has been invoked. The advantage
of using the pessimistic locking mode is that it provides the highest level of data
integrity possible at the page level. The disadvantage of using the pessimistic locking
mode is that it can lock data pages for a long period of time. This can cause other users
of the same database to encounter error messages as they attempt to read or write data in
the same table.
In optimistic locking mode, Microsoft Jet only locks
the data page whenever the Update method is invoked. Users can invoke the Edit
or AddNew method and begin editing data without causing Microsoft Jet to execute
a page lock. When the user is done making changes and saves the record using the Update
method, Microsoft Jet attempts to place a lock on the page. If it is successful, the
record is written to the table. If Microsoft Jet discovers that someone else also has
edited the same record and already has saved it, the update is canceled and the user is
informed with an error message saying that someone already has changed the data.
The advantage of using optimistic locking is that
page locks are in place for the shortest time possible. This reduces the number of lock
messages users receive as they access data in your database. The disadvantage of using
optimistic locking is that it is possible for two users to edit the same record at the
same time. This can lead to lock errors at update time rather than at read time. An
Example of Page-Level Locking In this section, you build a new Visual Basic project to
demonstrate page-level locking as well as the differences between pessimistic and
optimistic locking. Load Visual Basic and start a new project.
Place a command button on the form. Set its Name
property to cmdEdit and its Caption property to &Edit. Add a frame control to the form
and set its Caption property to Page Locking. Place two option button controls in the
frame control. Set the Caption property of Option1 to Pessimistic and the Caption property
of Option2 to Optimistic. Use Figure 17.4 as a layout guide.
Figure 17.4. Laying out
the page-locking project.
Now you need to add code to this demo. First, place the following variable declarations in
the general declarations section of the form:
Option Explicit
Dim db As Database
Dim rs As Recordset
Dim cName As String
Dim nMax As Integer
Now add the following code to the Form_Load
event. This code prompts you for a name for the form header. It then opens the database
and data table, and it counts all the records in the table:
Private Sub Form_Load()
` get instance ID
cName = InputBox("Enter Job Name:")
Me.Caption = cName
`
` load db and open set
Set db = OpenDatabase("C:\TYSDBVB5\SOURCE\DATA\MULTIUSE.MDB")
Set rs = db.OpenRecordset("mastertable", dbOpenTable,
dbSeeChanges)
`
` count total recs in set
rs.MoveLast
nMax = rs.RecordCount
`
End Sub |
Now add the following two code pieces to the Click
events of the option buttons. These routines toggle the LockEdits property of the
Recordset between pessimistic locking (LockEdits=True) and optimistic locking (LockEdits=False).
This code snippet turns on pessimistic locking:
Private Sub Option1_Click()
If Option1 = True Then
rs.LockEdits = True
Else
rs.LockEdits = False
End If
End Sub
This code snippet turns on optimistic locking:
Private Sub Option2_Click()
If Option2 = True Then
rs.LockEdits = False
Else
rs.LockEdits = True
End If
End Sub
Finally, add the following code to the cmdEdit_Click
event of the form. While in Edit mode, this code prompts you for a record number. It then
moves to that record, invokes the Edit method, makes a forced change in a
Recordset field, and updates some titles and messages. When the form is in Update mode,
this routine attempts to update the Recordset with the changed data and then resets some
titles. Here's the code:
Private Sub cmdEdit_Click()
On Error GoTo cmdEditClickErr ` set trap
`
Dim nRec As Integer ` for rec select
Dim X As Integer ` for locator
`
` are we trying to edit?
If cmdEdit.Caption = "&Edit" Then
` get rec to edit
nRec = InputBox("Enter Record # to Edit [1
- " +
_Trim(Str(nMax)) + "]:", cName)
` locate rec
If nRec > 0 Then
rs.MoveFirst
For X = 1 To nRec
rs.MoveNext
Next
rs.Edit ` start edit
mode
` change rec
If Left(rs.Fields(0),
1) = "X" Then
rs.Fields(0)
= Mid(rs.Fields(0), 2, 255)
Else
rs.Fields(0)
= "X" + rs.Fields(0)
End If
` tell `em you changed
it
MsgBox "Modified
field to: [" + rs.Fields(0) + "]"
` prepare for update
mode
cmdEdit.Caption =
"&Update"
Me.Caption = cName +
" [Rec: " + Trim(Str(X - 1)) + "]"
End If
Else
rs.Update ` attempt update
cmdEdit.Caption = "&Edit"
` fix caption
Me.Caption = cName
` fix header
dbengine.idle dbfreelocks ` pause
VB
End If
`
GoTo cmdEditClickExit
`
cmdEditClickErr:
` show error message
MsgBox Trim(Str(Err)) + ": " + Error$, vbCritical, cName +
"[cmdEdit]"
`
cmdEditClickExit:
`
End Sub |
Notice that there is a new line in this routine: the
DBEngine.Idle method. This method forces Visual Basic to pause for a moment to
update any Dynaset or Snapshot objects that are opened by the program. It is a good idea
to place this line in your code so that it is executed during some part of the update
process. This ensures that your program has the most recent updates to the dataset.
Save the form as MULTIUS2.FRM and the
project as MULTIUS2.VBP. Compile the project and save it as MULTIUS2.EXE.
Now you're ready to test it. Load two instances of the compiled program. When it starts
up, you are prompted for a job name. It does not matter what you enter for the job name,
but make sure that you enter different names for each instance. The name you enter is
displayed on messages and form headers so that you can tell the two programs apart.
Position the two instances apart from each other on the screen. (See Figure 17.5.)
First, you'll test the behavior of pessimistic page
locking. Make sure that the Pessimistic radio button in the Page Locking frame is selected
in both instances of the program. Now click the Edit button of the first instance of the
program; when prompted, enter 1 as the record to edit. This program now has
locked a page of data. Switch to the second instance of the program and click the Edit
button. You'll see error 3260, which tells you that the data is unavailable. (See Figure
17.6.)
Figure 17.5. Running two
instances of the page-locking project.
Figure 17.6. A failed attempt at editing during
pessimistic locking.
Remember that pessimistic locking locks the data page as soon as a user begins an edit
operation on a record. This lock prevents anyone else from accessing any records on the
data page until the first instance releases the record by using Update or UpdateCancel.
Now click the error message box and then click the Update button to release the record and
unlock the data page.
Now you test the behavior of Microsoft Jet during
optimistic locking. Select the Optimistic radio button on both forms. In the first form,
click Edit and enter 1 when prompted. The first instance now is editing record 1.
Move to the second instance and click Edit. This time, you do not see an error message.
When prompted, enter 1 as the record to edit. Again, you see no error message as
Microsoft Jet allows you to begin editing record 1 of the set. Now both programs are
editing record 1 of the set.
Click the Update button of the second instance of
the program to save the new data to the dataset. The second instance now has read, edited,
and updated the same record opened earlier by the first instance. Now move to the first
instance and click the Update button to save the changes made by this instance. You'll see
Error 3197, which tells you that data has been changed and that the update has been
canceled. (See Figure 17.7.)
Figure 17.7. A failed
attempt to update during optimistic locking.
Optimistic locking occurs at the moment the Update method is invoked. Under the
optimistic scheme, a user can read and edit any record he or she chooses. When the user
attempts to write the record back out to disk, the program checks to see whether the
original record was updated by any other program since the user's version last read the
record. If changes were saved by another program, error 3197 is reported. When to Use
Pessimistic or Optimistic Page Locking The advantage of using pessimistic locking is that
once you begin editing a record, you can save your work because all other users are
prevented from accessing that record. The disadvantage of using pessimistic locking is
that if you have many people in the database, it is possible that quite a bit of the file
is unavailable at any one time.
The advantage of using optimistic locking is that it
occurs only during an update and then only when required. Optimistic locks are the
shortest in duration. The disadvantage of using optimistic locking is that, even though
more than one user can edit a dataset record at one time, only one person can save that
dataset record. This usually is the first person to complete the edit (not the person who
opened the record first or the person who saves it last). This can be very frustrating for
users who have filled out a lengthy data entry screen only to discover that they cannot
update the data table! Except in rare cases where there is an extreme amount of network
traffic, you probably will find that optimistic locking is enough.
NOTE: All ODBC data sources use optimistic
locking only.
Using
Cascading Updates and Deletes
In the lesson on Day 9, "Visual Basic and the
Microsoft Jet Engine," you learned how to identify and define cascading updates and
delete relationships by using the relation data-access object. At the time, a particular
aspect of relation objects was not fully covered: the capability to define cascading
updates and deletes in order to enforce referential integrity. By using cascading updates
and deletes in your database definition, you can ensure that changes made to columns in
one data table are distributed properly to all related columns in all related tables in
the database. This type of referential integrity is essential when designing and using
database applications accessed by multiple users.
Microsoft Jet can enforce update and delete cascades
only for native Microsoft Jet format databases. Microsoft Jet cannot enforce cascades that
involve an attached table.
TIP: Cascading options should be added at
database design time and can be accomplished by using the Visdata program (see Day 7,
"Using the Visdata Program") or by using Visual Basic code (see Day 9).
Cascading occurs when users update or delete columns
in one table that are referred to (via the relation object) by other columns in other
tables. When this update or delete occurs, Microsoft Jet automatically updates or deletes
all the records that are part of the defined relation. If you define a relationship
between the column Valid.ListID and the column Master.ListID, for example, any time a user
updates the value of Valid.ListID, Microsoft Jet scans the MasterTable and updates the
values of all Master.ListID columns that match the updated values in the Valid.ListID
column. In this way, as users change data in one table, all related tables are kept in
sync through the use of cascading updates and deletes.
Building the
Cascading Demo Project
The MULTIUSE.MDB database used in the
earlier exercise is also used for this exercise. This database has a one-to-many
relationship with enforced referential integrity for both cascading updates and cascading
deletes. ValidTypes is the base table, and CustType is the base field. MasterTable is the
foreign table, and CustType is the foreign field. You might find it helpful to open this
database in the Visual Data Manager (Visdata) and explore the structure of these two
tables.
TIP: It might seem to you that the terms base
table and foreign table are used incorrectly in the relation definition. It might help you
to remember that all relation definitions are based on the values in the ValidTypes table.
Also, it might help to remember that any data table related to the ValidTypes table is a
foreign table.
Now you build a project that illustrates the process
of cascading updates and deletes. Use the information in Table 17.1 and Figure 17.8 to
build the MULTIUS3.VBP project.
Table 17.1. The control
table for the MULTIUS3.VBP project.
Control |
Property |
Setting |
Form |
Name |
Ch1703 |
|
Caption |
Cascading Demo |
|
Left |
1020 |
|
Height |
4275 |
|
Top |
1170 |
|
Width |
6480 |
DBGrid |
Name |
DBGrid1 |
|
AllowAddNew |
True |
|
AllowDelete |
True |
|
Height |
2715 |
|
Left |
120 |
|
Top |
120 |
|
Width |
3000 |
DBGrid |
Name |
DBGrid2 |
|
AllowAddNew |
True |
|
AllowDelete |
True |
|
Height |
2715 |
|
Left |
3240 |
|
Top |
120 |
|
Width |
3000 |
Data Control |
Name |
Data1 |
|
Caption |
Master Table |
|
DatabaseName |
C:\TYSDBVB5\SOURCE\ DATA\MULTIUSE.MDB |
|
Height |
300 |
|
Left |
120 |
|
RecordsetType |
1-Dynaset |
|
RecordSource |
MasterTable |
|
Top |
3000 |
|
Width |
3000 |
Data Control |
Name |
Data2 |
|
Caption |
Valid Types |
|
DatabaseName |
C:\TYSDBVB5\SOURCE\ DATA\MULTIUSE.MDB |
|
Height |
300 |
|
Left |
3240 |
|
RecordsetType |
1-Dynaset |
|
RecordSource |
ValidTypes |
|
Top |
3000 |
|
Width |
3000 |
Command Button |
Name |
Command1 |
|
Caption |
Refresh |
|
Height |
300 |
|
Left |
2580 |
|
Top |
3480 |
|
Width |
1200 |
Figure 17.8. Laying out the MULTIUS3.FRM
form.
Only two lines of Visual Basic code are needed to complete the form. Add the following
lines to the Command1_Click event. These two lines update both data controls and
their associated grids:
Private Sub Command1_Click()
Data1.Refresh
Data2.Refresh
End Sub
Save the form as MULTIUS3.FRM
and the project as MULTIUS3.VBP, and then run the project. Now you're ready to
test the cascading updates and deletes.
Running the
Cascading Demo Project
When you run the project, you see the two tables
displayed in each grid, side by side. First, test the update cascade by editing one of the
records in the Valid Types table. Select the first record and change the CustType column
value from T01 to T09. After you finish the edit and move the record
pointer to another record in the ValidTypes grid, click the Refresh button to update both
datasets. You see that all records in the MasterTable that had a value of T01 in
their CustType field now have a value of T09. The update of ValidTypes was
cascaded into the MasterTable by Microsoft Jet.
Now add a new record with the CustType value of T99
to the ValidTypes table (set the Description field to any text you want). Add a record to
the MasterTable that uses the T99 value in its CustType field. Your screen should
look something like the one shown in Fig-ure 17.9.
Figure 17.9. Adding new
records to the MULTIUSE.MDB database.
Delete the T99 record from
the ValidTypes table by highlighting the entire row and pressing Delete. After you delete
the record, click the Refresh button again to update both data controls. What happens to
the record in the MasterTable that contains the T99 value in the CustType field?
It is deleted from the MasterTable! This shows the power of the cascading delete. When
cascading deletes are enforced, any time a user deletes a record from the base table, all
related records in the foreign table also are deleted.
When to Use
the Cascading Updates and Deletes
The capability to enforce cascading updates and
deletes as part of the database definition is a powerful tool. With this power comes some
responsibility, too, however. Because database cascades cannot easily be undone, you
should think through your database design carefully before you add cascading features to
your database. It is not always wise to add both update and delete cascades to all your
relationships. At times, you might not want to cascade all update or delete operations.
Whenever you define a relation object in which the
base table is a validation table and the foreign table is a master table, it is wise to
define an update cascade. This ensures that any changes made to the validation table are
cascaded to the related master table. It is not a good idea to define a delete cascade for
this type of relation. Rarely do you want to delete all master records whenever you delete
a related record from the validation table. If the user attempts to delete a record from
the validation table that is used by one or more records in the master table, Microsoft
Jet issues an error message telling the user that it is unable to delete the record.
Whenever you define a relation object in which the
base table is a master table and the foreign table is a child table (for example,
CustomerMaster.CustID is the base table and CustomerComments.CustID is the foreign table),
you might want to define both an update and a delete cascade. It is logical to make sure
that any changes to the CustomerMaster.CustID field would be updated in the
CustomerComments.CustID field. It also might make sense to delete all CustomerComments
records whenever the related CustomerMaster record is deleted. This is not always the
case, though. If the child table is CustomerInvoice, for example, you might not want to
automatically delete all invoices on file. Instead, you might want Microsoft Jet to
prevent the deletion of the CustomerMaster record if a related CustomerInvoice record
exists.
The key point to remember is that
cascades are performed automatically by Microsoft Jet, without any warning message. You
cannot create an optional cascade or receive an automatic warning before a cascade begins.
If you choose to use cascades in your database, be sure to think through the logic and the
relations thoroughly, and be sure to test your relations and cascades before using the
database in a production setting.
Transaction
Management
Another important tool for maintaining the integrity
of your database is the use of transactions to manage database updates and deletes. Visual
Basic enables you to enclose all database update operations as a single transaction.
Transactions involve two steps: First, mark the start of a database transaction with the BeginTrans
keyword; second, mark the end of the database transaction with the CommitTrans or
RollBack keyword. You can start a set of database operations (add, edit, and
delete records) and then, if no error occurs, you can use the CommitTrans keyword
to save the updated records to the database. If you encounter an error along the way,
though, you can use the RollBack keyword to tell Microsoft Jet to reverse all
database operations completed up to the point where the transaction first began.
Suppose that you need to perform a
series of database updates to several tables as part of a month-end update routine for an
accounting system. This month-end processing includes totaling transactions by customer
from the TransTable, writing those totals to existing columns in a CustTotals table,
appending the transactions to the HistoryTable, and deleting the transactions from the
TransTable. The process requires access to three different tables and involves updating
existing records (appending new records to a table and deleting existing records from a
table). If your program encounters an error part of the way through this process, it will
be difficult to reconstruct the data as it existed before the process began. In other
words, it will be difficult unless you used Visual Basic transactions as part of the
update routine.
Microsoft
Jet Transactions and the Workspace Object
All Microsoft Jet transactions are applied to the
current workspace object. (See Day 10, "Creating Database Programs with Visual Basic
Code," for a discussion of the Workspace object.) If you do not name a Workspace
object, Visual Basic uses the default workspace for your program. Because transactions
apply to an entire workspace, it is recommended that you explicitly declare workspaces
when you use transactions. This gives you the capability to isolate datasets into
different workspaces and better control the creation of transactions.
Here's the exact syntax for starting a transaction:
Workspace(0).BeginTrans `
starts a transaction
...
If Err=0 Then
Workspaces(0).CommitTrans ` completes a transaction
Else
Workspaces(0).Rollback ` cancels a transaction
End If
|
In this code, the default workspace
for the transaction area is used. In an actual program, you should name a workspace
explicitly.
Building the
Microsoft Jet Transaction Project
You now build a small project that illustrates one
possible use for transactions in your Visual Basic applications. You create a database
routine that performs the tasks listed in the previous example. You open a transaction
table, total the records to a subsidiary table, copy the records to a history file, and
then delete the records from the original table.
TIP: To avoid errors when running this
project, make sure that you selected the appropriate DAO reference before executing the
program. Do this by choosing Project | References from the Visual Basic 5 menu. Then
enable the checkbox next to Microsoft DAO 3.5 object library.
Write two main routines: one to declare the
workspace and open the database, and one to perform the database transaction. First, add
the following code to the general declarations section of a new form in a new project:
Option Explicit
Dim db As Database ` database object
Dim wsUpdate As workspace ` workspace object
Dim nErrFlag As Integer ` error flag |
These are the form-level variables you need to
perform the update.
Add the following code, which creates the workspace
and opens the database. Create a new Sub called OpenDB and place the
following code in the routine:
This routine creates a new workspace object to
encompass the transaction and then opens the database for exclusive use. You don't want
anyone else in the system while you perform this major update. An error-trap routine has
been added here in case you can't open the database exclusively.
Now you can add the code that performs the actual
month-end update. Do this by using the SQL statements you learned in the lessons on Days
13, "Creating Databases with SQL," and 15, "Updating Databases with
SQL." Create a new Sub called ProcMonthEnd and then add the
following code:
Sub ProcMonthEnd()
On Error goto ProcMonthEndErr
`
Dim cSQL As String
Dim nResult As Integer
`
wsUpdate.BeginTrans ` mark start of transaction
`
` append totals to transtotals table
cSQL = "INSERT INTO TransTotals SELECT TransTable.CustID,
_SUM(TransTable.Amount) as Amount FROM TransTable
_GROUP BY TransTable.CustID"
db.Execute cSQL
`
` append history records
cSQL = "INSERT INTO TransHistory SELECT * FROM TransTable"
db.Execute cSQL
`
` delete the transaction records
cSQL = "DELETE FROM TransTable"
db.Execute cSQL
`
` ask user to commit transaction
`
nResult = MsgBox("Transaction Completed. Ready to Commit?",
_vbInformation + vbYesNo, "ProcMonthEnd")
If nResult = vbYes Then
wsUpdate.CommitTrans
MsgBox "Transaction Committed"
Else
wsUpdate.Rollback
MsgBox "Transaction Canceled"
End If
`
nErrFlag = 0
GoTo ProcMonthEndExit
`
ProcMonthEndErr:
MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical,
"ProcMonthEnd"
nErrFlag = Err
`
ProcMonthEndExit:
`
End Sub |
This code executes the three SQL statements that
perform the updates and deletes needed for the month-end processing. The routine is
started with a BeginTrans. When the updates are complete, the user is asked to
confirm the transaction. In a production program, you probably wouldn't ask for
transaction confirmation; however, this helps you see how the process is working.
Finally, you need to add the code that puts
everything together. Add the following code to the Form_Load event:
Private Sub Form_Load()
OpenDB
If nErrFlag = 0 Then
ProcMonthEnd
End If
`
If nErrFlag <> 0 Then
MsgBox "Error Reported", vbCritical,
"FormLoad"
End If
Unload Me
End Sub |
This routine calls the OpenDB procedure.
Then, if no error is reported, it calls the ProcMonthEnd procedure. If an error
has occurred during the process, a message is displayed.
Save the form as MULTIUS4.FRM and the
project as MULTIUS4.VBP, and then run the project. All you'll see is a message
that tells you the transaction is complete and asks for your approval. (See Figure 17.10.)
Figure 17.10. Waiting for
approval to commit the transaction.
If you choose No in this message box, Microsoft Jet reverses all the previously completed
database operations between the Rollback and the BeginTrans statements.
You can confirm this by clicking No, using Visdata or Data Manager to load the MULTIUS4.MDB
database, and then inspecting the contents of the tables.
NOTE: An SQL-Visual Basic script called MULTIUS4.SQV
is included on the CD-ROM that accompanies this guide. You can use this script with the
SQL-VB program (see Days 13 and 15) to create a "clean" MULTIUS4.MDB
file. After you run MULTIUS4.VBP once and answer Yes to commit the transaction,
you might want to run the MULTIUS4.SQV script to refresh the database.
Advantages
and Limitations of Transactions
The primary advantage of using transactions in your
Visual Basic programs is that they can greatly increase the integrity of your data. You
should use transactions whenever you are performing database operations that span more
than one table or even operations that affect many records in a single table. A secondary
advantage of using transactions is that they often increase the processing speed of
Microsoft Jet.
As useful as transactions are, there are still a few
limitations. First, some database formats might not support transactions (for example,
Paradox files do not support transactions). You can check for transaction support by
checking the Transactions property of the database. If transactions are not supported,
Microsoft Jet ignores the transaction statements in your code; you do not receive an error
message. Some Dynasets might not support transactions, depending on how they are
constructed. Usually, sets that are the result of SQL JOIN and WHERE
clauses or result sets that contain data from attached tables do not support transactions.
Transaction operations are kept on the local
workstation in a temporary directory (the one pointed to by the TEMP environment
variable). If you run out of available space on the TEMP drive, you'll receive
error 2004. You can trap for this error. The only solution is to make more disk space
available or to reduce the number of database operations between the BeginTrans
and the CommitTrans statements.
Microsoft Jet enables you to nest
transactions up to five levels deep. If you are using external ODBC databases, however,
you cannot nest transactions.
Summary
Today, you learned about the three important
challenges that face every database programmer writing multiuser applications:
- Using database locking schemes
- Using cascading updates and deletes to maintain
database integrity
- Using database transactions to provide
commit/rollback options for major updates to your database
You learned that three levels of locking are
available to Visual Basic programs:
- Database level: You can use the Exclusive property of
the data control or the second parameter of the OpenDatabase method to lock the
entire database. Use this option when you need to perform work that affects multiple
database objects (such as tables, queries, indexes, relations, and so on).
- Table level: You can set the Options property of the
data control to 3 or the third parameter of the OpenRecordset method to dbDenyRead+dbDenyWrite
in order to lock the entire table for your use only. Use this option when you need to
perform work that affects multiple records in a single table (for example, increasing the
sales price on all items in the inventory table).
- Page level: Microsoft Jet automatically performs
page-level locking whenever you use the data control to edit and save a record, or
whenever you use Visual Basic code to perform the Edit/AddNew and Update/CancelUpdate
methods. You can use the LockEdits property of the Recordset to set the page locking to
pessimistic (to perform locking at edit time) or optimistic (to perform locking only at
update time).
You learned how to use Visual Basic to enforce
referential integrity and automatically perform cascading updates or deletes to related
records. You learned that there are times when it is not advisable to establish cascading
deletes (for example, do not use cascading deletes when the base table is a validation
list and the foreign table is a master).
Finally, you learned how to use
database transactions to protect your database during extended, multitable operations. You
learned how to use the BeginTrans, CommitTrans, and Rollback
methods of the workspace object. Finally, you learned some of the advantages and
limitations of transaction processing.
Quiz
- 1. What are the three levels of locking
provided by the Microsoft Jet database engine?
2. Which form of locking would you use when compacting a database?
3. Which form of locking would you use if you needed to update price codes in the
price table of a database?
4. Which property of a Recordset do you set to control whether your application's
data has optimistic or pessimistic page locking?
5. What is the difference between pessimistic and optimistic page locking?
6. Can you use pessimistic locking on an ODBC data source?
7. What happens to data when cascading deletes are used in a relationship?
8. Why would you use transaction management in your applications?
9. What are the limitations of transactions?
10. Do you need to declare a workspace when using transactions?
Exercises
- 1. Write Visual Basic code that exclusively
opens a database (C:\DATA\ABC.MDB) during a Form Load event. Include
error trapping.
2. Build on the code you wrote in the previous exercise to exclusively open the
table Customers in ABC.MDB.
3. Suppose that you are building a new accounts receivable system for your company.
You have saved all tables and data into a single database named C:\DATA\ABC.MDB.
You have discovered that all invoices created must be posted to a history file on a daily
basis. Because this history file is extremely valuable (it is used for collections,
reporting, and so on), you don't want your posting process to destroy any of the data that
it currently contains. Therefore, you decide to use transactions in your code.
Write the Visual Basic code that takes invoice transactions from the temporary holding
table, Transactions, and inserts them into a table named History, which keeps the
cumulative history information.
The History table contains four fields: HistoryItem (counter and primary key), CustID (a
unique identifier for the customer), InvoiceNo (the number of the invoice issued to the
customer), and Amount.
The Transactions table also has four fields: TransNo (counter and primary key), CustID (a
unique identifier for the customer), InvoiceNo (the number of the invoice issued to the
customer), and Amount.
Complete this project by starting a new project and dropping a single command button
(named Post) onto a form. Clicking this button should trigger the posting process.
Include error trapping in your routines. Also, include messages to notify the user that
the transaction posting is complete or that problems have been encountered.
-