Default Record Locking Question

[nobull613]

I have a couple Access 2002 database that are in a multi-user environment that have been running fine (one for a few years) and am now users are starting to receive Currently Locked errors when entering data.

The database is not split (not ideal, I understand).

If I set the Default Record Locking in Options/Advanced to No Locks and deselect the Open databases with record-level locking will that apply only to my PC or to the database file used by all users? Basically do I need to make that change on their machines or will changing it on mine apply to all?

Thanks!

 

[T111]

There could be a few reasons
1. I find that adding an auto number field to each table reduces the probability of locking
2. The size of your data along with extra users could now be causing problems - could you archive off certain historic information and create 2 database 1-current 2-historic
3. Ensure nothing has changed to the physical location of the access database - the folder on the server that holds the database has not been moved or altered
4. Compact and repair the database
5. I find that if you create a front end for each user with just the tables missing =Frontend.mdb. Create a separate access database with just the tables and place in on the server (backend.mdb) then link all frontends (1 for each user) to the 1 backend (if you’re not already doing this)

I usually try to use SQL as the database and link using ODBC.

Just a few pointers hope this helps!

 

[nobull613]

T1111, thanks for the suggestions.
1) There are auto-number fields for each table.
2) The one that's been used for years is periodically archived, then compacted and repaired. The new one does have a lot of records but they aren't date specific, therefore an archive won't work as all records are "current"
3) I have not been made aware of any network/server changes where the files reside. One of the affected mdb files has just gone live (no time for our IT to change that yet).
4) I tried backing up the file and doing the compact and repair when aware of the locking to no avail.
5) I tried to split databases in the past and the performance was horrible. They ran much, much slower than in one file. The FE was on the local machine and BE on the network.

I've done the MSSQL BE / Access FE option in the past and it worked with another app, but getting IT support for those databases is nearly impossible.

I was hoping the record locking choices in the Options might help.

Thanks