DCount on a recordset?

[Guest]

In an effort to cut down on the insane amount of time it is taking to conduct
a large number of queries on an approxiamately 6000-8000 record database, I
have created a recordset that represents some common criteria between a
number of the queries so that I can requery from it in a loop and expect
faster results.

What I want to do is run a For Loop that executes a DCount that uses the
recordset as a domain. What happens however is that it always returns "Type
Mismatch". I have tried puting the recordset in quotes (in a vain attempt to
make peace with the offending method) which only resulted in "Jet Database
cannot find a Table named rcFacilityRecSet."

I need a way to get a record count of the records matching a subset of the
Recordset. I am...getting a little frustrated. Is there something that I
am missing?


Cory

 

[Douglas J. Steele]

It's not possible to use DCount with a recordset.

Does the recordset's RecordCount property give you the value you expect?
(Be aware that it's often necessary to move the end of the recordset before
RecordCount is correct)

 

[Guest]

The record count property value would be correct, but I want to count a
subset of the records in the record set. DCount (if it worked) would allow
me to use the Where Clause of an SQL statement to get that subset.

By doing the queries this way I would be able to reduce the amount of time
taken to do queries where several of the criteria are the same. If I just
collect the records that conform to those same criteria first, then query
them on the dissimilar criteria in a loop, I could probably save a good
50-60% of the time. Maybe even more. As it is, the Production Metrics
procedure may just end up being 45-50+ minutes long... I really need to take
a class on VBA and databasing... I have been teaching myself all of this out
of books and these boards, and trial and error and error and error. <Sigh>

Cory

 

[Ken Snell \(MVP\)]

I usually apply a filter to the parent recordset to "filter to the desired
records", and then create a new recordset from the filtered recordset. You
then can get the RecordCount of the new recordset much quicker than looping
through the parent recordset.

 

[Guest]

That would still require creating,opening and closing of a recordset over and
over... But i will check it out.

I was kind of wondering if the requery method would do me any good. Without
the time it takes for Access to open the record set (and thus close it when
it is done) 1400 times, what about setting the source as something new each
time and executing .requery?

I think this weekend I will lounge about and vegetate and let all of this
flow out of my mind so that I can come back fresh on Munday.

Thanks everyone for the suggestions so far!

 

[Steve]

You could create a query that returns the same records as your recordset.
Use DCount on this query to give you the number of records in that domain.
Next create another query based on the first query and write a QuerfDef
routine for the second query that programatically sets criteria for the
second query to return subsets of data from the first query. A DCount of the
second query will return the number of records in the subset of data. Lookup
QueryDef in the Help file.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)