[AccessD] Record count

SQL Server forces a tablescan if a field is named in the count function,
otherwise rows are just counted.


> 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
>