SELECT COUNT(*)

[ksutton]

What is the best way to return a Count of Records that match criteria in a DB?

The only way I know of is to Open the Recordset, MoveLast, and use the RecordCount Property...

Is there a way that I can do a Conn.Execute(SQLStatement) that just returns a Count? i.e. SELECT COUNT(*) FROM Table WHERE Active = 1, etc....

Thanks,
Kevin

[Gen-X]

Here is an example for ADO.

(Unfortunately you cannot use Execute because that is for what they call ACTION queries which mean they must perform an insert, update or delete)

Set rstCount = dbDatabase.OpenRecordset("SELECT COUNT(*) as NumRecs FROM TableName WHERE ........",adOpenSnapshot, adReadOnly, adReadOnly)

If rstCount.recordCount <> 0 Then
  iNumRecords = rstCount!NumRecs
Else
  iNumRecords = 0
End if

rstCount.close

[asad_s23]

Dear..
Try this code..

dim cn as new adodb.connection
dim rs as new adodb.recordset

cn.open(your_connect_string)
set rs = cn.execute("SELECT num = COUNT(*) FROM TABLE WHERE XYX..",cn

dim intNumRecords
intNumRecords = rs.fields("Num")

now intNumRecords will hold the number of records

[JHausmann]

intNumRecords = rs.fields(0) works as well, the advantage being you don't have to specify an abritrary name to SQL to make it work...