CurrentDb vs. db

I've seen people use 'Currentdb' and also 'db'; ie,
     Dim db as DAO.Database
     Set db = Currentdb

What difference in behaviour can I expect when using CurrentDb vs. db, and when should I use one over the other?
LVL 1
MilewskpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
CurrentDB() gives you a new database object each time you call it.
db is a object variable where you can store it to be used again and again.  So:
1. It's a performance gain.
2. It prevents some weird errors.
  For example, if you did this:
 Set doc = CurrentDB().Containers!Tables.Documents(0)
 and then
  debug.? doc.Name
  You'd get an error.  The reason is that CurrentDB()  (the database reference), went out of scope as soon as the line finsihed executing.  So the doc object variable now points to an non-existent reference.
You also might find the following useful:

http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html
JimD.
0
 
lee555J5Commented:
They are not the same thing - CurrentDb is an object and db is an object variable. You can say the following
CurrentDb.OpenRecordset or CurrentDb.Execute without dimming or using "db"
If you plan to access CurrentDb more than once or twice in a sub or function, it is best (more efficient and cleaner code) to declare db as a database object variable and set it to CurrentDb.
Remember to set it to Nothing when you no longer need it.
Lee
0
 
lee555J5Commented:
Personally, I have not encountered JDettman's issue.
However, it sounds like a good and consistent practice to always declare and set CurrentDb into db.
Lee
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is another what you can use CurrentDb.  Instead of all of this code:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
   
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SomeRecordSetName", dbOpenDynaset)
         ' operate on record set
     
    rst.Close
    Set rst = Nothing
    Set db = Nothing
   
... You can do this instead:

    With CurrentDb.OpenRecordset("SomeRecordSetName", dbOpenDynaset)
        .MoveFirst
        Do
            .Edit
            ![UserName] = "Milewskp"
            .Update
            .MoveNext
        Loop Until .EOF
    End With

No need to Dim any object variables, etc.  Everything remains in scope inside the With / End With.

Of course, if you have a lot of code in a Function ... and you plan to operate on the recordset in several places, then the first approach is more efficient - setting object variables which are used throughout the code.

However, the 2nd method has a definite performance advantage over the first, not so much on a one time use, but if it's run in a loop ... it's much faster.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<No need to Dim any object variables, etc.  Everything remains in scope inside the With / End With.>>
  Yeah, but since you can only use one with / end with at a time, your better off to use the function I had in my article and leave the with / end with to use with something else.
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"one with / end with at a time"
Well, I did point that out.  

In a new application I'm doing at work, in the simplest terms ... there is a Submit button.  When that is clicked, only one thing has to happen. 2-3 fields in a single record (the current record in effect in a local table) needs to be updated a remote table on the server in the backend.

Over the last two weeks, I did extensive testing on the fastest possible way to do this, trying at least 10 different ways,  calling the code in multiple loops (inner loops inside of outer loops) from multiple machines simultaneously ... all banging on exactly the same record ID at the same time, etc ... looking for the most reliable way to minimize locking conditions, etc.  The test table in the BE has 170K records, way more than will ever exist in the real system.  And of course this is over our super fast super stable WAN

At the end of the day, the fastest method turned out to be basically what I posted above - except I'm using the OpenDatabase method (instead of CurrentDb) off of DBEngine(0).  And the slowest method was basically the first method I posted.

One thing the was surprising was this:  Since I only have to open a Table as opposed to a Recordset in the BE, I have the opportunity to use the Seek method to locate the single record (out of 170K) ... as opposed to a WHERE clause in a SQL statement (in VBA).  I expected the Seek method to be the fastest, but it turns out it was approx the same as using a WHERE clause.

Anyway ... I learned a lot of stuff on the subject regarding (data) access methods, locking, etc.  And here is the ultimate statistic:

On a one shot basis ... the actual time to open the table in the BE, find the single record, due the update and close the table, etc .... 0.02nnn seconds !!!  Even with semi max stress testing .. 3-4 other systems doing the same thing in a loop ... and then from my local workstation ... executing the operation once ... it never took longer than 0.5 seconds ... but usually way less than that.  I find this amazing!

Oh JD ... remember when you pointed out the persistent connection thing a few Q's ago, well I tested that also in this paradigm ... and you are absolutely correct. That makes a HUGE difference in performance.  For any one of my test cases and for any number of loops (the more loops, the bigger the difference) ... the total time with a persistent connection was ... ONE HALF of the time w/o out a persistent connection.  And ... the persistent connection greatly reduces various locking errors ... you know ... the ones we see people post all the time here in EE.

mx
0
 
