Want a warning up front

  • Thread starter Thread starter Mel Brown
  • Start date Start date
M

Mel Brown

We have an Access database with multiple table of dynamic
data. Three user can access the tables at any time and
enter data - even against the same record in the same
table. Is it possible to warn the second and subsequent
user that updates are being made before they make their
changes? As is, this warning is only given when a
subsequent user trys to leave and save the database.
Does each user have their own image of the database or are
records updated immediately when the user leaves the
record.

TIA
 
I don't know if it will work for you, but what I tend to do is to first
split the database into a backend (the dynamic data only) and front ends for
each user with any static data and the forms, queries and reports. Each
user may have the same forms and reports or different ones.

For additions and updates I have a table on each machine that holds the
changed data. It only needs to have the key field and those fields that the
user may be changing or adding. Then at the end of the session or on some
sort of schedule, an update query is run updating the back end database with
the data from the local front end database.

Much fewer conflicts occur.
 
All multi-user databases (that are worth the name) have automatic checks for
the case where two users try to edit the same record at the same time.

Access does it in one of two ways.

Optimistic locking: Both users can type away to their heart's content, but
only the first one who tries to save the altered record, will succeed. The
second user gets a message to the effect that some-one else has changed the
record, & he must start again (eg. re-query the record & re-type his
changes).

Pessimistic locking: The first user who starts to edit the record, can do
so. Any other user who tries to edit that record will be warned, the instant
that he tries to do that, & he will have to wait for the first user to save
his changes (or discard them).

Pessimistic locking is GOOD in that it avoids the case where the second user
spends 10 minutes changing a record, only to have his changes rejected. It
is BAD in that if the first user starts typing but then goes to lunch
(without saving the record), the record is locked until he returns.
Optimistic locking is good for the opposite reasons.

If conflicts are rare, go with optimistic locking. If conflicts are common
(eg. several users often try to edit the same record), pessimistic may be
better. You can select which method you want, using the form LockEdits(?)
property (I don't have Access here to check. Try Help, if that is not the
right property).

Be aware, that Access versions up to & including Access 97 actually lock
"pages" - not "records". A page can contain part of a record, one record,
several records, or perhaps *many* records, depending on various factors -
primarily, the amount of data in each record.

If *any* record in a page is locked, *all* records in that page are locked.
This can be a problem if your database has small records (so there are many
records per page), and heavy multi-user edits. A user editing record 'A',
can lock-out another user who is trying to edit record 'B' - a different
record, but in the same page.

Phew!

HTH,
TC
 
Joseph Meehan said:
I don't know if it will work for you, but what I tend to do is to first
split the database into a backend (the dynamic data only) and front ends for
each user with any static data and the forms, queries and reports. Each
user may have the same forms and reports or different ones.

For additions and updates I have a table on each machine that holds the
changed data. It only needs to have the key field and those fields that the
user may be changing or adding. Then at the end of the session or on some
sort of schedule, an update query is run updating the back end database with
the data from the local front end database.

Much fewer conflicts occur.
Click to expand...


Joseph, I have to take issue with this!

What happens when two users edit their own copy of the same original record,
then "at the end of the session or on some sort of schedule", both users'
changes are updated back in to the original record?

Now there is *no* control over whose changes are saved, & whose changes are
overwritten, & there is no warning to either user that this has occurred.

So it is a bit like seeing smoke come out of your engine, & fixing this by
throwing a blanket over it. "The smoke has gone away, so everything must be
ok now!"

TC
 
TC said:
Joseph, I have to take issue with this!

What happens when two users edit their own copy of the same original
record, then "at the end of the session or on some sort of schedule",
both users' changes are updated back in to the original record?

Now there is *no* control over whose changes are saved, & whose
changes are overwritten, & there is no warning to either user that
this has occurred.
Click to expand...

Not from my experience. Access, at least with the version I am using,
does alert the user to the problem. This may be because I am using 97. I
really want to thank you for letting me know that the paging issue has
changed in later versions. While I doubt if I can talk the powers to be
into updating Office (It seems they consider Access nothing more than a poor
man's Excel), at least I know about it. I also want to thank you for
explaining the mistery of paging much better than I could have. You even
had a few facts about it I was not aware of. Explains a few odd results I
have had from time to time.

You might also note that I started off with "I don't know if it will
work for you" for some of the very reasons you pointed out. It works for me
for those situations I have used it. It will not work for everyone. I
should have made that more clear.
 
this follows up what TC said about Access locking pages
not records...

A way you can force Access to lock single records by
creating record sixes that are larger than half a page,
larger than 1024 bytes. This works because Access won't
store a new reocrd on a partially filled page if it can't
fith the entire record on that page.

Create dummy field on your table that will bump it to the
1024 bytes size. This will do the trick
 
As long as the field is populated with actual data (eg. spaces) - otherwise
it only takes a byte or two, not the specified maximum size.

TC
 
Back
Top