record locks or page lock? (1 Viewer)

Whats the difference between page lock and record lock? Is page lock used to lock forms? ie, if a certain user using this form, another user is not allowed to open the same form?

Another question is on exclusive use. For users with exclusive priviledge, does this mean able to bypass record locking even if some other user is using?
Or does this mean that someone with exclusive priviledge is using, all other normal users will not be able to save data?

Just implement user level security...

when a user is editing (changed from edited) a record, you can "lock" the record to prevent another user accessing while you are using it. the problem is that this can cause real usage problems, (eg a user leaves his screen for an extended period with a locked record) so you need a technique thats locks records only for the minimum time necessary.

i think access actually applies a lock to a "page" object - a page will include the active record and contiguous records that are retrieved into memory at the same time.

the important thing is that even if you use "no record locking", Access still applies record locking, but called optimistic record locking. what it does is before it writes the change, it re-reads the record, and if it has changed, gives you the "another user has altered the record" message.

Unless therei s a special need for something stronger this default optimistic strategy is generally perfectly acceptable

gemma-the-husky said:

when a user is edited a record, you can "lock" the record to prevent another user accessing while you are using it. the problem is that this can cause real usage problems, (eg a user leaves his screen for an extended period with a locked record) so you need a technique thats locks records only for the minimum time necessary.

i think access actually applies a lock to a "page" object - a page will include the active record and contiguous records that are retrieved into memory at the same time.

the important thing is that even if you use "no record locking", Access still applies record locking, but called optimistic record locking. what it does is before it writes the change, it re-reads the record, and if it has changed, gives you the "another user has altered the record" message.

Unless therei s a special need for something stronger this default optimistic strategy is generally perfectly acceptable

Click to expand...

I have a form with an autonumber control having default value of dmax+1, if 2 user entering the form, they would obtain the same autonumber, this would be a problem. Would I be able to lock the form if there is already 1 user using it? ie; without the 1st user closing the form, the 2nd user upon trying to access the same form would be prompted that there is another user logged on. Is there this type of record locking function?

if its really an autonumber. then no they wouldnt get the same autonumber.

if you are allocating the number, by doing a dmax, then the time to do this is in the beforeupdate event for the form (record), as there will only be a few millisecs when the situation you describe could occur. (ie two users doing a beforeupdate simulataneously)

If this is still an issue. You can avoid even this possibility, by storing the next value in a separate table, and reading the value from this table, temporarily LOCKING the lookup table as you read it.

gemma-the-husky said:

if you are allocating the number, by doing a dmax, then the time to do this is in the beforeupdate event for the form (record), as there will only be a few millisecs when the situation you describe could occur. (ie two users doing a beforeupdate simulataneously)

Click to expand...

Had a very similar problem with a site. For some weird reason the auditors wanted the recordID and the record number from Access to match. I had put in an Autonumber fld but within a few days was instructed to do what they wanted - the first delete got the nos. out of synch.

I tested the following code and it worked perfectly. It cycles around the SaveRecord routine until Error 3022 goes, then it save the record using the next highest number.

I got it to save the record after certain info was entered. This minimized the chance of an accidental/wrong entry. lngVisitID is DIMmed in the module, along with the sub GetVisitID().

Error 3022 in the code below is a DAO error " 'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. etc". If 2 users are in simultaneously, this routine cycles around for both until 1 just beats the other to a record save. The 2nd user then gets the next highest no. and so can save unless another user beats them to it etc.

Private Sub cboAgentID2_AfterUpdate()
On Error GoTo Err_cboAgentID2_AfterUpdate

If Forms!frmVisits!fldVisitID = 0 Then
GetVisitID
Forms!frmVisits!fldVisitID = lngVisitID
End If

SaveRecord:
DoCmd.RunCommand acCmdSaveRecord

Exit_cboAgentID2_AfterUpdate:
Exit Sub

Err_cboAgentID2_AfterUpdate:

If Err.Number = 3022 Then
Forms!frmVisits!fldVisitID = 0
GetVisitID
Forms!frmVisits!fldVisitID = lngVisitID
GoTo SaveRecord
Else
MsgBox Err.Description
Resume Exit_cboAgentID2_AfterUpdate
End If

End Sub


Public Sub GetVisitID()
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblVisitDetails", dbOpenDynaset)
rst.MoveLast
lngVisitID = rst!fldVisitID + 1
rst.Close
Set rst = Nothing
End Sub

gemma-the-husky said:

if its really an autonumber. then no they wouldnt get the same autonumber.

if you are allocating the number, by doing a dmax, then the time to do this is in the beforeupdate event for the form (record), as there will only be a few millisecs when the situation you describe could occur. (ie two users doing a beforeupdate simulataneously)

If this is still an issue. You can avoid even this possibility, by storing the next value in a separate table, and reading the value from this table, temporarily LOCKING the lookup table as you read it.

Click to expand...

Yes the number is allocated(not autonumber), using dmax+1 in the default value property of a textbox control on the form. Which on form load, I'll have the dmax+1 retrieved and there before other fields are entered.

I thought the form doesnt update till it closes or goes to next record? Then i should have a couple of minutes before the user completes all info and saves the record, which is a couple of minutes time(to complete data entry) which another user can enter the same form and obtain the same dmax+1 number. Correct?