Need alternative to a Busy Wait (Database is too slow)

  • Thread starter Thread starter Warren
  • Start date Start date
W

Warren

This post might be long, so please bear with me.

My goal is to export a report to RTF.
In the same function, I:
1) execute an Insert query
2) retrieve the Autonumber associated with the Insert
3) create a SQL statement to be used as the RecordSource
for my report
4) run the DoCmd.OutputTo function to export the report to
RTF format

I have done some testing, and realized that the report is
often Empty because the information that is supposed to be
used to generate the report (i.e. Insert statement from
Step1) is not yet "committed" to the database.

Between Step 3 and Step 4, I have added a busy wait using:

Do While rs.RecordCount < 1
Set rs = CurrentDB.openRecordset("Select statement")
Loop
(this is a VERY bad implementation, but the only
implementation that works for me)

My function now works, and the report is fine, but I am
worried about using the Busy Wait.

Any alternative suggestions?

Thanks
Warren
 
Warren, the wait may be unnecessary.

When you first open a DAO recordset that is a Dynaset (the default type if
you use a SQL statement or linked table), the RecordCount contain only the
number of records accessed so far. That will be 1 when first opened (or 0 if
there are no records). You will not have an accurate recordcount until you
MoveLast.

Hope this saves you a long weight! :-)
 
Allen, thanks for the reply, but that doesn't fix the
issue.

Consider the following abbreviated code (assume I have
clicked the button called "cmdButton")
(assume using DAO)
-----------------------------------------------
Private Sub cmdButton_Click(Cancel as Integer)
'Insert a Records into the table Table1

CurrentDB.Execute("Inserto into [Table1] (Field1) Values
(1);")
(assume Field1 is an identity field)

Call CheckRecords

End Sub

Public Sub CheckRecords()
Dim rs as Recordset, SQL as String
'Look for the record from above
SQL = Select * from Table1 Where Field1 = 1;
Set rs = CurrentDB.OpenRecordset(SQL)
msgbox Rs.Recordcount '(this value Always shows 0)

Do While rs.Recordcount < 1
set rs = CurrentDB.OpenRecordset(SQL)
Loop

msgbox Rs.RecordCount '(this value alway shows 1)
End Sub

So, the problem is that when I first query the table I
want, the record I am looking for is not there, and I have
to make the procedure wait (Busy wait, using Set rs =... )
until the record is there.
 
Hi Warren.

Try:
Private Sub cmdButton_Click(Cancel as Integer)
With dbEngine(0)(0)
.Execute "INSERT ...;", dbFailOnError
Debug.Print .RecordsAffected & " record(s) inserted."
End With
Debug.Print "Now checking records."
Call CheckRecords
End Sub
Public Sub CheckRecords()
Dim rs as DAO.Recordset, strSQL as String
strSQL = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing
End Sub

The code should demonstrate that the INSERT completed (i.e. the Immediate
Window shows "1 record(s) inserted." before the call to CheckRecords is
executed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Warren said:
Allen, thanks for the reply, but that doesn't fix the
issue.

Consider the following abbreviated code (assume I have
clicked the button called "cmdButton")
(assume using DAO)
-----------------------------------------------
Private Sub cmdButton_Click(Cancel as Integer)
'Insert a Records into the table Table1

CurrentDB.Execute("Inserto into [Table1] (Field1) Values
(1);")
(assume Field1 is an identity field)

Call CheckRecords

End Sub

Public Sub CheckRecords()
Dim rs as Recordset, SQL as String
'Look for the record from above
SQL = Select * from Table1 Where Field1 = 1;
Set rs = CurrentDB.OpenRecordset(SQL)
msgbox Rs.Recordcount '(this value Always shows 0)

Do While rs.Recordcount < 1
set rs = CurrentDB.OpenRecordset(SQL)
Loop

msgbox Rs.RecordCount '(this value alway shows 1)
End Sub

So, the problem is that when I first query the table I
want, the record I am looking for is not there, and I have
to make the procedure wait (Busy wait, using Set rs =... )
until the record is there.


-----Original Message-----
Warren, the wait may be unnecessary.

When you first open a DAO recordset that is a Dynaset (the default type if
you use a SQL statement or linked table), the RecordCount contain only the
number of records accessed so far. That will be 1 when first opened (or 0 if
there are no records). You will not have an accurate recordcount until you
MoveLast.

Hope this saves you a long weight! :-)
Click to expand...
Click to expand...
 
Back
Top