"adm" <ad*****@yahoo.com> wrote in news:1113335567.986886.304740
@o13g2000cwo.googlegroups.com:
There are a few ways to go about this, but what is the fastest when
called from VBA? This if for an ADP with a SQL Server back-end.
I think this depends to some extent on how frequently there will be some
records (and how many), as the answer to a small extent depends on how many
records.
If the table is populeted:
If the table has a primary key then I think the fastest way to count the
records in it (ADP-MS-SQL) may be:
n = CurrentProject.Connection.Execute("SELECT rows FROM sysindexes WHERE
name = 'NameOfPrimaryKey'").Collect(0)
This may be as many as 2 milliseconds faster than:
n = CurrentProject.Connection.Execute("SELECT Count(*) FROM
TableName").Collect(0)
n=DCount("*", "TableName") seems to be nine times slower than the
previously mentioned methods.
On a remote MS-SQL DB typical tick times were:
Primary Key: 4700
Count: 4750
DCount: 41000
for 50 iterations.
for a table with 594 records.
BUT ...
If the table has no records then Primary Key speed stays the same, while
Count and DCount speeds are reduced by about 10%.
....
so if the table had a primary key and might at times be large I'd use the
primary key rows methods; if it were a small table which was likely to be
empty most of the time or didn't have a primary key (ewwwwwwwwwwwwwww
yuck!) I'd use Count.
--
Lyle
--
From ADO28.chm