sql to count how many rows in a table

[jamesmills]

Hey people i need to find out what the sql code is for counting how many rows there are in a table.

Thanks

 

[travisbrown]

COUNT(*) FROM tbl;

 

[jamesmills]

Yer i got it in the end! How thick am i!

Dim rs As Recordset
Dim finalDate As String

Set rs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM tblCustomers&quot
finalCount = rs
rs.Close

lblTotalCustomers.Caption = finalCount

Any idea why this will not work?

 

[mikevh]

You're trying to assign rs, which is a recordset
object, to "finalcount", which apparently isn't
even declared. (You have a "finaldate".) I
recommend you put "Option Explicit" in the declarations
section of your module to force declaration of all
variables.

You didn't mention you were trying to do this in code.
Since you've got a recordset object, you can get the
count from its RecordCount property, no need for SQL.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCustomers")
    cmdUpdate.Caption = rs.RecordCount
    
    rs.Close
    db.Close