Couldn't update currently locked by...

What is the code you are using to open the database?  There is an option to open it in share mode and I believe the default is exclusive mode.

Also are you updating the database in anyway via Access while people are accessing it via VB?

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.

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....

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.AbsolutePosition > 0 Then
            Data1(Index).Recordset.Edit
        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

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?

Jet locks a page of memory whenever a record is maintained, added, deleted, or changed in any way.

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.

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.

#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 &lt; 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\NorthwindTables.mdb"
      ' Open the database in shared mode.
      Set dbs = OpenDatabase(strDbPath, False)
 
      ' Open the Orders table in shared mode.
      Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset, dbSeeChanges, dbPessimistic)
 
      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\Appendixes folder on the Office 2000 Developer CD-ROM.


--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.

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

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