Originally Posted by wscheiman

Here's another option that I use quite often using a field alias:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT COUNT(*) As RecordCount FROM tblProducts"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)

intResult = rs("RecordCount")

rs.Close
db.Close


Utilizing an alias can be beneficial when running these types
of queries. You can use any value you'd like for the alias
name as long as it follows the rules for column names in a
table.

If you are using VBA within Access you can also use a domain
function. Three of the most used as called DLookup, DCount,
and DSum. In this case, DCount() will give you the record
count like this:

intResult = DCount("ProdID", "tblProducts", "")

Here, you can use any valid existing column name from the table
in the first argument.

Both options should do exactly what you need. If you haven't
already, you may want to look in MS-Access online help for more
info on these 3 domain functions listed above (there are others
as well starting with D*) - I still use them quite extensively
myself in end-user delivered applications.

Best Wishes and Good Luck.
:D