# MS Access Gurus - DCount too slow **URL:** \ **Category:** Factual Questions\ **Created:** [November 28, 2005, 8:57pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104 "2005-11-28T20:57:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [November 28, 2005, 8:57pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/1 "2005-11-28T20:57:00Z")
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! ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [November 28, 2005, 10:32pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/2 "2005-11-28T22:32:52Z")
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 ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [November 28, 2005, 10:38pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/3 "2005-11-28T22:38:04Z")
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. ---
**Author:** ![bizzwire](https://avatars.discourse-cdn.com/v4/letter/b/958977/32.png) [@bizzwire](https://boards.straightdope.com/u/bizzwire)\ **Post date:** [November 29, 2005, 1:39am UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/4 "2005-11-29T01:39:27Z")
can you say: **if** _YourRecordset_.recordcount=0 **then** msgbox(blah, blah, blah)? ---
**Author:** ![BadBadger](https://avatars.discourse-cdn.com/v4/letter/b/22d042/32.png) [@BadBadger](https://boards.straightdope.com/u/BadBadger)\ **Post date:** [November 29, 2005, 10:09am UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/5 "2005-11-29T10:09:05Z")
> [@bizzwire](#): > > can you say: > > **if** _YourRecordset_.recordcount=0 **then** msgbox(blah, blah, blah)? 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. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [November 29, 2005, 10:12am UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/6 "2005-11-29T10:12:12Z")
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. ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [November 29, 2005, 2:30pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/7 "2005-11-29T14:30:51Z")
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, ```auto If Not IsNull (Me![ComboBox1]) Then MyQuery = MyQuery & " AND [Criteria1]= '" + Me![ComboBox1] + "'" End If ``` After the If statements, I build a dynamic query, ```auto Set QD = db.CreateQueryDef("DynamicQuery", "Select * from MyTable" & ("MyQuery" + Mid(where, 6) & ";")) ``` Then later to check for records returned I use, ```auto 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, ```auto Set MyRecordSet = .OpenRecordset("DynamicQuery") MyRecordSet.Movelast If MyRecordSet.RecordCount = 0 Then **Do stuff here** ``` ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [November 29, 2005, 2:48pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/8 "2005-11-29T14:48:25Z")
Something like that, but I think you need: ```auto 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 ``` ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [November 29, 2005, 2:52pm UTC](https://boards.straightdope.com/t/ms-access-gurus-dcount-too-slow/333104/9 "2005-11-29T14:52:09Z")
> [@Mangetout](#): > > Something like that, but I think you need: > > ```auto > > 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 > > ``` I’ll give it a shot - thanks!