lee555J5Commented:
@DatabaseMX:
First, is the rst.Close  necessary in this situation? Your next statement is Set rst = Nothing. Obviously, if you plan to reuse rst for another recordset within the same function, you need to close the first prior to opening the second. But, if you no longer need rst, is not the Set rst = Nothing sufficient? Access Help says they are alternatives, just do not attempt rst.Close AFTER you Set it to Nothing.
Second, is the rst.MoveFirst necessary in this situation? Does not OpenRecordset put the pointer/cursor on the first record?
Third, is it not better to check for EOF at the "Do" stage, rather than the "Loop" stage, as in
Do While Not rst.EOF
>> code <<
Loop
instead of 
Do
>> code <<
Loop Until rst.EOF
IOW, what happens if OpenRecordset returns 0 records (both .EOF and .BOF are true)?
Sorry to hijack this thread, but it is slightly relevant to the original question. :-)
Lee
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Mx,
<<Well, I did point that out. >>
Sorry, I didn't walk away with that when I read your comment.
<<At the end of the day, the fastest method turned out to be basically what I posted above - except I'm using the OpenDatabase method (instead of CurrentDb) off of DBEngine(0).>>
Be careful with that; they are not the same thing. For DAO operations you'll be safe enough, but there are times when the two might not point to the same database.
<<One thing the was surprising was this: Since I only have to open a Table as opposed to a Recordset in the BE, I have the opportunity to use the Seek method to locate the single record (out of 170K) ... as opposed to a WHERE clause in a SQL statement (in VBA). I expected the Seek method to be the fastest, but it turns out it was approx the same as using a WHERE clause.>>
That's interesting. I have always found .Seek to be the fastest by far.
<<I find this amazing!>>
JET when used properly can be very fast.
<<Oh JD ... remember when you pointed out the persistent connection thing a few Q's ago, well I tested that also in this paradigm ... and you are absolutely correct. That makes a HUGE difference in performance. >>
It depends on the app to a certain extent. If you normally are keeping something open all the time anyway, then you won't notice a difference. But if you do close everything, then leaving something open in the background will make a big difference.
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Be careful with that; they are not the same thing. "
Well ... again, the table being opened is remote.  I was only comparing CurrentDB in the sample I posted above ... where that would be for a local case.

Yeah, I'm still surprised by the Seek results.  However, I'm going with Seek because I *think* is s/b faster, lol. Some loops is was, others not ... *but* ... the difference was very minor.

@lee555J5:
"First, is the rst.Close  necessary in this situation?"

Technically, no.  When the Function ends (loses scope) ... everything is Nothing.  BUT ... in days gone by, Access was not a good a garbage cleanup as it probably is today ... in fact, there were various cases of memory leaks related to this and other weird issues.  Soooo ... me doing this is really more of a hold over ... closing the loop so to speak ... just-in-case.

"is not the Set rst = Nothing sufficient?"
Probably.

"Second, is the rst.MoveFirst necessary in this situation?
No.  I was just putting some code there to make the example more 'robust', lol.

Does not OpenRecordset put the pointer/cursor on the first record?"
Yes. Again, it was just a sample piece of code - filler if you like.

"on the first record?

Third, is it not better to check for EOF at the "Do" stage, rather than the "Loop" stage, as in"
"IOW, what happens if OpenRecordset returns 0 records (both .EOF and .BOF are true)?"

Where you check sort of depends on what you are actually doing a real example.  Regarding 0 ... again that was only a partial example - just to demonstrate the CurrentDB stuff.  In either example above, I would be checking for a RecordCount of zero before attempting any operations.  I just didn't put that in the example ... for simplicity.

mx



0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Actually on the use of .Close and Set = Nothing, both should be used.  They do different things.  If you don't do both, your relying on the object lib and VBA to do the garbage cleanup.
JimD.
0
 
lee555J5Commented:
Alright, let me ask it this way:
Can anyone recommend a resource (web site, book, etc.) one could study to learn these behind-the-scenes details--details such as Milewskp's original question, why you recommend still doing your own garbage cleanup and how, efficiency and timing, and other things talked about in this question? Some of this is in Access Help, but much of these subtleties are mere mentions and asides and do not appear to be addressed directly.
Lee
0
 
Dale FyeCommented:
I agree with JD, good to do your own cleanup.  I usually put the cleanup in the procExit section of my code:

Public Sub DoSomething

    Dim strSQL as String
    Dim rs as DAO.Recordset

   ON Error Goto ProcError

   'do something

ProcExit:
   if not rs is nothing then
       rs.close
       set rs = nothing
   end if
   Exit Sub

