"'69 Camaro" AM
wrote in :

I've encountered occasions (rarely, mind you) where the last chunk
of record changes hadn't been written to disk yet and the next
query errored because it couldn't find the data that I was
expecting to be there already. That's the problem with a desktop
database engine as opposed to a client/server database engine.
The client/server does these operations in memory and has a
snapshot of the data that includes the results of the previous
data manipulations and uses that snapshot for subsequent
operations which read from the snapshot, not from the disk, while
the database engine writes to disk the earlier transactions. (I'm
not sure I'm explaining this very clearly. Sorry.) The desktop
database engine doesn't have a snapshot to read from and always
reads what's already been written to disk. The lack of ability to
do these "will be done" operations in memory is one of the reasons
one sees lightning speed in client/server database engines and Jet
seems to take its sweet time.

I've never seen such a thing.

Jet *does* use memory, in the form of temp files on disk. And Jet
keeps track of which are the currently valid data pages, so it knows
that a data page in the MDB file has been superseded by a data page
in the temp file. Surely this is kept track of in an in-memory list,
which all query processing will access in order to get the latest
data. And it shouldn't matter if it's actually been written to disk
or is just in the disk cache queue to be written, since the disk
cache should also be keeping its own list of what is to be read from
disk and what sectors from the cache (because they haven't yet been
flushed to disk).

I would suggest that the problem you're described is caused by a
number of possible problems:

1. using a different connection/database variable for the second
query than for the first.

2. running them in separate transactions without commiting the first
before running the second.

These seem to me like the kinds of problems that could lead to a
query not seeing the updates made in the immediately previous query.
I guess the one thing that *may* happen is something about the disk
writes that DoEvents allows, but I think that's very unlikely, as
disk I/O should be running at a level much higher than Access
itself, so ought to happen anyway. Perhaps it frees up something in
Access itself to communicate with the OS in a way that makes the
write happen.

In all my years of programming in Access, I've never encountered
such a problem, and I've written lots of code where queries are
executed successively and later ones depend on writes that happened
in the earlier ones. I'd be interested to know what conditions lead
to this problem, since I've never run onto it. Perhaps ADO is the
culprit (I don't use ADO)?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/