Problems with recording locking MsAccess 2003

Posted on 2011-10-04
Last Modified: 2012-05-12
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

Question by:mickeyshelley1
    LVL 16

    Expert Comment

    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.

    Author Comment

    if i upsize the backend how would I set the record locking on the front ends to eliminate this problem?
    LVL 16

    Accepted Solution

    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.

    Author Closing Comment

    LMAO thank you for the advise...

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now