Record Locking, keeping users from viewing same record

[Spyrios]

Currently I have a front end/ back end over a network that is on about 10 concurrent users. They are using it to call customers, which brings up my problem.

I can't have two reps on the same record at the same time. Otherwise, they might both call the customer at the same time, or the first user be hanging up with the customer while the second user picks up the phone to call them. Probably not a good thing.

So what I did is this, when the user moves to a record, vb code 'stamps', or updates that record with their name and date. This is on the OnCurrent event. When they push the button to move to the next record, the form is requeried to limit out all of the records that have a name and a date. Actually a little more complicated, but that's the gist of it.

The problem is,,,, that the query runs faster than the vb code to update it. Compound that with what I'm guessing is slow network problems, and I have a situation where the first user opens the record and 'stamps' it, but the second user queries and sees that no one is on it before the first user's data is actually updated to the backend. Then I have two users on the same record, which is causing a whole host of problems. So I need the record to be locked even more than pessimistic, not only to keep them from editing the same record, but even from viewing the same record. Any suggestions??? Please?!?


Michael

 

[willir]

What a great question!

There are several approaches. I recall that BillPowers and JeremyNYC have provided some excellent examples in the past. Look up tip masters under VB coding...

http://www.tek-tips.com/tipmasters.cfm?spid=705

You can also find some excellent books on this subject.

Before we start... Make sure the server is not caching writes to disk. Updates need to be written to disk when they occur.

One approach is for the form not to retrieve any records and not to reference a table. Have the back end code scrolls through a record set that retrieves only a few key fields - ie, primary key or customer name. Then the record is displayed to the form using the primary key. Very effecient in terms of speed and has a very minimal impact on the LAN, but this apporachs takes a lot more work.

(Note: Access is not great in a multiuser, LAN environment, and really sucks in a WAN environment. This is because it retrieves an entire recordset to the desktop. So if you have 10 users and the record set had 10,000 records, each having 2 kbytes may be a lot of data flooding the network.)

You can also control record locking at the form level, select clause in the VB code. ADODB coding seems to be better suited to this than DAO coding - more options for record locking. Read up on record locking in the MS Access help. Obviously, you want to not lock any records while scrolling through, and then have an action button that brings the selected record in edit mode. This gets a little tricky if you use combo boxes to select a record by dont allow updates to the form.

Lastly, you do have the ability to toggle between view and edit mode. The following is a snippet of code that uses a toggle button on the form to flag record open for edits or not. Allow_edit is the name of the toggle button as defaults to false when loading the form.

Private Sub set_security(booUpdate)

Dim booField As Boolean, booMethod As Boolean

booMethod = booUpdate

If booUpdate Then
Me.allow_edits.Caption = "Allow Edits"
booField = False
Else
Me.allow_edits.Caption = "No Edits"
booField = True
End If

Me.edit_flag = booUpdate
Me.AllowAdditions = booMethod
Me.AllowDeletions = booMethod

Me.Field1.Locked = booField
Me.Field2.Locked = booField
Me.Field3.Locked = booField
etc.

I like your idea of stamping the record. You may want to use the requery method to update the field when required. It may help...

Me.Field1.Requery

Richard