Query takes to long

[Werner]

Hello,

In the code below the query takes to long to execute. Is it possible to wait
for the query to be executed?



'Option Explicit
Option Compare Database

Sub SimultaneousConnections(StartDate As String, EndDate As String)
'Datum December 2007.
'Deze methode telt het aantal gelijktijdige gesprekken. De Starttijd en
eindtijd van ieder gesprek wordt onthouden.
'Het aantal gesprekken dat actief is wordt aan iedere stempeling mee gegeven.
'Todo: telling per Gateway invoeren.

Dim qry As String
Dim whereclause As String
Dim rs As Recordset

If CheckParam(StartDate, EndDate) Then
If (StartDate = "" And EndDate = "") Then
whereclause = ""
Else
whereclause = "Where Date Between #" & StartDate & "# AND #" &
EndDate & "#"
End If
Else
MsgBox ("Incorrect date provided.")
Exit Sub
End If

qry = "Select * From 0711Tel " & whereclause

Set rs = CurrentDb.OpenRecordset(qry)
If rs.EOF Then ' it's empty
NumRecords = 0
Else
'There is at least one record in the recordset
With rs
'Move to the end to ensure we have an accurate record count
.MoveLast
NumRecords = .RecordCount

End With

MsgBox NumRecords

End If

rs.Close

End Sub
Sub test()

Call SimultaneousConnections("17-12-2007", "17-12-2007")

End Sub
Private Function CheckParam(StartDate As String, EndDate As String) As Boolean
'Controle of de opgegeven data wel echt data zijn
CheckParam = True
CheckParam = IsDate(StartDate)
CheckParam = IsDate(EndDate)

End Function

 

[Tom van Stiphout]

On Sat, 22 Dec 2007 06:04:00 -0800, Werner

The code counts the number of records between the given startdate and
enddate. It does that very inefficiently, by opening a recordset and
moving to the last record to get an accurate count.
It's better to use a saved query, and then in code supply the
parameters.
The most benefit you're going to get from running a Totals query like
this:
Select count(*) From 0711Tel
where [Date] between [parStartDate] and [parEndDate]
Let's say you save this query as "qryCountConnections"
Set the parameters for this query to be of Date type.
Note also how I write [Date] because Date is a reserved word. It is
not a great name for a field.

Now alter your code:
Sub SimultaneousConnections(ByVal StartDate As Date, ByVal EndDate As
Date)
dim qd as dao.querydef
set qd=currentdb.querydefs("qryCountConnections")
'set the two parameters
qd!parStartDate=StartDate
qd!parEndDate=EndDate
'open a recordset to retrieve the one and only value.
set rs=qd.openrecordset(dbopensnapshot)
NumRecords=rs(0)
rs.close
'etc.

-Tom.