Query delay

[JString]

My form contains a persistent snapshot recordset that is used to create a new
recordset which is in turn applied to a listbox's recordset property. Code
is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made to the
underlying data after the procedure is called immediately following a delete
query. But the procedure does work when a bound command button is clicked.
So it seems like there is some sort of delay following the AllRecords.requery
statement that is preventing the changes from propagating to the new
recordset.

Am I right that there is a delay and if so, how do I get around this problem?

 

[a a r o n . k e m p f]

wow, if Jet is too slow-- then you should upsize to SQL Server.

Jet doesn't have any tools for automating index creation/tuning; so it
makes sense to upsize to a real database that offers these
maintainability features.

 

[Tony Toews [MVP]]

Am I right that there is a delay and if so, how do I get around this problem?

There is indeed a delay but that should only work for other users or
applications accessing the same Jet database.

ACC2000: DBEngine.Idle dbRefreshCache Refresh Is Not Immediate
http://support.microsoft.com/?kbid=202097

So I'm rather puzzled why it takes a bit for the same app to see the
record being deleted. It may have something to do with the various
recordsets based on other recordsets being created. If you were to
create a completely new recordset that might solve your problem.

Please ignore Aaron Kempf's posting as he is rather monomaniacal on
the subject of ADPs and SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/