473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Nov 13 '05 #1
7 12028
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
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
"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

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave | last post by:
I have a form which is based on a table (a) with over 6000 records.I have a button on this form which will open another form with related data from another table (b). What I want to do is open the...
3
by: Larry Rekow | last post by:
As part of a macro, I'm trying to automate appending a table with new records. let's say the table 2 has some new records in it, but also has a lot of identical records to table 1. I would...
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
7
by: Ryan | last post by:
OK, here's my setup. I have a treeview control that is populated with records from a Product table, and it allows "checking". This is used for my automatic notification system. Say a particular...
2
by: shalini0702 | last post by:
Hi, I am facing problem of Missing Records in Table after Compacting and Repairing. Suppose my table had 486 records before compactig and after compacting I see only 485 records, One records has...
2
by: kevinjbowman | last post by:
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in...
2
by: Lemmuel31 | last post by:
i want to know the code that will determine if the table is existing and if the table exist i will drop it.. Can you give me some source code on that problem?
1
by: Dmitry Kulinich | last post by:
how to select 200 first records from table? -- Thank you, De Cool, EPE
1
by: elbatz | last post by:
Can somebody help me? How do I know if records in Table1 are also in Table2, using VB6 code? For example: If Table1 has 1111 in column1 and also Table2 has 1111 in column2 msgox "Existing...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.