Inserted rows cannot be immediately selected (A97 DAO)

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

(Access 97)

I have a situation where, depending on how data is read, rows inserted into
a table are not available for select until several seconds after the inserts
are finished.

Has anybody seen something like this before? Any ideas why I would get a
delay between the time when data is inserted and when it can be selected?

More info below.

Thanks!
Lee


More Info
==============

Steps:
(1) Import data from text file into table (140 rows)
(2) Add about 100 more rows to the table using
recordset.AddNew ... recordset.Update
(I close the insert recordset when done.)
(3) Query the data

I need to add a 3-second delay between steps 2 and 3, otherwise the select
in step 3 sees only the data imported in step 1, but not any data inserted
in step 2.

For diagnostics I have added calls to DMax() and DLookup() between steps 2
and 3. Strangely, these calls see all the data even when the select
statement that follows them does not.

Notes:
* The table is a link to an Access table in a separate .MDB file.
* Table connection is: ";DATABASE=(path)\MRData.mdb"
* I am not using replicated databases.
* I am not doing any asynchronous processing (as far as I know!)
* I am not using transactions.
* I tried using transactions as a possible fix -- no help.
* Both the code MDB and the data MDB reside in same folder.
* Both .MDB's are on my local drive.
* I have re-installed Access 97 SR-2, and Jet 3.5 SR 3
* I have repaired and compacted (again and again ...)

Code summary
============================================================
============================================================
''
'' Import 140 rows from text file for User "A".
'' Largest value of ItemNum field is 6
''
DoCmd.TransferText acImportDelim, "ImportV1", "tblData", _
strImportFile, HasFieldNames:=True

Set db = CurrentDB()

''
'' Insert an additional 100 rows
'' Largest value of ItemNum field inserted is 11
''
Set recIns = db.OpenRecordset("tblData", dbOpenDynaset, _
dbAppendOnly)
recIns.AddNew
recIns!UserID = "A"
recIns!ItemNum = intItemNum ' (ranges from 7 to 11)
etc...
recIns.Update
.... Another 99 rows ...

recIns.Close: Set recIns = Nothing

''
'' The following always returns 11 (ok)
'' (ItemNum is an integer data type that does not allow NULLS)
''
varMaxRowDiag = DMax("ItemNum", "tblData", "UserID = 'A'")

''
'' Without the delay loop, the following returns 6 (incorrect)
'' With the delay loop, the following returns 11 (ok)
''
'' (A shorter loop than below is not sufficient.)
''
varWait = DateAdd("s", 3, Now())
While varWait > Now()
Wend

Set recData = db.OpenRecordset("SELECT MAX(ItemNum) As MaxRow" & _
" FROM tblData" & _
" WHERE UserID = 'A'", _
dbOpenForwardOnly, dbReadOnly)

============================================================
============================================================
 
Data written to the database is cached locally, then
written to the mdb in a background thread.

Each database connection is cached: each connection
is separate.

You can force an immediate write ('synchronous') by
using explicit transactions or by telling Jet that
you want implicit transactions to be synchronous.

If there is only one user, a better way is to make
sure that the Write and the Read both use the same
connection. For example, you may DAO to Write to
a RecordSetClone so that the Form can see the data
immediately. Or you may use an UnBound Form so that
you can use DAO to Read the data from an existing
connection.

(david)
 
David,

Thanks for the reply, you have certainly pointed me in the right
direction....however something still seems amiss.

I had suspected caching and had already tried using an explicit transaction,
but it did not do the trick I did stumble across a solution that works, but
I still don't quite understand *why* it works, so I will toss out a couple
more items to you and the group.

In particular, why would the DMax() call at line (9) below see the
newly-inserted data, but the OpenRecordset at line (10) would not? To me,
there doesn't seem to be anything to indicate that (4) and (9) share
anything that is not also shared by (10).

Also, I discovered that removing the dbReadOnly from (10) below *made
everything work*. That's great, but why?

Note that I see the same exact behavior with or without the transaction.

The code, with the transaction, follows.

( 1) Set db = CurrentDB()
( 2) Workspaces(0).BeginTrans
( 3)
( 4) Set recIns = db.OpenRecordset("tblData", dbOpenDynaset, _
dbAppendOnly)
( 5) >>> Perform 100 Inserts via recIns.AddNew / recIns.Update <<<
( 6)
( 7) Workspaces(0).CommitTrans
( 8) recIns.Close: Set recIns = Nothing
( 9) varMaxRowDiag = DMax("ItemNum", "tblData", "UserID = 'A'")
(10) Set recData = db.OpenRecordset("SELECT MAX(ItemNum) As MaxRow" & _
" FROM tblData" & _
" WHERE UserID = 'A'", _
dbOpenForwardOnly, dbReadOnly)

Still, (9) saw all the rows, and (10) did not.

* I verified that the registry has Jet 3.5\UserCommitSync = yes
* I verified that Jet is honoring the transaction. (I replaced the
..CommitTrans with .Rollback at (7) and saw all the inserts roll-back OK.)

On a whim I tried removing the dbReadOnly option from the OpenRecordSet at
(10). With that change, (10) saw all the rows just fine even without the
explicit transaction.

If anyone has any more insights, or can point me towards some technical
reference on how Jet manages these connections and caches, I love to hear
back.


Still dazed and bewildered, but no longer panicked,
Lee
 
You may try
application.dbengine.idle dao.dbRefreshCache
and
CommitTrans dao.dbForceOSFlush

But in the situation you describe, I don't have
any trouble getting the most recent data.

(david)
 
Back
Top