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