MS Access Gurus - DCount too slow

Factual Questions
1

I’m currently using a “Query” form to build a dynamic (select) query which is then used to populate a “Results” form.

I want to ensure the query has returned records before displaying the results, and if no records are returned open a msgbox stating “No Records Found” and return to the query form.

I’m using DCount to see if the dynamic query returns any records, and it all works just fine, but it’s really slow. Is there a good alternative to DCount? I just want to see if any records are returned.

Thanks in advance!

2

How are you building the query?; it should have a recordcount property somewhere (although you may have to ensure it is updated by doing)
[recordset].Movelast
[recordset].Movefirst
DoEvents

3

Another option (thought a bit inelegant) is to build another query in parallel with the main one, but make it a summary query, counting the rows using Sum(1) AS count as one of the field expressions.

4

can you say:

if YourRecordset.recordcount=0 then msgbox(blah, blah, blah)?

5

Thats pretty much how I’d do it. I generally have the recordset open and get a count with the recordcount property of the recordset. You have to run the query in some shape or form, so there will always be a delay.

6

As I mentioned above; the recordcount property can be inaccurate if you read it while the query is still running (most likely to be the case with large or complex results); MoveLast>MoveFirst>DoEvents>RecordCount forces it to complete execution of the query before returning the record count.

7

How I’m building the query:
The query form looks at each of the controls and has a set of if statements to the effect of,


If Not IsNull (Me![ComboBox1]) Then
MyQuery = MyQuery & " AND [Criteria1]= '" + Me![ComboBox1] + "'"
End If

After the If statements, I build a dynamic query,


Set QD = db.CreateQueryDef("DynamicQuery", "Select * from MyTable" & ("MyQuery" + Mid(where, 6) & ";"))

Then later to check for records returned I use,


MyCount = DCount("*", "DynamicQuery")

So the statement above is the first place I actually run the query. This is the step that’s slow. I was told that functions like DCount are slow and should be avoided, but I’m not sure how else to do it.

Mangetout, are you saying I could do something like,


Set MyRecordSet = .OpenRecordset("DynamicQuery")
MyRecordSet.Movelast		
If  MyRecordSet.RecordCount = 0 Then
**Do stuff here**
8

Something like that, but I think you need:


Set MyRecordSet = .OpenRecordset("DynamicQuery")
MyRecordSet.Movelast
MyRecordSet.MoveFirst     '(otherwise we might trip over an EOF later on)
DoEvents                  '(ensures that Windows has finished processing
                          'the event queue - may not be strictly necessary)

If  MyRecordSet.RecordCount = 0 Then
Do stuff here
9

I’ll give it a shot - thanks!