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

mickeyshelley1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

kmslogicCommented:
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?
kmslogicCommented:
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.

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
mickeyshelley1Author Commented:
LMAO thank you for the advise...
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.