[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problems with recording locking MsAccess 2003

Posted on 2011-10-04
Medium Priority
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
  • 2
  • 2
LVL 16

Expert Comment

ID: 36913920
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

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

Accepted Solution

kmslogic earned 2000 total points
ID: 36914379
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

ID: 36914462
LMAO thank you for the advise...

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

868 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