jannea
asked on
Couldn't update currently locked by...
Hello!
I have a Access 97 database in our network. Some pepole work via the Citrix MetaFrame sever (TSRV). And some just run my exe on the fileserver.
Sometimes they get the errormsg:
Couldn't update currently locked by user "admin" om machine TSRV.
The error occurs when a datacontrol trying to update after a addnew.
How come? The traffic is not always heavy when it happens. And they add records, they don't edit a existent one.
/Janne, Sweden
I have a Access 97 database in our network. Some pepole work via the Citrix MetaFrame sever (TSRV). And some just run my exe on the fileserver.
Sometimes they get the errormsg:
Couldn't update currently locked by user "admin" om machine TSRV.
The error occurs when a datacontrol trying to update after a addnew.
How come? The traffic is not always heavy when it happens. And they add records, they don't edit a existent one.
/Janne, Sweden
ASKER
tward,
It's not opend in exclusive mode.
<Also are you updating the database in anyway via Access while people are accessing it via VB?
Yes.
It's not opend in exclusive mode.
<Also are you updating the database in anyway via Access while people are accessing it via VB?
Yes.
Through Tools -> Options -> Advanced -> Default Share Mode you can change it to shared mode.
But I do believe if you open it in Access and open a Table that it may still lock it exclusively....
But I do believe if you open it in Access and open a Table that it may still lock it exclusively....
ASKER
I access the table by a datacontrol as a dynaset.
Posting the code where the error occurs:
Private Sub Data1_Validate(Index As Integer, Action As Integer, Save As Integer)
Dim v, r&
On Error GoTo Data1_Validate
If Action = 6 Then Save = True
With Data1(Index).Recordset
r& = .AbsolutePosition + 1
Select Case Action
Case vbDataActionMoveFirst
Case vbDataActionMovePrevious
Case vbDataActionMoveNext
Case vbDataActionMoveLast
Case vbDataActionAddNew
Case vbDataActionUpdate
If Index = 1 Then 'Utlägg
.Fields("Utlägg") = True
Else 'Tid
.Fields("AnvändarID") = sAnvID$
End If
.Fields("Transaktionskod") = Index
'Hämta pris för det tycks inte sparas automatiskt om man ej skrivit i cellen.
If r& = 0 Then r& = 1
If r& >= 1 Then
Call SS(Index).GetText(3, r&, v)
.Fields("Kund") = v
Call SS(Index).GetText(4, r&, v)
.Fields("Projekt") = v
Call SS(Index).GetText(5, r&, v)
.Fields("Kod") = v
Call SS(Index).GetText(8, r&, v)
.Fields("APris") = Convdbl(v)
.Fields("ÄlstaRäkår") = True
Data1(Index).UpdateRecord
'If r& = SS(0).MaxRows - 1 And Index = 0 And SS(0).ActiveCol = 7 Then '
If Index = 0 And SS(0).ActiveCol = 7 Then
Call Summera(Index, (SS(Index).ActiveRow))
End If
End If
Case vbDataActionDelete
Case vbDataActionFind
Case vbDataActionBookmark
Case vbDataActionClose
End Select
End With
If sAction <> "End" Then Call FixaTal(Index, r&)
Exit Sub
Data1_Validate:
If Err = 3020 Then
If Data1(Index).Recordset.Abs olutePosit ion > 0 Then
Data1(Index).Recordset.Edi t
Else: SS(Index).DAutoSave = True: Exit Sub: End If
Resume
End If
'sFel = "Tidregistrering"
'FM (sFel)
'If sFel = "Resume" Then Resume
MsgBox Error, 16, "Data1_Validate"
Exit Sub
Resume
End Sub
Posting the code where the error occurs:
Private Sub Data1_Validate(Index As Integer, Action As Integer, Save As Integer)
Dim v, r&
On Error GoTo Data1_Validate
If Action = 6 Then Save = True
With Data1(Index).Recordset
r& = .AbsolutePosition + 1
Select Case Action
Case vbDataActionMoveFirst
Case vbDataActionMovePrevious
Case vbDataActionMoveNext
Case vbDataActionMoveLast
Case vbDataActionAddNew
Case vbDataActionUpdate
If Index = 1 Then 'Utlägg
.Fields("Utlägg") = True
Else 'Tid
.Fields("AnvändarID") = sAnvID$
End If
.Fields("Transaktionskod")
'Hämta pris för det tycks inte sparas automatiskt om man ej skrivit i cellen.
If r& = 0 Then r& = 1
If r& >= 1 Then
Call SS(Index).GetText(3, r&, v)
.Fields("Kund") = v
Call SS(Index).GetText(4, r&, v)
.Fields("Projekt") = v
Call SS(Index).GetText(5, r&, v)
.Fields("Kod") = v
Call SS(Index).GetText(8, r&, v)
.Fields("APris") = Convdbl(v)
.Fields("ÄlstaRäkår") = True
Data1(Index).UpdateRecord
'If r& = SS(0).MaxRows - 1 And Index = 0 And SS(0).ActiveCol = 7 Then '
If Index = 0 And SS(0).ActiveCol = 7 Then
Call Summera(Index, (SS(Index).ActiveRow))
End If
End If
Case vbDataActionDelete
Case vbDataActionFind
Case vbDataActionBookmark
Case vbDataActionClose
End Select
End With
If sAction <> "End" Then Call FixaTal(Index, r&)
Exit Sub
Data1_Validate:
If Err = 3020 Then
If Data1(Index).Recordset.Abs
Data1(Index).Recordset.Edi
Else: SS(Index).DAutoSave = True: Exit Sub: End If
Resume
End If
'sFel = "Tidregistrering"
'FM (sFel)
'If sFel = "Resume" Then Resume
MsgBox Error, 16, "Data1_Validate"
Exit Sub
Resume
End Sub
Does your table in which you enter new records have an auto-number as the key? if so, then there might be a case that someone enters a new record with a certain key and another user enters a new record in the same time, and they both try to insert with the same key. The first one will succeed, the second one will get an error that he's trying to update something which exists already.
jannea:
Just some thoughts:
I would suggest that you post a question in the Access area directing people to this question. I think you'll find many experts there who have solved record-locking problems with Access before.
Access handles record-locking in strange ways . . .Having several users in a table at the same time isn't a problem most of the time.
First, Access97 doesn't even lock records individually, but locks down 2K pages of memory. Sometimes this results in a lock that covers more than one record. Two users adding a record to the table at the same time might result in Access trying to place two locks on the same page of memory.
A workaround for your problem above might be to add some error-handling that tries to add the record again after a second or two has passed. Hopefully this way the record conflict will have passed by the time you try to add the record again.
Wes
Just some thoughts:
I would suggest that you post a question in the Access area directing people to this question. I think you'll find many experts there who have solved record-locking problems with Access before.
Access handles record-locking in strange ways . . .Having several users in a table at the same time isn't a problem most of the time.
First, Access97 doesn't even lock records individually, but locks down 2K pages of memory. Sometimes this results in a lock that covers more than one record. Two users adding a record to the table at the same time might result in Access trying to place two locks on the same page of memory.
A workaround for your problem above might be to add some error-handling that tries to add the record again after a second or two has passed. Hopefully this way the record conflict will have passed by the time you try to add the record again.
Wes
ASKER
ViniT,
No there isen't any auto-number or primarykey in the table.
wesleystewart,
OK, but I guess it have something to do with Citrix. Coz when this happens no one in the network can add records. Sometimes it goes days without this error. An about the Jet pagelocking, does this affect when just adding new records?
No there isen't any auto-number or primarykey in the table.
wesleystewart,
OK, but I guess it have something to do with Citrix. Coz when this happens no one in the network can add records. Sometimes it goes days without this error. An about the Jet pagelocking, does this affect when just adding new records?
Jet locks a page of memory whenever a record is maintained, added, deleted, or changed in any way.
Wes
Wes
Wesley is on the right track, it's the page locking that's the problem.
In the past, Access has always locked on pages, which poses a major problem when many users are adding records. Access 1.1 and 2.x always saved to the last page of a table causing a big concurrency problem. This was fixed with Access 95 where new records are now spread across the last few pages of the table. While this helped, it didn't really fix the problem. Access 2000 (JET 4.0) has now added record level locking, but it's so new the jury is still out.
Two things you can do:
1. Make sure your using optimistic locking (what Access calls "no locks") instead of pessimistic locking. Pessimistic locking locks the page as show as you do a .Edit or .AddNew and doesn't release the lock untill .Update.
Optimistic locking only locks the page when you perform the .Update. The risk you run is that someone else has modified the page since you pulled it into memory (you'll get an error messge). For adding, this is rarely significant.
2. As Wesely suggested, trap the error and do a timeout. Here's some Access code from one of my own routines:
GetRecordKeyError:
'Table locked by another user
If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER Then
intLockCount = intLockCount + 1
If intLockCount > 5 Then
GetRecordKey = Null
Resume GetRecordKeyExit
Else
DoEvents
DBEngine.Idle DB_FREELOCKS
lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
For lngX = 1 To lngWait
DoEvents
Next lngX
Resume
End If
Else
UnexpectedError ModuleName, Routine, Version, Err, Error$
GetRecordKey = Null
Resume GetRecordKeyExit
End If
This is old ver 2.0 code BTW, but it still shows what were talking about. If you encounter an error, back off a random time and try the operation again.
HTH,
JimD.
In the past, Access has always locked on pages, which poses a major problem when many users are adding records. Access 1.1 and 2.x always saved to the last page of a table causing a big concurrency problem. This was fixed with Access 95 where new records are now spread across the last few pages of the table. While this helped, it didn't really fix the problem. Access 2000 (JET 4.0) has now added record level locking, but it's so new the jury is still out.
Two things you can do:
1. Make sure your using optimistic locking (what Access calls "no locks") instead of pessimistic locking. Pessimistic locking locks the page as show as you do a .Edit or .AddNew and doesn't release the lock untill .Update.
Optimistic locking only locks the page when you perform the .Update. The risk you run is that someone else has modified the page since you pulled it into memory (you'll get an error messge). For adding, this is rarely significant.
2. As Wesely suggested, trap the error and do a timeout. Here's some Access code from one of my own routines:
GetRecordKeyError:
'Table locked by another user
If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER Then
intLockCount = intLockCount + 1
If intLockCount > 5 Then
GetRecordKey = Null
Resume GetRecordKeyExit
Else
DoEvents
DBEngine.Idle DB_FREELOCKS
lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
For lngX = 1 To lngWait
DoEvents
Next lngX
Resume
End If
Else
UnexpectedError ModuleName, Routine, Version, Err, Error$
GetRecordKey = Null
Resume GetRecordKeyExit
End If
This is old ver 2.0 code BTW, but it still shows what were talking about. If you encounter an error, back off a random time and try the operation again.
HTH,
JimD.
Do note that I kept refering to Access in the last post, but it is still accurate in terms of what your doing (your still using JET).
Here's the breakout between Access/JET versions:
1.1 - JET 1.1
2.x - JET 2.5
A95 - JET 3.0
A97 - JET 3.5
A2000 - JET 4.0
I re-read my post after posting and realized from a VB point that what I was talking about may not have been clear.
JimD.
Here's the breakout between Access/JET versions:
1.1 - JET 1.1
2.x - JET 2.5
A95 - JET 3.0
A97 - JET 3.5
A2000 - JET 4.0
I re-read my post after posting and realized from a VB point that what I was talking about may not have been clear.
JimD.
#Locked
This value can be caused by any of the following situations:
The record has been locked by another user and Microsoft Access cannot read the data.
There are two or more instances of Microsoft Access running on the same computer. Microsoft Access treats each open copy of the database as a separate user.
Your code has opened a recordset and has a lock on a record.
It is important to note that Microsoft Access uses page locking instead of record locking. Microsoft Access stores records in groups of 2048 bytes, called pages. A single page may hold only one record, or it may hold many records. If each record only requires 200 bytes, then up to 10 records may be stored on a page. When Microsoft Access places a lock on a record, it locks that record's page. If there are 10 records on the page, then all 10 records on that page are locked.
Also:
Step-by-Step Example for Level 3: Page Locking
This third example shows the Page Locking that is automatically built in to the database engine. That is, if you don't provide Database Locking or Dynaset or Table Locking, Page Locking is enforced automatically.
There are two different types of Page Locking when the Edit mode is invoked, Optimistic or Pessimistic. Pessimistic Locking is the default. In pessimistic locking, Visual Basic locks the page containing a record as soon as you use the Edit method, and it unlocks the page when you use Update or Rollback. Use Optimistic Locking when you want to lock a page only at the time you Update the data. You do this by setting the record's LockEdits property.
Page locking is enforced on a per page basis. A "page" is based on the number of records that can fit within one 2048 byte (or 2K) block of memory.
Since entire pages are locked, and a given page may contain more then one record, users need not be on the exact same record to cause a locking conflict. For example, if user A locks a record at the very beginning of a page, and user B attempts to do an Edit of another record that is also on that page, user B will receive a locking error.
The same locking scheme applies to the index pages. When the Seek method is used or indexes are being rebuilt, the index pages are locked on a 2K page basis. This can also cause locking errors, which the programmer should handle appropriately.
Start a new project in Visual Basic. Form1 is created by default.
Add a command button (Command1) and a list box (List1) to Form1.
Add the following code to the Command1 click event procedure:
Sub Command1_Click ()
Dim db As database
Dim ds As dynaset
Dim ret As Integer, fSuccess As Integer
Set db = OpenDatabase("BIBLIO.MDB")
Set ds = db.CreateDynaset("Authors" )
Do Until ds.EOF = True
' Attempt to access records, checking for possible page locking conflicts
fSuccess = False
' Disable any previous error handler
' and instead, just resume next
On Error Resume Next
While Not fSuccess
Err = 0
ds.Edit
If Err Then
If Err = ERR_DATA_CHANGED Then
ret = MsgBox("Record has been updated. Overwrite?", MB_RETRYCANCEL)
If ret = IDNO Then fSuccess = True
ElseIf Err = ERR_RECORD_LOCKED Then
ret = MsgBox("Record in use by another user.", MB_RETRYCANCEL)
If ret = IDCANCEL Then Exit Sub
Else
MsgBox "Unexpected error" & Str$(Err) & " editing record."
Exit Sub
End If
Else
fSuccess = True
End If
Wend
On Error GoTo 0 ' disable error trapping OR place On Error statements
' pointing to a new error handler here
ds("Author") = ds("Author")
' With Optimistic locking you would check locking on Update vs. Edit
ds.Update
ds.MoveNext
Loop
ds.Close
db.Close
End Sub
From the Run menu, choose Start or press the F5 key to run the program. Click the Command1 button. The records that are included in the results set that totals up to a 2K page will be locked from the other users. They will receive an error message if they try to open the any record in your results set.
For Optimistic locking, you would want to check for locking errors on the Update method, rather then the Edit methods.
Multi-user Access with the Data Control
Using the data control is essential the same as working with the Database and Dynaset objects. The Data Control can use any of the three locking levels described above, as follows:
Database locking: Set the Exclusive property to true.
Table locking: Set the Options property to the desired value. These values are the same as those passed as the second parameter to CreateDynaset.
Page locking: This is handled in exactly the same way as a Dynaset object.
NOTE: If you set the DatabaseName and/or RecordSource properties of a Data Control at design time, the Data Control will automatically attempt to perform the equivalent of an OpenDatabase and CreateDynaset when the form containing the data control is first loaded.
If an error occurs when the Data Control attempts to automatically open the Database or Dynaset, the Data Control will fire its Error event and pass in the appropriate run-time error value. Since no Visual Basic code is executing at this time, you will need to handle any possible locking conflicts in the Error event rather then using On Error.
Error Handling
There are two main run-time errors to trap for with page level locking. The first is error 3260 "Couldn't Update; currently locked by user "x" on machine "y".", which indicates that another user has the page you want to modify locked.
The second, error 3197 "Data has changed; operation stopped." indicates that the underlying data in the database has been changed by another user since the last time you retrieved that page from the physical database. This indicates that someone has modified the record (or some field of the record if you are using a Dynaset or Snapshot) that you are attempting to modify.
For example, if user A creates a Dynaset on the table Authors, and pulls in the first page (2k) of records from the database. User B now creates an identical Dynaset, retrieving the same 2k of records. If User A now updates the first record in the Dynaset, user B will not immediately see this change, since User B has already retrieved the first page of records and will not do so again unless he or she refreshes (recreates) the Dynaset.
If user B now attempts to modify the first record of the Dynaset, he or she will receive the "Data has changed; operation stopped" error, warning them that they are about to overwrite new data which they have never retrieved from the database. If you re-execute the operation that caused the "Data has changed; operation stopped" after receiving the error once, the data will be overwritten without the error being generated a second time.
NOTE: Although the AddNew method does not modify existing records, the page where the new record is to be added is still locked when the Update statement is executed to prevent two users from trying to add data at the same location in the database. This means that you should perform the same checks for locking conflicts with AddNew that you use for the Edit method.
Notes on Snapshots
Error 3197 "Data has changed; operation stopped." can occur for Snapshots which have memo fields. Since memo fields are usually quite large, the Access engine does not pull the entire contents of a memo field into the SnapShot at the time it is created; instead, a reference to the memo field in the database is stored in the SnapShot.
If the data in the memo field is changed by another user between the time a Snapshot is first populated (meaning that you access a given record, using the Move or Find methods or visit all records, using the sn.MoveLast method) and the time that record is revisited or made the current record again, the database engine will signal that your data is out of date by invoking run- time error 3197.
NOTE ON ODBC
When using external data engines such as Microsoft SQL Server, Sybase, or Oracle through ODBC, the locking methodology is the responsibility of the remote database engine. Visual Basic acts as a front-end to these database servers and does not control the data sharing aspects of the back-end database engines. In some cases, you can control how the remote server locks data by using backend-specific SQL statements or administrative options.
Some implications of the Access engine deferring to back-end locking schemes:
The setting of LockEdits has no effect on ODBC databases.
The Exclusive parameter of the OpenDatabase method is ignored.
The technique shown in the section "Step-by-Step Example for Level 2: Table or Dynaset Locking", will fail on ODBC databases, with the error "ODBC--Can't lock all records."
It is not recommended that the programmer attempt to lock the entire table or the entire database, using backend native syntax, as this can be detrimental to throughput in a high-volume transaction environment.
Incorporating sophisticated error handling to react correctly when locking contention occurs is the only reliable way to allow the backend database engine to operate its native locking scheme for maximum throughput.
Example Showing How to Call FreeLocks After Locking Error Occurs
In certain circumstances, particularly in an intense multi-user contention environment, the local access engine may get out of synchronization with the host (the VB program). When that occurs, the FreeLocks statement provides a way to allow the engine to 'catch up' and clear any leftover locks. The FreeLocks method tells the engine to perform its default idle processing, including freeing locks that are no longer in use but haven't timed out yet.
Below is an example showing how to call the FreeLocks method after a locking error occurs in a multi-user system. The following code is a sample of an error trapping routine that checks to see if a record is locked in a multi-user system:
Sub EditRecord(ds as dynaset)
On Error Goto ErrLock
ds.lockedits = True ' Return lock errors on Edit call, not the update.
Retry:
ds.Edit
ds.fields(0).value = "Something"
ds.fields(0).value = "Else"
ds.update
exit sub
ErrLock:
ds.bookmark = ds.bookmark ' Cancel the update.
FreeLocks
tm = timer
' Wait 3 seconds:
while timer - tm < 3
doevents
wend
resume Retry
End Sub
This value can be caused by any of the following situations:
The record has been locked by another user and Microsoft Access cannot read the data.
There are two or more instances of Microsoft Access running on the same computer. Microsoft Access treats each open copy of the database as a separate user.
Your code has opened a recordset and has a lock on a record.
It is important to note that Microsoft Access uses page locking instead of record locking. Microsoft Access stores records in groups of 2048 bytes, called pages. A single page may hold only one record, or it may hold many records. If each record only requires 200 bytes, then up to 10 records may be stored on a page. When Microsoft Access places a lock on a record, it locks that record's page. If there are 10 records on the page, then all 10 records on that page are locked.
Also:
Step-by-Step Example for Level 3: Page Locking
This third example shows the Page Locking that is automatically built in to the database engine. That is, if you don't provide Database Locking or Dynaset or Table Locking, Page Locking is enforced automatically.
There are two different types of Page Locking when the Edit mode is invoked, Optimistic or Pessimistic. Pessimistic Locking is the default. In pessimistic locking, Visual Basic locks the page containing a record as soon as you use the Edit method, and it unlocks the page when you use Update or Rollback. Use Optimistic Locking when you want to lock a page only at the time you Update the data. You do this by setting the record's LockEdits property.
Page locking is enforced on a per page basis. A "page" is based on the number of records that can fit within one 2048 byte (or 2K) block of memory.
Since entire pages are locked, and a given page may contain more then one record, users need not be on the exact same record to cause a locking conflict. For example, if user A locks a record at the very beginning of a page, and user B attempts to do an Edit of another record that is also on that page, user B will receive a locking error.
The same locking scheme applies to the index pages. When the Seek method is used or indexes are being rebuilt, the index pages are locked on a 2K page basis. This can also cause locking errors, which the programmer should handle appropriately.
Start a new project in Visual Basic. Form1 is created by default.
Add a command button (Command1) and a list box (List1) to Form1.
Add the following code to the Command1 click event procedure:
Sub Command1_Click ()
Dim db As database
Dim ds As dynaset
Dim ret As Integer, fSuccess As Integer
Set db = OpenDatabase("BIBLIO.MDB")
Set ds = db.CreateDynaset("Authors"
Do Until ds.EOF = True
' Attempt to access records, checking for possible page locking conflicts
fSuccess = False
' Disable any previous error handler
' and instead, just resume next
On Error Resume Next
While Not fSuccess
Err = 0
ds.Edit
If Err Then
If Err = ERR_DATA_CHANGED Then
ret = MsgBox("Record has been updated. Overwrite?", MB_RETRYCANCEL)
If ret = IDNO Then fSuccess = True
ElseIf Err = ERR_RECORD_LOCKED Then
ret = MsgBox("Record in use by another user.", MB_RETRYCANCEL)
If ret = IDCANCEL Then Exit Sub
Else
MsgBox "Unexpected error" & Str$(Err) & " editing record."
Exit Sub
End If
Else
fSuccess = True
End If
Wend
On Error GoTo 0 ' disable error trapping OR place On Error statements
' pointing to a new error handler here
ds("Author") = ds("Author")
' With Optimistic locking you would check locking on Update vs. Edit
ds.Update
ds.MoveNext
Loop
ds.Close
db.Close
End Sub
From the Run menu, choose Start or press the F5 key to run the program. Click the Command1 button. The records that are included in the results set that totals up to a 2K page will be locked from the other users. They will receive an error message if they try to open the any record in your results set.
For Optimistic locking, you would want to check for locking errors on the Update method, rather then the Edit methods.
Multi-user Access with the Data Control
Using the data control is essential the same as working with the Database and Dynaset objects. The Data Control can use any of the three locking levels described above, as follows:
Database locking: Set the Exclusive property to true.
Table locking: Set the Options property to the desired value. These values are the same as those passed as the second parameter to CreateDynaset.
Page locking: This is handled in exactly the same way as a Dynaset object.
NOTE: If you set the DatabaseName and/or RecordSource properties of a Data Control at design time, the Data Control will automatically attempt to perform the equivalent of an OpenDatabase and CreateDynaset when the form containing the data control is first loaded.
If an error occurs when the Data Control attempts to automatically open the Database or Dynaset, the Data Control will fire its Error event and pass in the appropriate run-time error value. Since no Visual Basic code is executing at this time, you will need to handle any possible locking conflicts in the Error event rather then using On Error.
Error Handling
There are two main run-time errors to trap for with page level locking. The first is error 3260 "Couldn't Update; currently locked by user "x" on machine "y".", which indicates that another user has the page you want to modify locked.
The second, error 3197 "Data has changed; operation stopped." indicates that the underlying data in the database has been changed by another user since the last time you retrieved that page from the physical database. This indicates that someone has modified the record (or some field of the record if you are using a Dynaset or Snapshot) that you are attempting to modify.
For example, if user A creates a Dynaset on the table Authors, and pulls in the first page (2k) of records from the database. User B now creates an identical Dynaset, retrieving the same 2k of records. If User A now updates the first record in the Dynaset, user B will not immediately see this change, since User B has already retrieved the first page of records and will not do so again unless he or she refreshes (recreates) the Dynaset.
If user B now attempts to modify the first record of the Dynaset, he or she will receive the "Data has changed; operation stopped" error, warning them that they are about to overwrite new data which they have never retrieved from the database. If you re-execute the operation that caused the "Data has changed; operation stopped" after receiving the error once, the data will be overwritten without the error being generated a second time.
NOTE: Although the AddNew method does not modify existing records, the page where the new record is to be added is still locked when the Update statement is executed to prevent two users from trying to add data at the same location in the database. This means that you should perform the same checks for locking conflicts with AddNew that you use for the Edit method.
Notes on Snapshots
Error 3197 "Data has changed; operation stopped." can occur for Snapshots which have memo fields. Since memo fields are usually quite large, the Access engine does not pull the entire contents of a memo field into the SnapShot at the time it is created; instead, a reference to the memo field in the database is stored in the SnapShot.
If the data in the memo field is changed by another user between the time a Snapshot is first populated (meaning that you access a given record, using the Move or Find methods or visit all records, using the sn.MoveLast method) and the time that record is revisited or made the current record again, the database engine will signal that your data is out of date by invoking run- time error 3197.
NOTE ON ODBC
When using external data engines such as Microsoft SQL Server, Sybase, or Oracle through ODBC, the locking methodology is the responsibility of the remote database engine. Visual Basic acts as a front-end to these database servers and does not control the data sharing aspects of the back-end database engines. In some cases, you can control how the remote server locks data by using backend-specific SQL statements or administrative options.
Some implications of the Access engine deferring to back-end locking schemes:
The setting of LockEdits has no effect on ODBC databases.
The Exclusive parameter of the OpenDatabase method is ignored.
The technique shown in the section "Step-by-Step Example for Level 2: Table or Dynaset Locking", will fail on ODBC databases, with the error "ODBC--Can't lock all records."
It is not recommended that the programmer attempt to lock the entire table or the entire database, using backend native syntax, as this can be detrimental to throughput in a high-volume transaction environment.
Incorporating sophisticated error handling to react correctly when locking contention occurs is the only reliable way to allow the backend database engine to operate its native locking scheme for maximum throughput.
Example Showing How to Call FreeLocks After Locking Error Occurs
In certain circumstances, particularly in an intense multi-user contention environment, the local access engine may get out of synchronization with the host (the VB program). When that occurs, the FreeLocks statement provides a way to allow the engine to 'catch up' and clear any leftover locks. The FreeLocks method tells the engine to perform its default idle processing, including freeing locks that are no longer in use but haven't timed out yet.
Below is an example showing how to call the FreeLocks method after a locking error occurs in a multi-user system. The following code is a sample of an error trapping routine that checks to see if a record is locked in a multi-user system:
Sub EditRecord(ds as dynaset)
On Error Goto ErrLock
ds.lockedits = True ' Return lock errors on Edit call, not the update.
Retry:
ds.Edit
ds.fields(0).value = "Something"
ds.fields(0).value = "Else"
ds.update
exit sub
ErrLock:
ds.bookmark = ds.bookmark ' Cancel the update.
FreeLocks
tm = timer
' Wait 3 seconds:
while timer - tm < 3
doevents
wend
resume Retry
End Sub
Checking for Errors with Page Locking
When using page locks, your code must check to see if the attempted lock succeeded before proceeding. As with the previous examples, use the four-step process of turning off error handling, attempting the operation that will initiate a lock, checking for errors, and finally, turning on error handling.
The following table describes the most common page-locking multiuser errors your code will encounter.
Error number and text Cause and suggested response
3186 Couldn’t save; currently locked by user <name> on machine <name>. This error occurs when a user attempts to update a page that contains a read lock placed by another user.
To handle this error, wait for a short interval, and then try to save the record again. Optionally, you can inform users of the problem and allow them to indicate whether or not they want to retry the operation.
3197 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. This error occurs when you use the Edit method or the Update method and another user has changed the current record since you opened the Recordset or last read data from the record.
If this error occurs when you use the Edit method, you may want to refresh the user’s view of the data with the current data and then attempt the Edit method a second time.
If this error occurs when you use the Update method, you’re using optimistic locking and the record has changed since you used the Edit method. Inform the user that someone else has changed the data. You may want to display the current data and give the user the choice of whether to overwrite the other user’s changes or cancel edits.
3260 Couldn’t update; currently locked by user <name> on machine <name>. This error occurs when you use the Edit method and the page containing the current record is locked.
This error also occurs when you use the AddNew method or the Update method to save a record on a locked page. This situation can occur when a user is trying to save a new record or when you’re using optimistic locking and another user locks the page.
To handle this error, wait for a short interval, and then try to save the record again. Optionally, you can inform users of the problem and allow them to indicate whether or not they want to retry the operation.
See Also For more information about Microsoft Jet error numbers, see Appendix D, “Error Reference.”
In the previous pessimistic locking example, the code checked to see if an error occurred and reacted regardless of the type of error. This example can be made more versatile by actually checking the error that occurs and responding to it. The following function modifies the pessimistic locking example to include better error handling:
Sub EditARecord()
Const conMultiUserEdit As Integer = 3197
Const conRecordLocked As Integer = 3260
Dim dbs As Database, rst As Recordset
Dim intLockCount As Integer, intRndCount As Integer
Dim intChoice As Integer, intX As Integer
Dim strDbPath As String
On Error GoTo 0
strDbPath = "C:\JetBook\Samples\Northw
' Open the database in shared mode.
Set dbs = OpenDatabase(strDbPath, False)
' Open the Orders table in shared mode.
Set rst = dbs.OpenRecordset("Orders"
With rst
' Setting LockEdits to True tells Jet to use
' pessimistic locking.
.LockEdits = True
.FindFirst "[OrderID]=10565"
' Try to edit the row. This will cause a lock attempt.
On Error GoTo Err_EditARecord
' If record is locked, edit will fail.
.Edit
' Make changes and commit.
![ShipAddress] = "New Address 3"
.Update
End With
Exit_EditARecord:
On Error Resume Next
rst.Close
dbs.Close
Exit Sub
Err_EditARecord:
Select Case Err.Number
' Data has changed since last read.
Case conMultiUserEdit
Resume
Case conRecordLocked
' Row is locked.
intLockCount = intLockCount + 1
' Tried to get the lock twice already.
If intLockCount > 2 Then
' Allow user to choose Cancel/Retry.
intChoice = MsgBox(Err.Description & " Retry?", _
vbYesNo + vbQuestion)
If intChoice = vbYes Then
intLockCount = 1
Else
Resume FailedEdit
End If
End If
' Yield to Windows.
DoEvents
' Delay a short random interval,
' longer each time the lock fails.
intRndCount = intLockCount ^ 2 * Int(Rnd * 3000 + 1000)
For intX = 1 To intRndCount: Next intX
' Try the edit again.
Resume
Case Else
' Unanticipated error.
MsgBox ("Error " & Err.Number & ": " & Err.Description)
Resume FailedEdit
End Select
FailedEdit: ' Begin contingency procedure if edit fails.
MsgBox "This row could not be edited. Please try again later."
GoTo Exit_EditARecord
End Sub
This code works by moving the program’s execution to the Err_EditARecord section when an error occurs. The code in the Err_EditARecord section checks the specific error message and tries to correct the problem. It also allows the user to retry or cancel the operation. Note that it specifies a random interval for retrying the operation. This is an important technique for making sure two users vying for the same record don’t end up in a deadlock situation where code is trying to lock the record at the same time. By introducing a random element into the timing loop, you can minimize the chances of a deadlock.
--------------------------
Send feedback to MSDN.Look here for MSDN Online resources.
Microsoft Office 2000/Visual Basic Programmer's Guide
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
Page-Level Locking vs. Record-Level Locking
When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data. For previous versions of the Jet database engine, a page is equal to 2K (2048 bytes) of data within the database file; for the current version, Jet 4.0, a page is 4K (4096 bytes) of data. (The size was doubled to accommodate storing data as Unicode characters, which occupy 2 bytes instead of the 1 byte used by previous characters.) Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked. Page locking generally results in better performance, but can reduce the ability of other users to edit data.
A new feature of Jet 4.0 is the ability to lock individual records rather than pages. In Access, this is controlled by the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). By default, this setting is selected (on), which means two users can update or delete two different records that are located on the same page within the database (which isn't possible when you use pessimistic locking under page-level locking). The locking mode that is in effect is determined by the first user to open a database. If the first user has the Open databases using record-level locking setting selected, all users who subsequently open that database will use record-level locking whether they have the setting selected or not. Conversely, if the first user to open a database has the Open databases using record-level locking setting cleared, all users who subsequently open that database will use page-level locking.
When record-level locking is on, data edited through Access forms and datasheets will use record-level locking. Also, Recordset objects opened by using the DAO OpenRecordset method, and any ADO methods (when you use the Microsoft Jet 4.0 OLE DB provider) that open or return a Recordset object will use record-level locking. However, any SQL Data Manipulation Language (DML) queries — that is, queries that add, delete, or modify records — that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed — they still require page-level locking.
You can't programmatically override record locking settings for DAO Recordset objects, Access forms and datasheets, or SQL DML statements run from the Access query user interface.
When you use ADO with the Microsoft Jet 4.0 OLE DB Provider to work with an Access database, you can set the provider-specific Jet OLEDB:Database Locking Mode property of the Connection object before opening a database, and then set the provider-specific Jet OLEDB:Locking Granularity property of the Recordset object used to execute SQL DML statements or to work with methods that open or return a Recordset object. For information about the Jet OLEDB:Database Locking Mode and Jet OLEDB:Locking Granularity properties, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Ap pendixes folder on the Office 2000 Developer CD-ROM.
-------------------------- ---------- ---------- ---------- ---------- ---------- ----
Send feedback to MSDN.Look here for MSDN Online resources.
--------------------------
Page-Level Locking vs. Record-Level Locking
When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data. For previous versions of the Jet database engine, a page is equal to 2K (2048 bytes) of data within the database file; for the current version, Jet 4.0, a page is 4K (4096 bytes) of data. (The size was doubled to accommodate storing data as Unicode characters, which occupy 2 bytes instead of the 1 byte used by previous characters.) Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked. Page locking generally results in better performance, but can reduce the ability of other users to edit data.
A new feature of Jet 4.0 is the ability to lock individual records rather than pages. In Access, this is controlled by the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). By default, this setting is selected (on), which means two users can update or delete two different records that are located on the same page within the database (which isn't possible when you use pessimistic locking under page-level locking). The locking mode that is in effect is determined by the first user to open a database. If the first user has the Open databases using record-level locking setting selected, all users who subsequently open that database will use record-level locking whether they have the setting selected or not. Conversely, if the first user to open a database has the Open databases using record-level locking setting cleared, all users who subsequently open that database will use page-level locking.
When record-level locking is on, data edited through Access forms and datasheets will use record-level locking. Also, Recordset objects opened by using the DAO OpenRecordset method, and any ADO methods (when you use the Microsoft Jet 4.0 OLE DB provider) that open or return a Recordset object will use record-level locking. However, any SQL Data Manipulation Language (DML) queries — that is, queries that add, delete, or modify records — that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed — they still require page-level locking.
You can't programmatically override record locking settings for DAO Recordset objects, Access forms and datasheets, or SQL DML statements run from the Access query user interface.
When you use ADO with the Microsoft Jet 4.0 OLE DB Provider to work with an Access database, you can set the provider-specific Jet OLEDB:Database Locking Mode property of the Connection object before opening a database, and then set the provider-specific Jet OLEDB:Locking Granularity property of the Recordset object used to execute SQL DML statements or to work with methods that open or return a Recordset object. For information about the Jet OLEDB:Database Locking Mode and Jet OLEDB:Locking Granularity properties, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Ap
--------------------------
Send feedback to MSDN.Look here for MSDN Online resources.
ASKER
JDettman & CareyMBilyeu,
Thanks and sorry, I have been away for some days.
As I say before I guess it have something to do with Citrix. Coz when this happens no one in the network can add records. They all get the error(3260 Couldn’t update; currently locked by user...). No matter if trying again after some seconds.
Sometimes it goes days without this error.
I have notice that when user logon to Citrix MetaFrame lots of files like 'JET146D.tmp' are created in the Tempfolder. I have also notice that sometimes when all users have logoff. It's still not possible to delete all this 0 Byte files, coz of sharing violation. Could this bee the reason to this type of error? And why does it happen?
/Janne
Thanks and sorry, I have been away for some days.
As I say before I guess it have something to do with Citrix. Coz when this happens no one in the network can add records. They all get the error(3260 Couldn’t update; currently locked by user...). No matter if trying again after some seconds.
Sometimes it goes days without this error.
I have notice that when user logon to Citrix MetaFrame lots of files like 'JET146D.tmp' are created in the Tempfolder. I have also notice that sometimes when all users have logoff. It's still not possible to delete all this 0 Byte files, coz of sharing violation. Could this bee the reason to this type of error? And why does it happen?
/Janne
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim,
I did get this answer i a other forum:
If you disable the terminal server licensing and the license logging service in the control panel the system will then no longer lock and create the *.jet files in the %systemroot% directory. This may also fix your other database problems.
But I grade you coz of you have been the most active one.../Janne
I did get this answer i a other forum:
If you disable the terminal server licensing and the license logging service in the control panel the system will then no longer lock and create the *.jet files in the %systemroot% directory. This may also fix your other database problems.
But I grade you coz of you have been the most active one.../Janne
Also are you updating the database in anyway via Access while people are accessing it via VB?