ProcError:
   'handle error
   'you might handle something here and use Resume
   'or handle something and use Resume Next
   'but for those errors you want to log and then exit out of the subroutine, I always use:

   Resume ProcExit

   'to make sure I cleanup my recordsets, especially after encountering errors.

End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... I *do* always use both aka cleanup.  Where I said 'probably' above is because well ... it probably is true ... even the Help file says that.  But in actual reality, 'probably' ... when the function loses scope ... they are all toast.

The best book you going to find on the subject is still the Microsoft Jet Datbase Engine Programmers Guide ... which in fact I have been going through again later.  Most of the material is still valid through A2003.  It's a great book.

http://www.amazon.com/Microsoft-Database-Programmers-Professional-Editions/dp/B00005TNX9/ref=sr_1_1?ie=UTF8&s=books&qid=1269729300&sr=8-1

I got a used copy like brand new with CD for $16 ...

mx
0
 
lee555J5Commented:
@fyed: same here, that is how I do mine
@DatabaseMX: Thanks. I will look for / wait for one covering v4.0 and Access 2007/2010.
Lee
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I will look for / wait for one covering v4.0 and Access 2007/2010."

Not sure that' gonna happen.  For $10-15 on Amazon ... the original is still happening ... and all of the above is covered.

mx
0
 
datAdrenalineCommented:
Ok ... I will add my two cents to the mix ... :)

>>Dim db as DAO.Database
     Set db = Currentdb

What difference in behaviour can I expect when using CurrentDb vs. db, and when should I use one over the other?<<

CurrentDb does NOT give you a new database object, it refreshes all the collections, then gives you a reference object (basically a pointer) to the current database object.

Any time you need to call CurrentDb more than once, create an object variable to hold the pointer that is returned, or use the With..End With block, your performance will improve since you will not be refreshing the collections over and over.  Using DBEngine(0)(0) does NOT refresh the collections and thus is not the preferred method for pointing to the current db, in addition there are scenarios in which DBEngine(0)(0) does NOT refer to the current database.  IIRC sometimes add-ins assume the index position of 0.

Sometimes you are required to declare an object variable and maintain persistence of the current database returned by CurrentDb (I call the Parent Object Persistence).  In summary, If an object defines how data is to be stored, then it will require parent object persistence.

A querydef defines how data is retrieved/manipulated, and a recordset is a retrieved set of records ... a tabledef and a relation defines how data is stored ... So, with this, a QueryDef or Recordset object does NOT need persistence of the db object, but a TableDef or Relation DOES need persistence of the db object.

Examples:
Set tdf = CurrentDb.TablesDefs("MyTable") 'Drops scope ... object on right defines how data is stored
Set qdf = CurrentDb.QueryDefs("MyQuery") 'Maintains scope ... object on right defines how data is retrieved/manipulated
Set rst = CurrentDb.TablesDefs("MyTable").OpenRecordset(dbOpenSnapshot) 'Maintains scope ... object returned on right refers to retreived data.
Set fld = CurrentDb.QueryDefs("MyQuery").Fields(0) 'Drops scope ... object on right defines how data is stored in the resultant recordset.

LPurvis and myself had a progressive discussion on this very topic here:
http://www.utteraccess.com/forum/index.php?s=&showtopic=1720104&view=findpost&p=1721338

----
As far as using .Close and setting to Nothing.  If I .Open it, I .Close it.  Many have argued that a .Close is more critical (i tend to think 'required') than a setting to Nothing (which I deem as optional), and I agree with that philosophy.  LPurvis, myself, and others discussed about that topic here:
http://www.utteraccess.com/forum/index.php?s=&showtopic=1567910&view=findpost&p=1567953

----
With respect to .Seek and a WHERE clause.  A .Seek ALWAYS uses and index, so it is very efficient.  A WHERE clause will use an index if one is defined on the field being searched. So, if the WHERE clause searches for a value in an indexed field, then I would definately assume the speed results to be vitually identical.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"then I would definately assume the speed results to be vitually identical."

Which is what I found ... and the Index in both cases was the Auto Number PK.

As far as DBEngine(0)(0) and CurrentDB dialog ... I will always refer to the master on the subject ... Michael Kaplan:

http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea?hl=en&

mx

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@Lee,
<<Can anyone recommend a resource (web site, book, etc.) one could study to learn these behind-the-scenes details--details>>
  That's somewhat difficult.  Most of these tid bits come from various MSKB articles, the JET programmers guide as Mx mentioned, white papers on JET, published books, etc.
