Matching records table in a multi-user environment

[Jim Pockmire]

I use a matching records table to select records in a query. This is a
multi-user environment where all objects are in a shared database.

1. Is it possible for someone to overwrite the matching records table while
I have the database open, such that my query would produce incorrect
results?

2. If so, is there an alternative that will assure that my query runs
correctly? Splitting the database is not an option, nor is opening the
database exclusively.

 

[Tom Ellison]

Dear Jim:

It certainly is just as possible for someone to change a row in the
table while you are looking at it, asuming it is possible for someone
to change it when you aren't looking at it. This does not mean the
query has produced incorrect results. The results were correct as of
the moment the query was run.

If you put the results of the query in a form, you could refresh the
form using a timer, perhaps every 10 or 20 seconds.

You can even check to see if the data has changed and give an audible
and/or visual warning that this is the case. If the data that has
changed has scrolled off the screen, then the user may have looked at
it and made not of its state. It may be best to inform the user.

None of this begins to approach the problem of having someone change
the data AFTER you have finished using it. If you look at the data
and make note of something about the data, then close the query, it
may change in the next second. What is the difference in having the
data change WHILE you are looking at it compared with having it change
one second after you finish looking at it? Usually, there is NO
difference.

If it is important that you be able to associate data with the exact
moment you looked at it, then a transactional approach is best, in
which everything is posted with the date and time it was posted, and
NEVER CHANGES. Any corrections must be done by adding additional
postings, which can include cancelling the already posted data as of
some later date/time. An approach like this may be what you would
want. You can come back and look at the data at any time, given that
you desire to see it as of some specific prior date/time. It will
always come back the same.

Splitting the database has nothing to do with this issue. In order to
have a good reliable system, the database MUST be split. This is not
an option.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I use a matching records table to select records in a query. This is a
multi-user environment where all objects are in a shared database.

1. Is it possible for someone to overwrite the matching records table while
I have the database open, such that my query would produce incorrect
results?

My guess is that you have a Table "tblSelected" that simply store the
RecordIDs of the Records to be selected from the (real) Source Table?

In that case, it certainly can when the other user starts the same process
just after you saved the RecordIDs.

2. If so, is there an alternative that will assure that my query runs
correctly? Splitting the database is not an option, nor is opening the
database exclusively.

Possibility: Create a second Field "ActioningUserID" in the Table
"tblSelected" using either the AccessUser (using Access Security) or the
Windows LogInID to distinguish Records (in tblSelected) belonging to a
particular User. Use the AccessUser / LogInID always in transactions to the
Table tblSelected and in your Query to select the Records from the (real)
Source Table.