Problems with recording locking MsAccess 2003

I have a split access 2003 database on a network, the front end (mde) is located each local computer with the backend (mdb) on the server. The tables are linked using a mapped letter drive. This is a multiple user environment with 2 to 3 users connected and entering data 24/7.
The problem is with the record locking, I need it to lock a record when it is being edited by another user. What is happening is it is sometime locking several records and it seems to be the record before the edited record and the record after.

So my question is two-fold first what type of record locking strategy I should be using and secondly where is the record locking suppose to be implemented, on the front ends or the back end or both

Server=Windows 2008 Server Standard
Client = Windows 7 and Windows xp
MsAccess 2003

Who is Participating?
if the backend is SQL server it ignores Access' record locking.  It will do its own thing.  To achieve something like preventing another person from opening a record while it's being viewed by a second user you can maintain your own lock table or put a flag/username field combo in the tables you want this behavior from.  There's a lot to think about with that approach including what to do when someone goes into a record and then falls asleep, or goes home, or dies.
Access says record locking but it locks a page of records at a time so it may lock more than one record at a time.  You pretty much have to tolerate this if you are using Access.  The record locking setting will be on the the database your users are opening (the front end).

One alternative is to use Access as a front end to a SQL server database, or handling a lot of the record handling yourself via VBA.
mickeyshelley1Author Commented:
if i upsize the backend how would I set the record locking on the front ends to eliminate this problem?
mickeyshelley1Author Commented:
LMAO thank you for the advise...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.