Attaching the results of an Access query to a variable in VBA

This question has 2 parts

first I am wondering if I can create a global variable that tells me the results of a query. it would be something like this

count(id) from table1
where field1 = 1

Open in new window


Alos is there a way to attach this variable to a label name for a button. What I mean is that I have a button that when clicked sends you to a workqueue. Would it be possible to have the count of the workqueue in the button name so we can know how many items are in the workqueu

thanks in advance.
damixaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Nick67Commented:
Labels have .Captions

In the OnCurrent event, you can put code like

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("select ID from table1 where field1 = " & Me.SomeControl.Value, dbOpenDynaset)
'Me.SomeControl.Value sets the filtering criteria
me.MyLabel.caption = "In Queue: " & rs.RecordCount
rs.close
set rs = nothing

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Maybe even faster:

 =DCount("*","table1","field1 = " & Me.SomeControl.Value)

Jim.
Nick67Commented:
@Jim
DCount() is of the devil.
Especially for something fired in the current event.
It really hurts performance in navigation.

Things in Access that are of the Devil
Aggregate functions in general
Lookup fields
Checkboxes or yes/no in table Boolean fields
Multi-valued fields
Attachment fields

All those things for which some fool in an alley should be given a good whooping.

You know that :)
They won't die if people keep using them.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

damixaAuthor Commented:
Thank you, this is great!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<DCount() is of the devil.>>

 That's only a myth.   The domain functions can be as fast or faster than comparable methods.

 Where they get a bad rap is when people use them in ways they were not intended to be used, such as in a query.

Jim.
Nick67Commented:
That's only a myth.
Then I am a mythical slayer.
Because after discovering through testing what a pig it is, I went through my apps and killed them all.
It perked up the ability to navigate forms quite markedly.
In the right circumstances using ADO to the SQL Server backend was even better yet.

I'll stick with Allen Browne on the subject
http://allenbrowne.com/ser-42.html

Let's not talk about the poorly named DFirst and DLast :()
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Again, it depends on how your using them.  If your using Dlookup() like this:

<x> = Dlookup("[Field1]","table1",...)
<y> = Dlookup("[Field2]","table1",...)
<z> = Dlookup("[Field3]","table1",...)

 or course it will be slow because your opening and closing a recordset each time to fetch three fields.  Comparing apples to apples, would you do this in code:

Dim db as database
Dim rs as recordset

set db = currentdb

set rs = db.openrecordset("select ID from table1 where field1 = " & Me.SomeControl.Value, dbOpenDynaset)
<x> = rs[ID]
rs.close
set rs = nothing

set rs = db.openrecordset("select ID from table1 where field1 = " & Me.SomeControl.Value, dbOpenDynaset)
<y> = rs[ID]
rs.close
set rs = nothing

