unbound forms in multi-user environment

[belovedcej]

I am trying to puzzle this out and would like advice.

Our standard is to use unbound forms and have stored procedures write to the database. Our programmers all use VB.Net and bring over the data in a "short-term memory bank" that all users share.

I wouldn't know how - or even if it's possible - to do that in an Access environment. I am using ade files for the front end, which means there is one file installed on each user's computer.

If the forms are unbound, what will happen if two people happen to work on the same case at the same time? Wouldn't the second person to save overwrite the first person's data?

How do others get around this problem?

 

[ianataqsl]

Concurrence (writeconflict) issues with Access is a BIG subject. If you have a copy of Access Developers Handbook by Litwin-Getz-Gilbert refer to chapter 6, Volume 2 (Enterprise Edition). If you don't, and want to use Access to develop SQL Server applications, I strongly recommend you purchase a copy.

We have adpted the following policy to overcome just the type of problem you refer to.

1) All tables have a ConcurrenceID field (Int type)
2) On all bound forms this field is incremented when Form_BeforeUpdate is fired.
3) If Form_Error fires we capture the error and take control away from Access to enable the user to resolve the write conflict using our purpose written conflict viewer.
4) When using Unbound forms, the ConcurrenceID of the recordset is retrieved prior to running the save operation and compared with its value on load. If different, the record has been updated since being loaded and our purpose written conflict viewer is launched to enable the user to choose which fields to keep/overwrite.
5) As a security measure we log all conflict instances to provide a complete audit trail.

Sorry I can't give you a 1 line answer, but this is a big subject and requires a considerable amount of coding to provide bullet proof multi-user applications with Access as a front end to SQL Server.

 

[danvlas]

Well...

When populating the form with the initial data, store the record in a recordset or array or whatever you choose.

When trying to write data back to the server, begin a transaction, load a new recordset with the current data on the server, compare the two recordsets and see if there are changes (made by someone else). If yes, rollback the transaction and give the message to the user. If not, write your data and commit the transaction.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[KevCon]

danvlas, that is an interesting approach and is what I've been thinking about. any chance you could paste an example, please?
my biggest quandry is, even with adUseCLient, and without me specifically coding to Update, Access still updates the record on SQL when I Close a form. it does not Update however when I change values in text boxes, but as soon as I exit the form, even close out of the ADP, it updates the SQL record.
so with Optimistic locking, isn't that rollback?

thanks for your input.

 

[danvlas]

It is important that the form and the controls are UNBOUND
My approach is to name the controls exactly after names of the resulting recordset from the stored procedure.



'General section of form's module
Dim oRst as New ADODB.Recordset

Function fGetData(oConn As ADODB.Connection, ID as Long) as ADODB.Recordset
Set fGetData = oConn.Execute("sp_Name(" & ID & ")")
End Function

'1. Get the recorset to populate the form , place it in any event procedure you may find appropriate

Set oRst = fGetData(CurrentProject.Connection, Me("IDControl"))

'Assuming the same names exist in the recordset and in the controls on the form

with oRst
For each fld in oRst.Fields
me(fld.Name)= iif(.EOF, Null, fld.Value)
Next
End with

Keep the recordset 'alive' until making changes

When trying to save the changes:

Sub sMakeChanges()
Dim oNewRst As New ADODB.Recordset
Dim oConn As New ADODB.Connection
Dim fld As ADODB.Field
Dim blnTransStarted As Boolean
On Error GoTo Err_Handler

Set oConn = CurrentProject.Connection
oConn.BeginTrans
blnTransStarted = True

'From now on, if SQL Server has its default settings, nothing should go wrong (isolation level Read Commited (Read_Commited_Snapshot on)

Set oNewRst = fGetData(oConn, Me("IDControl"))
'This locks the records until the end of transaction

With oNewRst
For Each fld In .Fields
If Nz(fld,"")<>Nz(oRst.Fields(fld.Name),"") Then
'Data has been changed
Err.Raise 10000,"Data changed by another user", "The data has changed while you were working so slowly"
'This will pass execution to error handler
End If
Next
End With

'If the code got here, no change has been made, so make your changes

conn.Execute("sp_That_Makes_The _Changes")

If blnTransStarted Then
conn.CommitTrans
blnTransStarted = False
End If

'From now on, your changes are permanent and visible to the others. Locks are gone

Exit_Here:
Set oConn=Nothing
Exit Sub


Err_Handler:
If blnTransStarted Then
oConn.RollbackTrans
'Locks are gone
blnTransStarted = False
End If

MsgBox "An error has occured: " & Err.Number & ", " & Err.Description

Resume Exit_Here

End Sub


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[danvlas]

One more thing...

Never leave a transaction at the hand of the user (messages, dialogs, whatever) before it ends. You may have everybody else locked out of the database until the person comes back from the toilet...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[KevCon]

mr. danvlas, that is a very impressive solution.
yes, all of my forms are unbound, and as of yet there's no problem instatiating an RS on Open and Set me.recordset (etc...), it works great, I'm just thinking of concurrency in the future (and taking the load off the server)and I've been reading about how to do this more correctly.
I have a few questions re: your ex. but I think I need to read a bit more about executing SQL transactions as such.

likewise, mr. ianataqsl, your model summary now has me thinking about expanding a similar strategy I've employed and applying it application-wide to include record strategies, it's just nice to see that you have a clean formal way of employing this...

Bottom line to both of these powerful strategies is I will be making a trip to Border's to have a look at the latest "Access Bible" and other suggested literature hopefully covering ADP's and Access 2003.

thanks again