By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

fastest way to determine if any records exist in table?

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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-----
Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a

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


Nov 13 '05 #5

P: n/a
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.

Nov 13 '05 #6

P: n/a
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(*).

Nov 13 '05 #7

P: n/a
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
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.