Getting Record Count in VB

  • Thread starter Thread starter David F
  • Start date Start date
D

David F

I want to get a record count from a table to use in the VB code of a form
module.

The SQL is simple: SELECT count(*) FROM customers

But how do I assign this to a VB variable?

Something like:

Dim x as integer
X= Execute(SELECT count(*) FROM customers)

Thanks for any ideas.
Dave
 
Try using DCount:

X = DCount("*", "customers")

If you really want to use your SQL statement, you need to open a recordset,
and retrieve the value of the 1st field of the recordset. Using DAO, it
would be something like:

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT count(*) FROM customers")
X = rsCurr.Fields(0).Value
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
 
Thanks Douglas for showing me both methods.



Douglas J. Steele said:
Try using DCount:

X = DCount("*", "customers")

If you really want to use your SQL statement, you need to open a recordset,
and retrieve the value of the 1st field of the recordset. Using DAO, it
would be something like:

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT count(*) FROM customers")
X = rsCurr.Fields(0).Value
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Click to expand...
 
Back
Top