SQL Recordset

[MK]

Hello

I want to know the number of records that an SQL statement will produce. I have been told that it is best to feed the SQL into a recordset and then look at the record count of that

I've not been able to get the code to work in VBA, so could someone please help me

Many thanks

MK

 

[Chris Nebinger]

You probably don't want to open a recordset. The only way
to get an accurate count that way is to open the
recordset, scroll to the end, then get the count. This is
alot of overhead.

Instead, use the Count SQL Statement along with the SQL
code:


set rst = currentdb.openrecordset("Select Count(*) from
[Select ... Your statement) as Recordset")

intRecordCount=rst(0)



Chris Nebinger

-----Original Message-----
Hello,

I want to know the number of records that an SQL

statement will produce. I have been told that it is best
to feed the SQL into a recordset and then look at the
record count of that.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could also use the DCount() function:

dim lngRecords as Long
lngRecords = DCount("*","query name")
debug.Print lngRecords

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI62d4echKqOuFEgEQJjngCguJacJ7q/QRb0fEjM5TVG+r50uQ4AoKjK
KrXYuzrsmfjQh7Z1EkEzTT5W
=JKoz
-----END PGP SIGNATURE-----