here is an example of multi-user database page locking and the error handling needed to make it work. note that the database routines are pulled from my cyberstyle column and can be found in the module db.bas. please refer to the cyberstyle section for an explanation of how they work. also note that i have not included any code that makes use of the data control supplied with vb3. it has been my experience that the data control is of little value in the real world and that true power and control only comes with using the data access objects in the vb3 professional edition.
const mb_retrycancel = 5
const mb_yesno = 4
const idcancel = 2
const idretry = 4
const idno = 7
const db_denywrite = &h1
const db_denyread = &h2
const err_reserved = 3000
const err_cant_open_db = 3051
const err_cant_lock_table = 3262
const err_data_changed = 3197
const err_record_locked = 3260
const rerr_exclusivedbconflict = "-8194"
sub command1_click ()
dim db as database
dim ads() as dynaset
dim ds as dynaset 'used only to keep code simple
dim ret as integer
dim bunlocked as integer
ret% = db_connect("biblio.mdb")
ret% = db_query2dyna("authors", ads())
'this next line is not needed but makes the code easier to read without the ads(0) stuff.
set ds = ads(0).clone()
do until ds.eof = true
'attempt to access records, checking for possible page locking conflicts
bunlocked = false
'disable any previous error handler and instead, just resume next
on error resume next
while not bunlocked
err = 0
ds.edit
select case err
case 0
'no error happened...ok
bunlocked = true
case err_data_changed
ret% = msgbox("record has been updated. overwrite?", mb_retrycancel)
case err_record_locked
ret% = msgbox("record in use by another user. try again?", mb_retrycancel)
case else
msgbox "unexpected error" & str$(err) & " editing record."
exit sub
end select
select case ret%
case idcancel
'cancel means quit the functon
exit sub
case idretry
'retry means try again in the loop. note that the retry for the err_data_changed
' will always work the second time through since the edit method will
' not generate this error again for this series of events.
bunlocked = false
end select
wend
'disable error trapping or place on error statements
'pointing to a new error handler here
on error goto 0
ds("author") = ds("author")
' with optimistic locking you would check locking on update vs. edit
ds.update
ds.movenext
loop
ret% = db_closedyna(ds)
ret% = db_closedatabase()
