Problem with record locking in Access

Instead of 5+ users accessing the database itself, why don't you consider storing the data on the server. A copy of the database would then be stored on each individual PC and linked to the data on the server.

Let us know if you need further help.
mpyne

The key here is how long the locks are held.

If your program Edits the record on the first data entry, and doesn't Update the record until all entries are made and the record is submitted, the page that the record is on (4KB pages, I believe) is locked until the Update.

Ensure that your program (1) doesn't Edit the record until it is ready to update all data, and (2) retries any Edit attempt that receives the Locked error (for at least a couple of seconds).

Hmm... making 5 copies of the database would not be a good
idea because the workstations use FAT16 so anyone of the pupils
could delete the MDB file off the hard drive. Plus I haven't a clue
how I would set it up (forgive me but I am a hardware engineer and
not a database programmer.)

The idea from Bhess1 sounds good, but how do I make those
changes that you suggest - could you be more specific please...
Thankyou for the help!

This is where coding on the form becomes important.  If your input forms are bound directly to the database, then by default you issue an implicit Edit whenever you change any data on the forms (I could be mistaken on this - I'm primarily a VB programmer - but I don't believe that I am).

Instead, have your form load and display the information 'manually' - from a recordset object.  The user then edits as needed, and, when you click 'Save', or go to another record, you .Edit the recordset, update the fields, and .Update the recordset before going to the next record.

Hi Bhess1

Thanks for your comment - again it sounds like a great idea in Theory
but more difficult in practice because I am a hardware technician and
although I have some months experience in VB programming it
would take me a long time to figure out how to do what you
mentioned...

Just a thought, but are you using a form to edit the data? Have you checked the form properties to make sure that record locking is set correctly there?

Couple of things:

1.  All users of the database need full access to the directory where the MDB is stored.  If they don't have that, Access create a .LDB, which it uses for locking operations.

