Exporting Query - Want count of records

[Tokyo Alex]

Dear all,

I have a query set up and I export the results into a CSV file using
DoCmd.TransferText.

Sometimes this query returns no records. In that case I don't want to
export the CSV.

I can think of a couple of ways to do this:
1) Create a recordset object on the query, check if there are any records
and if so go ahead and export. However, this means running the query twice
(once to check recordcount and once to export) which is not efficient.

2) Change the query into a make-table query, use OpenQuery, export the
resulting table if rowcount is not 0 and then delete the table. This is a
bit clunky, but would work reasonably well (except that I may want at some
point to run the same query without making a table).

Can anyone think of a more elegant way to achieve this? Any suggestions
would be appreciated.

Thanks in advance,
Alex.

 

[Jeanette Cunningham]

If there is a primary key involved,
you can use a saved query (created for this purpose) with the primary key
and enough fields to allow matching against the where clause of the query
you are going to export.

Use the where clause to do a DCount on the saved query.

Dim lngCount As Long
Dim strWherre as String

lngCount = DCount("*", "TableName", strWhere)
strWhere is the where clause from the export query.

The "*" allows access to use the indexed primary key field to very quickly
count the records in the saved query.

If lngCount <=0, then you know not to do the export.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Tokyo Alex]

Hi Jeanette,

Thanks for the response.

Just to clarify:
The saved query (let's call it qdfForRecordCount) should have the primary
key fields from the tables my export query uses, plus any fields needed to
evaluate the WHERE clause, but shouldn't itself have a WHERE clause?

lngCount = DCount("*", "TableName", strWhere)

I'm guessing this should be DCount("*", "qdfForRecordCount", strWhere)?

My export query is a 'LEFT JOIN ... WHERE ... Is Null' job to identify
records with no match in a separate table, but as long as qgfForRecordCount
includes the LEFT JOIN, it should work anyway shouldn't it?

One question though; when DCount evaluates the query records against
strWhere to get a count, is it any quicker than running the same query with a
WHERE clause and then using DCount on the results?

Thanks again,
Alex.

 

[Jeanette Cunningham]

If the query you are exporting is a saved query ( and not a sql string
created in code), then you can just do the DCount on the export query.
You only need one primary key field or an indexed field for the DCount.
You can set up the saved query for the dcount check so that it already has
the left join and the IsNull criteria in it.
Then you just use the other criteria from the where clause on your form as
needed.

For example if your form was exporting data for a record where the ClientID
was 256 and the OrderDate was Tuesday last week,
you would use the ClientID and the OrderDate to build the strWhere to do the
DCount on the saved query (which already has the left join and the IsNull
criteria).


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Spencer]

Jeanette,
Perhaps I am mis-interpreting what you are saying.

Although an indexed field might make the DCount faster, it is not a
requirement when using DCount.

Tokyo Alex,
Method 1 while a little clunky is probably going to be faster than any other
method.

Method 2 is a bad choice in that a make table query is going to be a lot
slower than creating a recordset and seeing if there are any records. Plus
using the table is not really going to be much faster (if any faster) than
re-running the query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tokyo Alex]

Hi Jeanette and John,

Thanks for the advice.

In the end, I went with DCount (less typing ). Works very well.

One day, I'll have to actually time the different methods, though.

Thanks again for the help,
Alex.