fastest way to determine if any records exist in table?

[adm]

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.

[Alan Webb]

In Jet and DAO the only time BOF and EOF are both true is when there are no
rows in a result. In ADO you only need to check for EOF. If you ran
something like "SELECT TABLE1.* FROM TABLE1;" in a recordset and checked for
EOF in VBA then it's likely there is nothing in the table.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"adm" <ad*****@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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.

[MGFoster]

adm wrote:

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Run a query that gets the count of the table:

SELECT Count(*) As Recs FROM table

or use DCount():

Debug.Print DCount("*","table_name")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlxGgYechKqOuFEgEQIV9ACeMzb4Hi6VGe2mKqPhWEoX8B C6nZoAniil
HYlrT29063JxK81jAJboGRZn
=QX5w
-----END PGP SIGNATURE-----

[Lyle Fairfield]

"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

[Lauren Wilson]

Why would the following code not be adequate:

dim db as DAO.Database
dim rst as DAO.Recordset
set db = currentdb()

set rst = db.OpenRecordset("tablename", dbopentable)

if rst.recordcount = 0 then
do what is needed
end if

On Wed, 13 Apr 2005 01:23:28 GMT, Lyle Fairfield <Lo******@FFDBA.Com>
wrote:

"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

[Lyle Fairfield]

Lauren Wilson wrote:

Why would the following code not be adequate:

dim db as DAO.Database
dim rst as DAO.Recordset
set db = currentdb()

set rst = db.OpenRecordset("tablename", dbopentable)

if rst.recordcount = 0 then
do what is needed
end if

Perhaps, you could try it and report back on its speed, as my test has
not yet completed running? The original poster asked for, I believe, the
fastest way in an ADP/MS-SQL Server application.

[adm]

great reply, lyle. i was wondering about stuff like "select top 1
from..."(not sure of syntax here)...i wonder if that's faster than
count(*).

[Lyle Fairfield]

adm wrote:

great reply, lyle. i was wondering about stuff like "select top 1
from..."(not sure of syntax here)...i wonder if that's faster than
count(*).

I tried TOP 1 but found it about 7% slower than PK and Count.

--
--
Lyle