set rs = db.openrecordset("select ID from table1 where field1 = " & Me.SomeControl.Value, dbOpenDynaset)
<z> = rs[ID]
rs.close
set rs = nothing

 of course not.  But you can return an expression from Dlookup()

 =Dlookup("[Field1] & "|" & [Field2] & "|" & [Field3],"table1",...)

 and now be some what comparable.   It's a clumsy way to work though because you have to work with the expression.  

 And long before Allan came on the scene, an article appeared in Smart Access where all the lookup methods were tested going the recordset route vs the domain functions.   In most cases they were close.  In some, the domain functions edged out what you could do in code.  In other's, the code edged out the domain functions.  

 So the old myth of them being performance hogs is just that, a myth.  That mostly stems from people using them incorrectly like above or sticking them in a query.

  Also the page you pointed to is just talking about Dlookup().  Personally, unless I have a recordset already open, I'll use DCount().

 BTW, your example should have been structured a bit different because in DAO, you may or may not have an accurate record count until you do a .MoveLast

It should have been:

select Count(*) from table1 where field1 = " & Me.SomeControl.Value

for the SQL.   You also are missing some performance optimizations in that CurrentDB() is an expensive call and not always needed (and it's not here)  You also opened the recordset as a dynaset, which is the most expensive cursor you can have.  A read-only, forward-only cursor would be faster if all your trying to do is get the count.

Jim.
Nick67Commented:
@harfang conclusively proved that CurrentDb is in fact always available and that code like

db.close
set db = nothing

is in fact completely useless, as it doesn't close or set CurrentDb to nothing.
On the other hand, just earlier this month I saw a case where someone was doing some tabledef work, and the code just WOULDN'T work right until an explicit Set db = CurrentDb statement was in use.
http://www.experts-exchange.com/questions/28787159/Appending-propertly-to-column-not-working-ACCESS-2010.html

Since it's close to zero overhead -- given that the CurrentDb object is always available, never closes, and can be set explicitly to the variable and shortcut some problems that can occur by trying to do without it, setting up a db variable is the way to go.

select Count(*) from table1 where field1 = " & Me.SomeControl.Value
You're right.
@damixa, the SQL for the recordset should be what @Jim posted.

Cursor types can be finicky, too, depending upon backends.
Not all cursors are supported by all providers
A dynaset always works and has less opportunity to lie in a multi-user environment.

Still, in my experience there are absolutely no occasions where using DLookup() is going to be a better choice than recordset codes, no occasions to use DFirst() or DLast ever, no occasions where DMax or DMin is superior to a Top 1 Order By (Asc or Desc as suits the occasion)

DCount() is the only one of them that comes close -- but most often you have use for the recordset anyway.

Depending upon the OP's form setup -- if this form is filtered to just the one ID, then
dim rs as recordset
set rs = me.recordsetclone
rs.movelast
me.MyLabel.caption = "In Queue: " & rs.RecordCount
rs.close
set rs = nothing


might be the very best way of all to get after it.

Lots of things that work, and some things that work well
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Since it's close to zero overhead -- given that the CurrentDb object is always available, never closes, and can be set explicitly to the variable and shortcut some problems that can occur by trying to do without it, setting up a db variable is the way to go.>>

 I'm not sure what the close has to do with anything, but the CurrentDB() call is an expensive one.  When I said it's "not needed", I meant in regards to vs using dbengine(0)(0), which was not clear.   Take a look at the following article:

http://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 But in short, CurrentDB() is about 5,000 times slower than using the JET reference because while not obvious, a call to CurrentDB() always creates a new database object with all collections refreshed.  Since this table would already exist, that's not needed.

<<and the code just WOULDN'T work right until an explicit Set db = CurrentDb statement was in use.>>

 That's covered in the article as to why.

<<Still, in my experience there are absolutely no occasions where using DLookup() is going to be a better choice than recordset codes, no occasions to use DFirst() or DLast ever, no occasions where DMax or DMin is superior to a Top 1 Order By (Asc or Desc as suits the occasion)>>

 To each his own, but if you buy into "Domain Functions are always bad", then your just buying into the myth.

Jim.
Nick67Commented:
@Jin
I'll see your article and raise you three plus empirical evidence:
https://groups.google.com/forum/?hl=en#!topic/comp.databases.ms-access/AKNOQqL05iw%5B51-75%5D
I think your 5000x number comes from here, but so does my understanding that you only ever take that hit once -- the first time you set up CurrentDb after opening the file.  Since you can't close it or set it to nothing, it remains -- and when you make another call, you get the existing object.  If it didn't work that way then, this code would crash

Dim db As Database
Dim x As Integer
Dim duration As String

duration = Timer
For x = 1 To 32000
    Set db = CurrentDb
    db.Close
    Set db = Nothing
Next x
duration = CStr(Timer - duration)
MsgBox duration


It doesn't crash.
If I run it, it takes 15.667 seconds to run through 32000 loops

This code
Dim db As Database
Dim x As Integer
Dim duration As String

duration = Timer
For x = 1 To 32000
    Set db = DBEngine(0)(0)
    db.Close
    Set db = Nothing
Next x
duration = CStr(Timer - duration)
MsgBox duration


also doesn't crash and executes in 0.2265 seconds
Much faster.
69 time faster.
Not 5000

Knock it down to for x = 1 to 1
0.012 for CurrentDb
0.004 for DBEngine(0)(0)
A factor of three, not 5000.
And you can run into some issues via DBEngine(0)(0)
So it's not worth doing.

Let's do the same test with DLookup() vs CurrentDb vs DBEngine
Now, this is a dumb and slow way to set up the code and takes 133.54 seconds to execute
Dim db As Database
Dim rs As Recordset
Dim x As Integer
Dim duration As String
Dim theValue As String

duration = Timer
For x = 1 To 32000
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select theName from table1 where ID =1", dbOpenDynaset, dbSeeChanges)
    theValue = rs!TheName
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
Next x
duration = CStr(Timer - duration)
MsgBox duration


This is a bit better code and takes 107.09 seconds to run
Dim db As Database
Dim rs As Recordset
Dim x As Integer
Dim duration As String
Dim theValue As String

Set db = CurrentDb
duration = Timer
For x = 1 To 32000
    Set rs = db.OpenRecordset("Select theName from table1 where ID =1", dbOpenDynaset, dbSeeChanges)
    theValue = rs!TheName
    rs.Close
Next x

duration = CStr(Timer - duration)
MsgBox duration


DBEngine(0)(0) doesn't improve matters much.  Using it instead of CurrentDb takes 105.03 seconds -- and since the call for ONE call was in milliseconds, the difference here is in the vagaries of testing, and not in substance.

DLookup() is a pig -- and @harfang has demonstrated that ALL the aggregate functions are actually built on it's piggish underpinnings -- and this code takes 135.99 seconds to execute.
Dim x As Integer
Dim duration As String
Dim theValue As String
duration = Timer
For x = 1 To 32000
    theValue = DLookup("theName", "table1", "ID = 1")
Next x
duration = CStr(Timer - duration)
MsgBox duration

It's not a myth.
Stay away from the aggregate functions.  They are pigs.
That becomes especially apparent when you use them in queries because it mimics what this loop code does -- it calls them repeated--as you noted.

The aggregate functions are syntactic sugar -- although with all the quotes they aren't that sweet.
Recordset code performs better out of the box and can be optimized with persisted object etc.

Unless structured correctly, attempting to skirt setting up a db object will lead to grief when working with TableDefs.  This is a double-down since I've given you one example of that already, but here's one with extensive discussion
http://www.experts-exchange.com/Database/MS_Access/Q_23355139.html

And finally there's MS's advice
https://msdn.microsoft.com/en-us/library/office/ff835631.aspx
Backed with Michael Kaplan's statement from the first link:


7) Extensive argument over which method is better on the incomplete
basis of the current thread here is stupid. Each has advantages and
disadvantages. The 100% solution is a very simple one:

    Private m_db As DAO.Database

    Public Property Get CurrentDbC() As DAO.Database
      If (m_db Is Nothing) Then
        Set m_db = CurrentDb
      End If

      Set CurrentDbC = m_db
    End Property

Why is this the "CurrentDbC" property proc best? Because it is the
most generic and handles the most issues and problems. After all, a
global can be erased if someone hits the code reset button in VBE.
DBEngine(0)(0) might not be the current db. CurrentDb is expensive if
called repeatedly. And so on.

So, empirically, the aggregate functions are slower than their recordset counterparts, aren't as flexible or extensible, and the difference between DBEngine(0)(0) and CurrentDb is in the millisecond range.

It's whatever floats your boat -- but I have seen setting  .Caption and .Value with DLookup slow navigation up.  For giggles I fired 1200 records into Table1 and made Public variables for db and rs and some code to walk through the 1200 records on the single form, changing the Caption and Value of a couple of controls with
If db Is Nothing Then Set db = CurrentDb
Set rs = db.OpenRecordset("select Count(*) as TheCount from table1 where TheName=" & Chr(34) & Me.ID & Chr(34), dbOpenDynaset, dbSeeChanges)
Me.Label6.Caption = rs!Thecount
Me.Text5.Value = Me.Label6.Caption

or
Me.Label6.Caption = DCount("*", "table1", "TheName=" & Chr(34) & Me.ID & Chr(34))
 Me.Text5.Value = Me.Label6.Caption


No surprise -- the recordset code is faster.

At the heart of every myth there is some deep truth :)

