Database locking

  • Thread starter Thread starter tgavin
  • Start date Start date
T

tgavin

I have a split db with 3 different frontends and about 30 users. The majority
of them use a front end that they need to log into so that they only see
their own cases (social workers). Although there are a few still using the
old shortcut, most are using a copy which is added fresh to their desktops
each time they log in.

They primarly use one form to write up their visits. The query under it is
comprised of 2 primary tables linkedwith a one to one relationship and 2
other tables with 1 to many relationships. The first table contains all the
pertinate info on the visit using text, date, time and combo boxes. The
second table (1-1) has the memo and large text fields. There is one subform.

On occassions, the database will lock giving them the message "can not
update; currently locked". When they hit to OK, they lose the record,
sometimes with long synopsis' on them.

First, there is no way that there can be 2 people accessing the same record,
between the fact that they are working on a front end copy and they can only
access their own kids. It does appear to happen on days with above average
use and to people who are putting in a lot of notes on one day.

I have been posting this problem for months and am at my wits end. I am
working with low computer skilled users who love to blame everything on the
db. I really need an answer to this if anyone can help.

Terri
 
Terri

If you are using a single back-end, consider the possibility that more than
one record is stored on the same "page" in the Access file.

This would mean if you and I both attempted to get one of our own records,
and if both records were stored side-by-side on the same Access file, one of
us would get an error message about the record being locked...?!

I didn't notice which table it was that's locking up ... and I don't have a
very clear understanding of your table structure. Would you be willing to
post back a description something like the following? (this is a woefully
under-complicated table structure, good for nothing more than
description...):

tblStudent
StudentID
FName
LName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

If you are using a memo field to "collect notes", consider using a Notes
table that has something like the following structure:

tblNotes
NoteID
AccountID (this is whatever you're using to identify the
person/account/whatever to which the note applies)
NoteTaker (the person taking/documenting the note)
NoteDate (date/time field for when)
Note (unless you have verbose notes, this would be a text field, not
a memo)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You haven't stated anything that you've tried previously in this post, so
forgive any dupes.

1. Is the form using Add Only mode?
2. Is the form using some form of Pessimistic Locking? (Those locking a page
of data exclusively.)
3. Have you entertained using an unbound form and committing the changes
with code/queries?
 
Back
Top