[AccessD] Record count

I have heard the same about Access.  I've never actually tried it though.
It seems a test could be done to time both on a large table to see which is
faster.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Wednesday, May 25, 2005 2:31 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Record count


Just a side note...

My boss told me, that at least for SQL Server that using
SELECT COUNT(*)
Was better optimized for execution speed than 
SELECT COUNT(somefield)

He was not sure if this carried over to Access though.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Wednesday, May 25, 2005 2:16 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Record count


Thanks everyone. Here is what I use when I need to determine the NUMBER of
records, it just seems like a lot of code to see if there are ANY records.

Dim dbs As Database, rs(1) As Recordset, strQry(1) As String, intCt As
Integer

Set dbs = CurrentDb
strQry(1) = "SELECT Count(tblNewTransCodes.fldDesc) AS CountOffldDesc FROM
tblNewTransCodes;"  Set rs(1) = dbs.OpenRecordset(strQry(1))
        intCt = rs(1).Fields("countoffldDesc")
 If Not (rs(1) Is Nothing) Then rs(1).Close: Set rs(1) = Nothing   
If Not (dbs Is Nothing) Then dbs.Close: Set dbs = Nothing

Jim Hale

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com