RecordLock Property

[JackieEVSC]

I created a 2007 database (frontend and backend) that links to a SQL database and an AS400 database. There are some linked Access tables and a few local tables (in the frontend) that allows users to customize their environment. I have all the queries and forms local (in the frontend).

This database will replace a different Access database (that I did not write) that consistently locks everyone out ... forever. In order to use it again, they have to replace it with a backup, which often means losing data.

My question is ... do I need to change the default RecordLock property from "No Locks" to "Edited Records" to keep from having this problem, or will having everything local except the linked tables solve this problem? Is there any risk in having everything but the linked tables local?

 

[kjv1611]

If each person being able to load the same record at the same time is an issue, then yes, you'll need record locks.

However, just using Access' built-in record-locking doesn't work out too well.

When I've tried to use it (wish it weren't this way), this is what happeend:
1. User1 Opened Record1, looked at it, found whatever other reference they needed, and began editing.
2. User2 Opened Record1, looked at it, found whatever other reference they neded, and tried to begin editing the same record. Only, they did not know that User1 was already working on it. So User2 just lost all their time and effort.

So I did manage to sort of get around this, though it doesn't always work correctly.

I set up a separate RecordLocks table, as well as a users' Session table. Here's basically what I did with that:
[ol][li]I did set the Access record locks on in case the other fails.[/li]
[li]When a user opens the database, a new record is added to the sessions table. If they had any previous "open" sessions, then they would just be deleted to avoid having multiple sessions... or else they get a "close" or "stop" date appended - I forget at the moment)[/li]
[li]When the user pulls up a record, if no one else is already "on" that record, then a record is added to the Record Locks table, and the user is shown the record.[/li]
[li]If someone else is "on" the record, the user is skipped to another record automatically.[/li]
[li]If the user was specifically searching for the locked record, then when they try to go to it, a message is displayed, saying "Record1 is locked by SoAndSo, would you like to wait?" - well, I think I started this part, but never finished it... so it doesn't exactly work that way right now. [/li]
[li]Once the user moves on to another record, the record locking record for the previous data record is deleted, so that someoen else can then look at the record, and of course the next record gets a new record created in the locking table.[/li]
[li]When a user closes the form they are on, or clicks the "Exit" button for the database, the session is considered ended, and a session closing date is logged.[/li][/ol]

The session table was used as an extra error-checking piece for the record locks. I forget the details, but it never worked 100% to what I really wanted out of it. It did get the job done, but I did have to put a little manual effort in, here and there. Things would occasionally happen to where one user ended up with more than 1 record lock... or more than one open sessions.... and so nobody else could get to the "locked" record, b/c it thought SoAndSo was working it, though they were not...

Well, not sure whether that was any help, but hopefully will at least give a glimpse of the joys of record-locking in Access.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57