multiuser conflicts/errors

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a small database that we use to help make inventory
adjustments on our mainframe. I am having errors that I
cannot explain when the database is used by more than one
keypunch operator. I hope to understand why these errors
occur, then set up proper security. Here is what I know:

We download about 1800 warehouse records into the
database. The fields are PartNo Qty Location.
I have a query feeding a form that displays this
information. The form is a continuos form.
We print out a hardcopy report, make necessary inventory
changes, which can include changing quantity, deleting a
record, adding a record, and sometimes changing the
partno, or location, then upload this to the mainframe.
Two or more keypunch operators, operating at the same
time, enter the changes using the form.

Access is loaded on each computer, while the database
application resides in a folder on the server.
There are no workgroups set up.
Record Locks property is set to no locks, on both the form
and the query.
It is rare if ever that two operators modify or delete the
same record.

What the problem is:
We get erratic results. Many times a change that one
operator made, is posted to the record above or below the
desired record to be changed.

I have tried to reproduce the problem in a smaller
controlled situation, using two computers, making defined
changes. I have not been able to reproduce the problem.

We do not get conflict messages, and since two operators
do not work on the same record, we should not.

I suspect somekind of timimg error that I do not
understand, but am afraid to try making changes until I
understand the problem. A full scale test may be in
order, but is expensive. I hope you can point me in the
right direction.
 
A few thoughts.

(1) It's not clear whether your database is set up as follows. If it's not,
it probably should be!
- a "back-end" (BE) database containing all of the tables, but none of the
queries, forms, reports or modules;
- a "front end" (FE) database containing all the queries, forms, reports &
modules;
- the FE should link to the tables in the BE;
- there is a single copy of the BE - on the server;
- *each user* should have their own copy of the FE.

(2) If no users ever edit the same record, why bother fussing with
Recordlocks changes? If you are having multiuser concurrency issues, it
seems to me that you should use Edited Record (or somesuch) - not No Locks.

(3) > There are no workgroups set up.
Access always uses a workgroup file. If you have not set one up explicitly,
Access uses a default one that is created when you install Access.

HTH,
TC
 
1. I've not done any multiuser databases myself. But I thought from other
people's comments, that a split FE/BE approach was necessary to avoid
corruption. Anyone else like to comment on that?

2. There are two forms of locking. Optimistic locking waits until the last
possible moment. This is good because there is no way someone can walk away
& leave the record locked. It is bad because in certain cases, someone
else's changes (to the same record) can be rejected (by Access) when they
try to save them. Conversely, pessimistic locking locks the record at the
first possible moment (eg. when you start to edit it). This is bad because
you can walk away & leave the record locked. But it is good because, no-one
else can start making changes which Access will eventually refuse to save!

The 2k page issue, is as follows. MS Jet v3 (the default database engine for
Access 95-97) used so-called page locking. When you lock a record, every
record on the same 2k internal data page is locked. This is because the
variable-length record structure doubtless made it difficult to get true
record-level locking. Depending on the record size, there might be zero, 1,
a few, or many other records on the same page - and thus, locked at the same
time. Starting with Jet 4 (Access 2000), it is possible to get true
record-level locking. But I am not sure of the details of how to activate
that. It is doubtless in (or not in!) online help, somewhere.

HTH,
TC
 
Thank you for the comments:
1.This database is not split. I am familiar with that and
have used it many times. Mostly for performance issues.
That is not the case here. I do not understand why that
would cause conflicts between users.
2. I agree, so I did not bother with recordlocks. No
Locks is the default. But, for some reason I wonder if
this has something to do with my problem. I have a side
question that relates to this. I do not understand what
is meant by the edited record Record Locks property. The
help item indicates that this locks 2K of data, which can
be more than 1 record. Why would you want to lock a whole
page?
3. OK, But, this should have nothing to do with my
problem?

Any additional comments?? Any other ideas??
 
Back
Top