Access table row count in VBA

[johncb]

Hi all,
Advice please on how I can, in VBA code, return a row count into an integer variable.
e.g.
======================
Sub something
dim dbs as variant
dim intRows as integer
dim strSQL as string

set dbs = currentdb
strSQL = "SELECT Count(*) AS intRows FROM tblX WHERE tblX.name = 'smith';"
dbs.execute strSQL
end sub
=============
I need to be able to get the count into 'intRows' but this code does not work.

Any help gratefully received.

John

 

[jtmach]

If you get a recordset rather then doing an execute query as shown below you can get the recordCount.

    Dim db As Database
    Dim qry As QueryDef
    Dim rst As Recordset
    Set db = CurrentDb
    Set qry = db.CreateQueryDef("", "SELECT * FROM tblX")
    Set rst = qry.OpenRecordset()
    if rst.eof = false then
         intRows = rst.RecordCount
    end if
    'make sure that you set the recordset to nothing or you will have a memory leak
    set rst = nothing

The hardest questions always have the easiest answers.