Number of records in a query

  • Thread starter Thread starter Simon C
  • Start date Start date
S

Simon C

I need to run a query about 40 times (with different
criteria) and export the results to an excel workbook.

About half of the criteria I'm using will result in the
query returning no records and I dont want these to
produce useless excel files cluttering up my disk space.

How can I use VBA to tell how many records a query returns
or when a query returns no records at all?

Thanks in advance

Simon
 
Thanks Scott
-----Original Message-----
You didn't say which version of Access you're using, so we'll go through
both.

Access 97 uses DAO, and a DAO Recordset returns a .RecordCount property. So,
you can simply check this property:

If rst.RecordCount >0 Then
<<do something here
End If

If Access 2000 or greater you _may_ be using ADO, which may or may not
expose a recordcount property, depending on which CursorLocation you
selected when opening your recordset. You must use either adOpenDynamic or
adOpenKeyset to return a valid recordcount:

rst.Open "SQLHere",connect,adOpenDynamic (or adOpenKeyset), lockoptions

Then use the same syntax as above to check for records. The problem with ADO
recordsets is that they default to a ForwardOnly cursor, which doesn't
perform a recordcount since doing so would require the recordset to move to
the Last record, and since this is a forward only cursor, well, you'd be at
the end with no place to go <grin>!! The OpenDynamic and OpenKeyset allow
you to move about in the recordset, which DOES allow the recordset to
navigate to the end and back to report the count.




.
Click to expand...
 
Back
Top