Nick67
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I think your 5000x number comes from here, but so does my understanding that you only ever take that hit once -- the first time you set up CurrentDb after opening the file.>>

 Nope.  Gives you a new database object every time.  That's why you never want to use it in a loop.  That's also why it's collections are always up to date.  If what you said was true, then if you created a querydef, your next grab of CurrentDB() would not see it and you'd have to do a refresh.

 As for the rest, YMMV.  There are a number of issues with what you did in your testing (did you clear the JET cache or disk cache between each?, how many objects were in the DB? records in the table?, etc.) that could give you quite a different out come.

 And in your last test, you only set CurrentDB() once, but that's not something you'd end up with if it was in a routine and you called the routine each time.   Sure you could cache it in a global variable, but that's not really a fair comparison to what Domain functions give you.

 So we'll just agree to disagree I guess.

 Have a great weekend,

Jim.
Nick67Commented:
So we'll just agree to disagree I guess.
and that's ok.

The test was with a brand new mdb, initially with a single record in the table.
Nothing complicated.
DLookup gets worse as things get more complex (FE/BE, SQL Server) so I gave it its best shot with the simplest scenario.
And it is still slower.

a fair comparison to what Domain functions give you
Domain functions output a value with some syntactic sugar
Recordset does the same thing with more setup.
And note that the test I did with Set db=CurrentDb inside the loop still did slightly better than DLookup (133 sec vs 135 sec)
Set it up more sanely and it performs better, around 40% better.
DLookup has no optimization available to it.  It is what it is.

