Select Count(*) of a table and return the count

[DebHanleyRI]

I need to count the number of records in a table before I process the records - If there are more than 100 records, I need to send an email.
This is my function, what I thought would work, but it returns cntAcks = -1
Public Function getAck() As ADODB.Recordset

'Get all of the acks from the ack table.
Dim rst As New ADODB.Recordset
Dim sql As String
Dim cntAcks As Integer

sql = "select count (*) from Ack as [cntAcks]"
rst.Open sql, con, adOpenDynamic, adLockOptimistic
cntAcks = rst.RecordCount

Set getAck = rst

End Function


not all who wander are lost....

 

[JayKusch]

sql = "select @cntAcks = count(*) from Ack"
rst.Open sql, con, adOpenDynamic, adLockOptimistic
cntAcks = rst.RecordCount


Thanks

J. Kusch

 

[amorous]

First thing:

Change your query to

sql = "select count (*) as [cntAcks] from Ack"

Then when you do this below line

rst.Open sql, con, adOpenDynamic, adLockOptimistic

rst("cntAcks") is the count value you are looking for.

-VJ