Re: Waiting for a recordset to finish fetching

JoelB wrote:
I have one form kicking off another, and passing a record ID to the
latter as a parameter. The popup in turn makes a clone of its own
recordset, and then does a find on the clone using bookmarks to move
the user to the desired record. Pretty standard stuff.

Well, sometimes users are complaining that it's not finding the
record.
What I think is happening is that it is that the find is executing
before all of the records are retrieved from the database. To prove
it, I logged debug messages using the recordset.state property, and
sure enough, the times it failed, the state was 9 and the times it
did not fail the state was 1.

So, I added this code, right before the find:

Do While rs.State <> adStateOpen
' (nothing)
Loop

However, within the loop, the state never changes. I actually
included a timer to prevent an endless loop, and when I add a
debug.print inside the loop, even when I set it for 15 seconds it
still stays in the fetching state (9) for the full period. I guess
the fetch is stalled while loop is running.

Can anyone suggest an alternate way to wait until the recordset is
built before doing the find?

The platform is MS Access.

TIA,
Joel

Are you using a single connection?
Unless you are using an asynchronous connection, you should not be running
into this problem, unless you are using a single connection to try and open
a recordset while the connection is still dealing with another recordset.
You can avoid this by using a client-side recordset and disconnecting it by
setting its ActiveConnection property to Nothing, leaving the connection
free to deal with a new recordset.

It doesn't sound like it but you may be running into the Jet dalayed-write
optimization:
single connection:
http://support.microsoft.com/?kbid=240317

two connections:
http://support.microsoft.com/kb/200300
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)
  • Re: Problem: MDB-Datenbank Zugriff über das Intranet
    ... Wie und wo öffnest Du Deine Connection zur *.mdb? ... > End With ... Du öffnest ein Recordset mit adOpenForwardOnly. ... Datensatz liegenden Datensatz zuzugreifen. ...
    (microsoft.public.de.vb.datenbank)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Can Not Create Control
    ... > Private Sub Form_Load ... > Dim laco As Connection ... > Dim lars As Recordset ...
    (microsoft.public.pocketpc.developer)