How would you test if a table is empty using Access VBA code (perhaps with a SELECT COUNT(* query) ?

I am writing an Access application using Access as the front end and SQL Server as the back end database.

How would you test if a table is empty using Access VBA code (perhaps with a SELECT COUNT(* query) ? For example using a table named tblA.
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If Nz(DCount("*", "[YourTableName]") ) =0 Then   ' The count is zero
    ' do whatever
Else
     'do whatever else
End If
0
dportasCommented:
I'd do it server-side:

IF EXISTS (SELECT * FROM tbl)
BEGIN;
 /* ... */
END;
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Except I believe the Q was how to do it in VBA :-)

mx
0
dportasCommented:
>> Except I believe the Q was how to do it in VBA :-)

VBA can ONLY access a SQL Server database by issuing Transact-SQL statements. Based on zimmer9's example code it seems that he wants to know the SQL statements rather than VBA. Best practice in SQL Server is to use stored procedures for all data access and presumably zimmer9 knows how to call a proc from VBA.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"How would you test if a table is empty using Access VBA code "
Seems clear to me.

Using 'tblA'  as the table name.

If Nz(DCount("*", "[tblA]") ) =0 Then   ' The count is zero
    ' do whatever
Else
     'do whatever else
End If

mx
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>VBA can ONLY access a SQL Server database by issuing Transact-SQL statements
actually, that is inaccurate.
creating a linked table to the sql server table allows you do handle the table almost as if it were a native access table.

>Best practice in SQL Server is to use stored procedures for all data access
that is indeed 100% correct.
0
dportasCommented:
DatabaseMX: That's a potentially bad example though. Your code is apparently equivalent to the SQL statement:

SELECT COUNT(*) FROM tbl;

I haven't tested it but I guess that's the SQL actually executed by DCOUNT. If that's issued against a large table then you'll wait a long time for a result and your query is much more likely to be blocked than mine. That's a pointless overhead to impose on your application because EXISTS will give the same result much faster.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In Access VBA/SQL  Count ("*") is optimized to grab the total record count..

And you seem to insist giving him the solution in SQL when the Q clearly asked for a VBA solution.

mx

0
dportasCommented:
>> In Access VBA/SQL  Count ("*") is optimized to grab the total record count..

I just tested your code using a linked table in Access 2007. It issues "SELECT COUNT(*) FROM ..." on the server every time. That means it performs a table/index scan every time. In other words t is not "optimized" - it actually forces a count of every row. Did you actually try it? I'd be interested to know if you see different results.

zimmer9's question clearly asked about a SQL query as well as asking about VBA. That's the part of the question I was responding to.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In Access VBA/SQL  Count ("*") is optimized to grab the total record count..
It's been that way since Access 1.0.  I don't have to try it.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
LOL !!!
0
dportasCommented:
DatabaseMX: I'm not denying that it "grabs" the total record count. I'm explaining to you just HOW it does that.

If I run SQL Profiler it is clear that your code issues a SELECT COUNT(*) every time. Here are the events I see in Profiler:

SQL:BatchStarting      SELECT COUNT(* )  FROM "dbo"."tbl"       2007 Microsoft Office system
SQL:BatchCompleted      SELECT COUNT(* )  FROM "dbo"."tbl"       2007 Microsoft Office system

SQL:BatchStarting      SELECT COUNT(* )  FROM "dbo"."tbl"       2007 Microsoft Office system
SQL:BatchCompleted      SELECT COUNT(* )  FROM "dbo"."tbl"       2007 Microsoft Office system

If you are claiming something different then I think you should share the evidence with others and explain how you achieved it. Unsupported assertions are not helpful unless you can back them up with an explanation.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"If you are claiming something different then I think you should share the evidence with others and explain how you achieved it"

Dude ... it's been *documented* in the M$ Help Files for years.  I don't make a habit is spouting upsupported information.

Also, I don't work with SQL Server ... so, I cannot speak for what happens in that case.

mx
0
dportasCommented:
>> DatabaseMX: "Also, I don't work with SQL Server ... so, I cannot speak for what happens in that case"