And it isn't me saying it, it's Michael Kaplan who designed some of the wizards still in use in Access
1) Calling db.Close in Access when db is either DBEngine(0)(0) or
CurrentDB is a *no-op*. It does nothing. This is due to the fix for an
old bug in Access 2.0, where that call would still fail but would
cause problems with any open objects like recordsets. So they moved
the "able to close the db?" check to the beginning of the method from
the end. Any attempt to call will now not cause problems but it will
literally do NOTHING. If this text is not clear then you are welcome
to do it. Just as you are welcome to add any useless method call to
your app. But it will not do anything.

2) Setting db to Nothing in Access when db is either DBEngine(0)(0) or
CurrentDB will do the same thing as in any VBA reference variable --
it will lower the reference count to the object by one, and if the ref
count is 0 then it will destroy the object. Since the current database
has a permanent reference internally in Access, this will not destroy
the object and thus never have an effect.

SO,  Nope.  Gives you a new database object every time.
Clearly not, since it doesn't close.  What the CurrentDb method does is give you a refreshed and refreshable reference to the persistent object that Access opens and maintains itself.  This clearly takes some overhead to do -- but as the demo code shows, not nearly 5000x the overhead.  And done correctly -- persisting your own object to a Public variable, and check its validity before use  -- it's thoroughly negligible.

I've conclusively demonstrated that in Access 2003 contained within an mdb, CurrentDB is not anywhere near 5000x as expensive as DBEngine(0)(0).  Run 32,000x its ~69x as expensive.  Run once, it is three times as expensive -- and in the millisecond range.  And it doesn't do what CurrentDb does -- which is stay updated throughout its lifecycle -- so it's not really apples-to-apples.

But comparing DLookup to recordset code is certainly such a comparison, and in even the best situation, DLookup is slower -- and in the worst scenario I have seen it be much, much slower.

Have a great weekend,

Nick67
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<SO,  Nope.  Gives you a new database object every time.
Clearly not, since it doesn't close. >>

 Yes it does...your slightly missing the point, it gives you a new object for VBA every time.  

<<And it isn't me saying it, it's Michael Kaplan who designed some of the wizards still in use in Access>>

 and before Michael, it was Getz, Litwin, Reddick, Gunderloy, and Ferguson, not to mention Kevin Collins, who helped develop most of JET ;)

 Micheal certainly did a lot of great stuff in and with Access, but he was not the only one.   What he stated is true enough; a close on a CurrentDB() in VBA is a no op as far as Access is concerned.  Access will never destroy it's internal object that represents the DB currently open in Access until it's closed.   That's why the Access UI works outside of VBA code.  But a object in VBA is created and destroyed each time (and I'm not talking about an object variable).  That's why the reference count is incremented/decremented each time you call CurrentDB().

 The object that Access holds representing the database and what you work with via CurrentDB() in VBA are two separate things.   Direct from the on-line help from way back when:

The CurrentDb function creates another instance of the current database,while the DBEngine(0)(0) syntax refers to the open copy of the current database.

  Like I said, read the article,  and if you use the function in it and simply replace CurrentDB() with CurDB() throughout your app, you'll find the app runs faster, and I bet you more so than you would expect.

 Of course it depends on the app.  Certainly a simple DB with few objects will not see much difference between the two, but the typical app will and a complex app will see a considerable difference.

Moving on...

Jim.
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.