Database Engine could not lock table.....error message

I have a live db running on a VPN network (FE/BE both centrally located on a main server , users 'view' their own personal front end through the VPN tunnel rather than having it on their client pcs) ) and have recently provided a new update to the existing Front End. Until this time , some users were problem free but some persisted in getting the above message. Now the new update is in place , they all get it, and this when no one is using the db and a single user logs in and tries to use the system. I guessed initially it must be a design fault ( ie mine ) but then why does it work for some and not for others?. It also works no problem on my network at home ( Windows XP, Access 2003) (. The user runs Access 2000.My db was developed in 2003 ( but saved in the default 2000 file format ). My online searches have started to uncover references to installation of SP5 and BDE. Is this the right direction?I have to go in tomorrow to sort it out so Im giving it urgent points value
Any help most appreciated.
Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi hominus7,
Do several users use the same FE copy on the server via TS?  You should have a copy for each user on the sever.
Dave :-)
hominus7Author Commented:
Hi . Each user has his / her own copy.
To isolate the VPN and network, try running the database from each version on the server directly and from the network at work.

This problem is usually caused by multiple datasets to the some data open at the some time.  that could be a recordset, query and/or form.  You want to look for that.

Don't know if these might help.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

hominus7Author Commented:
Thanks for your advice. We have now discovered that the problem is not with the VPN or network, but rather the internal table structures.So It seems like a design fault on my part. I have a contact table and a client table. The client table PK is a FK to the contact table. On the form a user opens the to enter data to the contact table, there is a button that opens another form to enter client data ( the contact form does not close). If two users are entering data on their respective front end contact forms, and either clicks the button to enter client data , the error message appears  'Database engine could not lock th table Client etc etc....' How can I get round this ?
The two users are on the same record?
If no, check that the record locks property on the data tab for both forms are set to no locks (optomistic locking) or edited record (pessimistic locking).

If yes, set the record locks property on the data tab for both forms to edited record (pessimistic locking).  You can use the Access built in locked record message which is at bit confusing or the code below.

One of the biggest concerns in a multiuser environment is what happens if two or more people try to change the same information at the same time.  The way that this is handled is through locking.  If you look up "lock" in Access and VBA help, you will see record locking, all record locking, page locking, optimistic locking, pessimistic locking.  99% of the time you want pessimistic record locking (editied record) or optimistic record locking (no locks).  Then the question becomes optimistic or pessimistic locking.

The optimist says "It's unlikely that two people will be in the same record at the same time."  The pessimist says "Yah, right!  And most likely, it'll be my boss that's in the same record as me!!"  I our medical office, we have over 2000 patients in the database so you might think it is pretty unlikely that two people would be changing the same patient at the same time.  Until you realize that we are only seeing ten of those 2000+ on any given day.  Until I switched to pessimistic locking for the patient records, we were getting a lot of write conflicts.  I use optimistic locking for the rest of my db.

If you use optimistic locking, you can reduce the risk of write conflicts by breaking your forms up into subforms or tabs.  Records are saved when you move to a subform or another tab.  You can also put in "Me.Dirty = False" in after update events for some or all controls to save the record.

If you don't want the second person to be able to save changes to a record when someone else has not finished saving then that is called pessimistic locking.  The code below (thanks to Jim Horn if my memory serves) will notify the second person that the record is in use and by whom.  The biggest downside of pessimistic locking is the famous employee went to lunch without saving or logging off.  The computer goes to screen saver, locks and no one but the missing employee can save the record.  This can be resolved by 1: automatic logoff (of database or windows) after timed inactivity; 2: automatic save by setting the timer interval with the form's on dirty event and "Me.Dirty = False" in the timer; and 3: a reminder to save the record.

Function IsRecordBusy( _
    rs As Recordset, _
    Optional UserName As String, _
    Optional MachineName As String, _
    Optional CreateMsg As Boolean = True) As Boolean
      ' Accepts: a recordset and two string variables
      ' Purpose: determines if the current record in the recordset is locked,
      '          and if so who has it locked.
      ' Returns: True if current record is locked (and sets UserName
      '          and MachineName to the user with the lock).  False if the
      '          record isn't locked.
      ' From: Building Applications Chapter 12
          Dim ErrorString As String
          Dim MachineNameStart As Integer
10       On Error GoTo IsRecordBusy_Error

20        IsRecordBusy = False
40        rs.Edit                     'Try to edit the current record in the recordset.
50        rs.MoveNext
60        rs.MovePrevious
70        Exit Function               'No error, so return False.

80       On Error GoTo 0
90       Exit Function

100       If Err = 3260 Or Err = 3197 Or Err = 3188 Then   'Record is locked -- parse error string.
110           If CreateMsg Then
120               ErrorString = Error$
130               UserName = Mid$(ErrorString, 45, InStr(45, ErrorString, "'") - 45)
140               If UserName = "" Then UserName = "(unknown)"
150               MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
160               MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
170               If MachineName = "" Or MachineNameStart = 0 Then MachineName = "(unknown)"
180               MsgBox "This record is being used by " & UserName & " on station " _
                      & MachineName, vbExclamation, "Record Lock Warning"
190           End If
200       Else
210           MsgBox "Error: " & Err.Number & ", " & Err.Description & ", IsRecordBusy, " & Erl
220       End If

   On Error GoTo 0
End Function

Call the function with:
    Set rstForm = Me.RecordsetClone
    strBM = Me.Bookmark
    Set rstFormClone = rstForm.Clone()
    rstFormClone.Bookmark = strBM
    If IsRecordBusy(rstFormClone) Then
          Cancel = True
          Exit Sub
    End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hominus7Author Commented:
I think you may well have solved my problem. I wont be able to test it until tomorrow with the user. I found that while the 'Contact' form had record locks set to no locks , the 'client' form had 'all records' as its setting. Thus my problem. Ill report back .. Thanks
I hope you found the problem.  "All records" locking will do it.  Try setting both of them to no locks.  If you don't get the message (paraphased "Someone else has changed the file.  Do you want to save, discard, save to clipboard." often, you are home free.  If you get the message rarely and your users are confused by it, you can change the message by trapping it in the form's error event.  If you get the message often, go to pessimistic locking.

BTW: Tools, options, advanced tab, default record locking will set the default setting for all new forms.
hominus7Author Commented:
Reporting back. It was the 'all records ' locking.
Thanks for your help.
Glad to help.

Don't forget to assign points and close the question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.