But the OP's question is specifically about Access when used as a front end to SQL Server! Therefore in the OP's case your code WILL force a count of the table every time. Please realise that it's very unwise to assume that what happens in the world of Jet is also applicable to SQL Server.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
So, in resume:

 the COUNT(*) (ie DCOUNT), used against MS Access tables is optimized
 the COUNT(*) (ie DCOUNT), used against linked table will eventually result in full table/index scans (which is not optimized and not optimizable)

 the EXISTS() method IS optimized, but cannot be used within VBA unless you run indeed a query against the base table (ie not against the linked table)

 let me add another option:
 * query the table with the TOP 1 syntax, if that returns 1 rows (is very fast in both native access tables AND linked tables), the table is not empty, if no rows returned, the table is empty.

for the rest, I must assume that any further discussion is futile.
angelIII, zone advisor
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
dportas:
Note the Accepted Answer!

mx
0
dportasCommented:
Yep. zimmer9 unfortunately took some poor advice. Presumably because he, like you, didn't know any better. I hope you learned something for next time.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Angel does not give poor advice !!! Note his overall status on EE. Do you think that is from giving bad advice??

What I learned is what I already knew:  Don't argue with SQL guys who know nothing about Access.

out!


0
zimmer9Author Commented:
Angel does not give poor advice !!!
-------------------------------------------
As Spock would say "Affirmative".

I know I'm indebted to Angel for the countless times he has helped me with my challenges in Access.

thanks Angel.
0
dportasCommented:
zimmer9: Angel agreed with me in his followup post: "DCOUNT ... is not optimized and not optimizable".
Whenever possible you should avoid running such queries against SQL Server. Why would you choose a very sub-optimal solution in this case when an optimal one is available? Naturally you are free to do as you choose but my advice still remains sound and has been repeated by Angel.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Man O Man !!!

Z9:
"How would you test if a table is empty using Access VBA code"
A3:
"creating a linked table to the sql server table allows you do handle the table almost as if it were a native access table."
" the COUNT(*) (ie DCOUNT), used against MS Access tables is optimized"
DP:
What part of these statements don't you understand?  Let-It-Go!

mx
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DatabaseMX: I really have to ask you to stop now.

dportas IS right, and you posted it actually itself (copying what I said):
a linked table (is) <...> ALMOST as if it were a native access table.

dcount() on a linked table is not optimized and cannot be optimized, as such a linked table is NOT a native table.

so, what part of that do YOU not understand?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yet, that is the answer You gave.

This MSDN article makes no distinction about linked MDB's

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A0B5.asp

But actually, I've been somewhat mistaken from the git go, partly because I saw the work Count (*) in the  ... where in I posted DCount in the code.

Note in the MSDN article there is a distinction between Count and DCount:

"Tip The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression **instead of the DCount function**, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control."

So, re
"
 the COUNT(*) (ie DCOUNT), used against MS Access tables is optimized"

It appears the Count(*) and DCount (*) are in fact not the same.

 
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Yet, that is the answer You gave.
huh, and what is this:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23133444.html#a20809607

lol :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
That's what I am saying - where I was in error - referring to DCount as being optimized:

"But actually, I've been somewhat mistaken from the git go, partly because I saw the work Count (*) in the  ... where in I posted DCount in the code."

I posted DCount .. and then started referring to DCount as being optimized wherein Count (*) ... used in a query is optimized.

Also, re "DatabaseMX: I really have to ask you to stop now."
I really think you've got the wrong guy here Angel ... since I did *not* post this statement:

" dportas
02.03.2008 at 12:47PM PST, ID: 20810001
Yep. zimmer9 unfortunately took some poor advice"

In fact, I tried to back you up .. and then you slam me. Seems odd.

Anyway, the fact remains - according to Microsoft that Count(*) - as opposed to DCount is optimized ... and I don't see where Microsoft is making a distinction between linked Access tables and local Access tables.

mx
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Children, you'd better kiss and make up right {expletive deleted} now, or I'll call JD in here and have him open up a big can of whoop ass on all of you...
0
zimmer9Author Commented:
Come on people now
Smile on your brother
Everybody get together
...

The Youngbloods
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm out !!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.