@datAdrenaline
<<CurrentDb does NOT give you a new database object, it refreshes all the collections, then gives you a reference object (basically a pointer) to the current database object.>>
  Sorry, but that is incorrect.  Every call to CurrentDB() hands you a new database object.  That's why the collections are always up to date.  This is proven and documented in numerious places.
DBEngine(0)(0)  does not give you refreshed collections because it is JET's internal pointer to the DB through the DAO object lib and not Access's.  JET does not know what's going on in Access, so it has no way of knowing if any Access objects have been added.  That's why you need to refresh the collections.
 <<As far as using .Close and setting to Nothing.  If I .Open it, I .Close it.  Many have argued that a .Close is more critical (i tend to think 'required') than a setting to Nothing (which I deem as optional), and I agree with that philosophy.  LPurvis, myself, and others discussed about that topic here:>>
  Again, I think your off a bit.  Each does something different.  Neither is technically required, but if your doing a good job of cleanup, you always want to close anything you've opened and if a object variable was used, set it to nothing.
JimD.
0
 
MilewskpAuthor Commented:
Hi Jim,
You article was really, really helpful. I'll be using your CurDb() from now on. Thanks!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<You article was really, really helpful. I'll be using your CurDb() from now on. Thanks!>>
 Glad you found it useful...
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

DBEngine(0)(0) || CurrentDb

From the Microsoft Jet Database Engine Programmer's Guide, page 619 (also on page 24 discussing CurrentDb):

"If you're working in Microsoft Access 97, you can use either the CurrentDB function or the DBEngine(0)(0) syntax to return a reference to the database that is currently open in the Microsoft Access user interface.

The CurrentDb function differs from the DBEngine(0)(0) syntax in that it creates another instance of the current database and returns a reference to that instance, and it refreshes all collections in the current database. On the other hand, the DBEngine(0)(0) syntax doesn't refresh any collenctions, so it may perform faster.

If you switch from using the CurrentDB function to the DBEngine(0)(0) function syntax, make sure that your code doesn't depend on any collections being refreshed.  If it does, you can refresh these individual collections separately."

I would say the key sentence is:

"...in that it creates another instance of the current database and returns a reference to that instance."

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw, I agree that .Close and =Nothing are two different things.

And the rule has always been: "If you didn't Open it, don't Close it"  (or vice-versa).  That's why you do not use .Close on a CurrentDb object variable.  Funny that in many of the Help file examples, this is done. As Michael Kaplan pointed out in the link I posted above, back in A2.0 (and maybe 97) doing so created some subtle problems ... what at that time ... I did confirm, IE was able to duplicate.

mx

0
 
MilewskpAuthor Commented:
< I usually put the cleanup in the procExit section of my code:>
Me too, but I use 'On Error Resume Next' instead of If statements to test if each variable is already closed and set to nothing: eg.,

QuitSub:
   On Error Resume Next
       rs1.close
       set rs1 = nothing
       rs2.close
       set rs2 = nothing
       set qdf - nothing
       set tdf = nothing
       set dbs = nothing
       ....

End Sub
0
 
datAdrenalineCommented:
>> <<CurrentDb does NOT give you a new database object, it refreshes all the collections, then gives you a reference object (basically a pointer) to the current database object.>>

 Sorry, but that is incorrect.  Every call to CurrentDB() hands you a new database object.  That's why the collections are always up to date.  This is proven and documented in numerious places. <<

Yep ... sorry, I know better <dazed>, and know the "mechanics" behind it and have described it in many posts --- I guess the sleep deprivation mixed with C# and ASP learning curve mixing in with VBA taht was causing the "duh" moment.  But in my defense, the object instances point to the same collections, so changing QueryDef in one will change the querydef in the other, but CurrentDb does indeed give you a different memory space in order to set properties that are exclusive to that instance.  Thanks for getting the information about CurrentDb back on track!

With respect to .Open and .Close --- I don't agree that I am off a bit, I don't think I said anything than much differnent than you --- if you open it, close it.  And agree that "good" clean up would be to set object variable to Nothing.  And while technically neither are "required", I have seen and experienced issues with not closing something, however, I have never experienced an issue with not setting to Nothing --- I am not saying a scenario of trouble does not exist somewhere, I just have not been exposed to one, thus my mindset (opinion) that .Close is more imortant than setting to Nothing.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

JD:
"CurrentDB() gives you a new database object each time you call it."

Brent:
"it refreshes all the collections, then gives you a reference object (basically a pointer) to the current database object."

Microsoft Jet Database Engine Programmer's Guide:
"...it creates another instance of the current database and returns a reference to that instance."

mx
0
 