2. Make sure the form your using is set to "No Locks".  This is a bit of a mis-nomer as locking is still going on.  Access has two modes of locking pessimistic (Edited Records) and optimistic (No Locks).

 The difference is when a locked is placed. Access locks on 4K pages, with one or more records per page.  Records never span page boundries.  So when a user locks a page, they may lock several records (Note that A2000 offers true record level locking).

  With pessimistic, a lock is placed as soon as the user begins editing the record and it's not released until they finish.  With optimistic, it's locked just while it's being saved.  Thus, it's possible that two users may over write each others changes (Access pops up a dialog warning this will happen and allows some choices.

3. There is a bug in Access (MSFT calls it a feature) in that if you open a MDB from the MRU (Most recently used list), it opens with the settings that were in effect when it was put on the list and not the current settings.  This means that if someone had the "exclusive" box checked and now opens from the MRU, it's opened exclusive even if it's not currently checked.  This doesn't bear on your current problem but I thought I'd mention it.

HTH,
Jim.

Oh BTW, a quick fix for locking problems is to pad the records with dummy fields enough so that one record can only be stored on a page.

 You can use text fields and set there default values to all "x"'s or something.

Jim.

Thanks for your comment Jdettman - it was very informative.

I have checked the form that people are using and it is already set to "no locks"
is this the best one for it to be set on?

I also checked the network folder that the MDB file is stored in and its
permissions were set to "RWX" for all staff so I have changed this to
"Full control" in the hope that it might help.

Also we are using a shortcut icon to load the database so the bug with
the most recently used file list won't apply.

The fix with padding records with dummy fields sounds useful although I will
just see how things go for a while and see if the change of permissions might
have helped things - I still don't fully understand how the .LDB file works
in access.

just a quick update - all the things I have tried have not made any
difference and the problem is getting worse and worse because
more staff are using the database at once.

Please could someone tell me what I can do to make access share the database
between all the staff without all these annoying errors appearing all the time. We
are also getting another error that says something about the data form having
been changed by another user and do we want to save changes to the data.

I will just say a bit more about the system we are running:

We have a table in the Access database called "Grades" and then we have a
form which is linked to the Grades table and also linked to the Pupils table
using a one-to-many relationship (all grades but only matching pupils). The
relationship is there in order to look up the forename and surname from the
pupils database.  There are about 290 records in each of these two tables and
they are linked with a master key which is the "Pupil ID".  

I reckon that each record is on average about 835 bytes which means that Access
only locks a set of 5 records for each user and yet even with just 2 database users
accessing completely differrent groups of records we are still getting all these
sharing errors. This is a real headache - I was under the impression that Microsoft
said this software was designed for sharing data on a network...!??

Any further help very thankfully recieved.

Sounds like you've got nothing more the the normal page locking going on.  

MSFT always touted the page locking as a performance enhancement over record level, but some many folks complained that they finially put it in A2000.

You need to make sure that all the forms your using are set to "No Locks".  When this is done, from what you describe it should be rare that two people bump into one another.  But as I havn't seen the design, I can't say that with 100% certainty.  My concern would be is that your storing something in the pupil record.  Then I could foresee lots of conflicts.

  If your not using A2000, the quickest fix is to pad the records.  IN A97 and back, the page size is 2K (2048 bytes).  I can outline how to calculate the record size if you want to go that route.  As it doens't sound like the MDB has all that many records, that's the route I'd go.

Jim.

Thankyou for your help Jdettman - I think that padding the records looks like
a good idea in order to try and minimize the number of records that Access
is locking.

So do I just create 3 or so dummy fields in the Grades table and set them as
text and 255 chars and then default them to "xxxxxxxxxx......" etc?  Do I also need
to pad out the Pupils table too? - this is simply linked in the form in order to
get out the pupils forename and surname - the data is not changed in this
table, only in the grades table.

To answer your question on the .LDB, it really doesn't do all that much on it's own per say.

First, the file holds the list of users. This list is two 32 byte entries for each (user name and machine name) up to the max of 255 users.  Therefore it is never larger 16K.

  It's other use is that Access uses the LDB file name to place extended byte range locks with the NOS system.  Access never writes locks to disk.  It simply takes a lock out on a specific byte range (depending on the type of lock) of the LDB file that doesn't exist.

 One other note: Keep in mind that you can get conflicts on index pages as well.  Also, if there indexes defined on everything under the sun, that increases the time a lock is held on a page.

Jim.

Ok here's how it all breaks out (this is for A97 BTW):

1. Seven bytes per record for record overhead.

2. One byte variable-length field overhead for each Text, Memo, Hyperlink, and Long Binary (OLE Object) field.

3. One additional byte for every 256 bytes of the total space occupied by all Text, Memo, Hyperlink, and Long Binary (OLE Object) fields

4. One byte fixed-length field overhead for each Yes/No, byte, Integer, Long Integer, AutoNumber, Single, Double, Currency, and Date/Time field

For the fields:

Byte 1 byte
Integer 2 bytes
Long Integer 4 bytes
Single 4 bytes
Double 8 bytes
Currency 8 bytes
AutoNumber with FieldSize property set to Long Integer Long Integer 4 bytes
AutoNumber with FieldSize property set to ReplicationID GUID 16 bytes
Yes/No Boolean 1 bit
Date/Time 8 bytes
Text Variable
Memo Long 14 bytes
OLE Object Long 14 bytes
Hyperlink Long 14 bytes


  Now the OLE and memo fields are special in that they are typically stored seperate from the "fixed" portion of the record.  For these fields, just count the overhead and pointers for the fixed record size.  

Your goal then is to pad the records with text fields so that the size exceeds 1024 bytes.  When you figure out how many text fields you need, modify the table def and make sure you set the default value to all "x"'s.  As text fields are stored variable lenght, you must put something in there or you'll end up with a one byte field.

 I'd start with the grade record first and switch everything to pessimistic locking (Edited Record).  Then see what that clears up.

 If you have any problems with this or would like me to look over the design, you can E-Mail the MDB to me.

Jim.

 

Thankyou once again Jim.

I have made the changes to the form as you suggested so now each record
is over 1024 bytes and also set the form's locking property to "edited record".

I will test how this goes today and let you know what happened.

Hmm, not going too well this end.  Now we are having new errors which are
appearing frequently and say :

"Couldn't save to disk".
"Network error".

Any ideas what this means?

"Network error" means that the client is having a problem with the connection to the MDB in some way.  A lot of those can be taking care of by making sure you close any objects you open in code and setting the object variables to nothing.  For example:

  If Not IsNothing(rstLoads) then
     rstLoads.Close
     Set rstLoads = Nothing
  End If

  on the "couldn't save to disk" is that part of the same error.  Outside of the connection problem above, the only other error you might bump into is "record too large".  This would happen if you made the padding too much or had a large number of text fields that were filled.  This is one of the "gotchas" with Access.  Since a record must fit on a single page and text fields are stored variable length, it's possible to add a record OK, then edit it and not be able to save it because of increased size.

What about the locking errors?
Jim.

We haven't had any more locking errors yet which is good news, though the
reports were finished last week so there have not been many people using
the database now.  I think that we have fixed that problem ok and I am
ready to close this question soon and assign you the points.

I just thought I'd check if you knew what these "couldn't save to disk" errors
are that appear.  I usually get the error when I leave Access open on my
computer and then come back an hour or so later and as soon as I try and
do anything it gives me that error and I have to close it down and reopen it.

Thanks again for the help.  I suspect that the above error may be something
wrong with bottlenecks on our network and PDC which is not really relevant
to the problem of record locking so feel free to submit an answer posting.

 Go through any code you have a make sure your closing recordsets.  You should also make sure that the PC when left idle is not doing anything (i.e. sitting on a "menu" of some type).

  JET will kept trying to refresh pages in it's cache.  If it can't do that (gets a network timeout), you'll get that error.  If your having network congestion, that will happen sooner or later over the course of an hour.

Jim.

hmm... I am using VB code in the database but none of it is to do with recordsets.
The PC when idle is not in any menus although I tend to leave it in a form or
table view.

I think you're right about the network congestion because we have had some other
programs crashing from timeouts trying to load stuff off the network when its very
busy.

The teaching staff are currently filling out the school reports in the database so
there will frequently be 5 or more people editing records from the reports table
at a time which should be a good test to see if the changes we have made are
working.

Havent had any further problems yet but I will leave this question open until
Thursday if you don't mind and then Thursday morning perhaps you could submit
an answer posting Jdettman. Am really grateful for all  you work on this.  

Glad to hear things are starting to smooth out a bit.  On the network side, what's being used for a protocol?  BTW, whatever it is, I hope all stations and servers are using the same default protocol.  If there not, this can give you timeouts.

  One of the ways this happens is when TCPIP is run on a NT server in addition to NetBeui.  TCPIP is a bit of CPU hog under NT and will cause timeouts if it is a higher priority over NetBeui.

 BTW, you can accept anyone of my comments as an answer.

Jim.

Yeah this stuff we are having with network timeouts and file errors on the network
is really giving me a headache because I can't understand why the network is
running so slow.

We have a PDC and BDC NT servers on one domain and a trust relationship with
a PDC on the second domain.  The whole network is (or should be) running at
100MBps with the 3xIntel switches and 100MB 3com hub. It is a bit of a complex
setup because both domain controllers are acting as bridges accross two
different IP ranges - they both have two net cards in them. Also have an ISDN
router linked to one of the switches.

I think I might put in another question on EE about this.  Or if you have a solution then
I could increase this question to 450 points or something.  :)

Anyway - the good news is that all is running smoothly on the database front so
far.

The bridges are probably the problem.  Your carrying all the traffic that should be local across the entire network.  ie. all the broadcast meesages for browsing etc.  If you really need to bridge and no one is browsing the network, you could shutoff the boradcasts.

  However as you well know, a network can bit a tricky at times and I'd hate to offer suggestions that would actually slow things down.  Without a lot more detail and a good sniffer, it would be hard to say what's actually causing the problem.

  You might have something as simple as a bad NIC throwing lots of bad packets.

Jim.

Everything seems to be running fine now and have not had any problems in the
last 3 days so I think its time to close this question.

Thankyou for all your input Jim, could you submit an answer posting.