datAdrenalineCommented:
Also ... in my defense ... the quote from MX ..

"...in that it creates another instance of the current database and returns a reference to that instance."

So, while CurrentDb can be viewed as a reference it can be viewed as a new object as well.  For example, check out the following code:

Public Sub TestRef()

    Dim db1 As DAO.Database
    Dim db2 As DAO.Database
   
    Set db1 = CurrentDb
    Set db2 = CurrentDb
     
    db1.QueryTimeout = 120
   
    Debug.Print db1.QueryTimeout & " " & ObjPtr(db1)
    Debug.Print db2.QueryTimeout & " " & ObjPtr(db2)
   
End Sub

You will see that in the Immediate window, the values of .QueryTimeout are different, also the values of ObjPtr are different, thus giving us a basis for claiming that CurrentDb returns a new "copy".  As a comparison, lets use the same code with DBEngine(0)(0)

Public Sub TestRef()

    Dim db1 As DAO.Database
    Dim db2 As DAO.Database
   
    Set db1 = DBEngine(0)(0) 'CurrentDb
    Set db2 = DBEngine(0)(0) 'CurrentDb
     
    db1.QueryTimeout = 120
   
    Debug.Print db1.QueryTimeout & " " & ObjPtr(db1)
    Debug.Print db2.QueryTimeout & " " & ObjPtr(db2)
   
End Sub

You will see that the querytimeout of db2 will match that of db1, even though only db1's querytimeout was set, also the value returned by ObjPtr() is the same for db1 and db2, thus "proving" that we created pointer object instead of individual instances.

So, with this small "proof", CurrentDb does act more like a new copy of the db object with its ability to hold property values that differ from other instances set with CurrentDb, but I think it goes deeper and tricker than simply stating that currentdb creates a new copy or that it is a reference, because there are reasons to beleive both. So a bit of a back-pedal but the esoterics of it can get confusing, even when you know whats what.
0
 
datAdrenalineCommented:
Hello MX,

Yes my statement is virtually identical to the manual :) ... woo hoo! ... but with the post I just made, I hope you can see why JD said what he said, accompanied by my subsequent consession to JD's then ... but then a bit of a back pedal again .... man I am waffling too much! ... Uggghhh!!!  .... CurrentDb just seems to be both a reference and a copy.  I typically refer to CurrentDb as pointing to the current database, and I will continue to do so.
0
 
MilewskpAuthor Commented:
Hi Mx,
<However, the 2nd method has a definite performance advantage over the first>
I was surprised at this claim, so I ran my own test (Access 2002, SP3), using the attached snippet.
I found no difference in speed.
Public Sub InsertNumberedRows(MyTable As String, MyField As String, First As Long, Last As Long)
   'This proc inserts Last-First+1 rows into MyTable, populating MyField with consecutive long
   'integer values from First to Last inclusive.
   
   'ASSUMPTIONS: Myfield is of type Long.
   
   Dim i As Long
   Dim rs As DAO.Recordset
   Dim Start As Single '''
   
   DoCmd.RunSQL "DELETE " & MyTable & ".* FROM " & MyTable
   Start = Timer '''
      Set rs = CurrentDb.OpenRecordset(MyTable, dbOpenDynaset)
      For i = First To Last
         rs.AddNew
         rs(MyField) = i
         rs.Update
      Next i
   MsgBox Timer - Start '''
      
   DoCmd.RunSQL "DELETE " & MyTable & ".* FROM " & MyTable
   Start = Timer '''
      With CurrentDb.OpenRecordset(MyTable, dbOpenDynaset)
         For i = First To Last
            .AddNew
            .Fields(MyField) = i
            .Update
         Next i
      End With
   MsgBox Timer - Start '''

End Sub

Open in new window

0
 
datAdrenalineCommented:
... Times like these I would we could edit our posts ... :-s ...
0
 
datAdrenalineCommented:
.. Ok ... reviewing ... I really wish we could edit our posts ... :-s ... I keep hearing this voice ... 'sir, please step away from the computer' ... sounds like good advice tonight!, hopefully it will let brain catch up to my thoughts and everything will be ok ... ROFL!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@
"I found no difference in speed."

We, put this:

Dim rs As DAO.Recordset

inside the timer ... and then run both in a loop 100 to 500 times.

I found a noticeable difference every time.

mx
0
 
MilewskpAuthor Commented:
Hi mx,
Can you post you code?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Can you post you code?"

Well, not easily at this time.

mx
0
 
MilewskpAuthor Commented:
Thanks Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
??
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Unsubscribing to further Q's by this user.

mx
0
All Courses

From novice to tech pro — start learning today.