Solved

To close or not to close, that is the Question.....

Posted on 2004-09-14
20
794 Views
Last Modified: 2008-01-09
Dim db as dao.database
set db = currentdb
db.close  '********************
set db = nothing

I know the general rule, if you open it close it (recordsets for example).  But i have seen DONT db.close, you didnt open it.  

From the A97 help

"The CurrentDb function creates another instance of the current database"

Dones't this state we are "opening" an new instance of the same db, show soudln't we close it??

I know some ppl may think that db.close will close their close there currentdb (ie docmd.quit), but i use it all the time, never happened..

Thoughts, what do you do??...

Dave
0
Comment
Question by:flavo
  • 9
  • 5
  • 3
  • +2
20 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 300 total points
Comment Utility
Here's a lovely long discussion thread on the newsgroups about this (and related) issues:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&frame=right&rnum=1&thl=0,1418985459,1418979873,1418665489,1418624219,1418619584,1418611046,1418117325,1418606539,1418618492,1418615375,1418601774&seekm=Kc2k6.3036%248u5.8749%40newsfeed.slurp.net#link1

From what I gather, it doesn't matter as long as you set the variable to Nothing, but it certainly does no harm to explicitly close it. I always set object variables to nothing out of habit in any case - I generally only explicitly close recordsets.
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
damn BCC internet Gestapo wont let me go there... "Non business related site"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Shame - it's a good discussion (the stuff on the merits of CurrentDb() vs DBEngine(0)(0) is quite interesting) - some of the guys on that thread have forgotten more about Access than I'll ever know.

See if you can get to it from another connection sometime.
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
>> See if you can get to it from another connection sometime.

have to wait till i get home ;-(
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Dave,

VBA Window
Help>Answer Wizard
Type CurrentDB then search
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
Mr. Paper Clip tells me nothing...  Access 97) I know the 2003 help seems better, ill have to look at that at home too.. Do MS even think about cleaning up anyway???
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Actually, the A97 help file (without Clippy) is far, FAR better than the A2K and A2K2 help files. I dunno about A2K3 though. The 97 one has everything in one place, but it isn't particularly enlightening on CurrentDb() apart from saying what you quoted above, and the A2K2 one pretty much repeats the same information. I really recommend reading the usenet thread :-)
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
Yes the 2k and xp help went down hill, from what i see the 2003 is another step forward
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
from Help

The CurrentDb method returns an object variable of type Database that represents the database currently open in the Microsoft Access window.

Note  
In Microsoft Access the CurrentDb method establishes a hidden reference to the Microsoft DAO 3.6 Object Library in a Microsoft Access database (.mdb). If you want to use the CurrentDb method in an Access project (.adp) you must set a permanent reference to the DAO 3.6 Object library in the Microsoft Visual Basic Editor.

In order to manipulate the structure of your database and its data from Visual Basic, you must use Data Access Objects (DAO). The CurrentDb method provides a way to access the current database from Visual Basic code without having to know the name of the database. Once you have a variable that points to the current database, you can also access and manipulate other objects and collections in the DAO hierarchy.

You can use the CurrentDb method to create multiple object variables that refer to the current database. In the following example, the variables dbsA and dbsB both refer to the current database:

Dim dbsA As Database, dbsB As Database
Set dbsA = CurrentDb
Set dbsB = CurrentDb

Note  

In previous versions of Microsoft Access, you may have used the syntax DBEngine.Workspaces(0).Databases(0) or DBEngine(0)(0) to return a pointer to the current database. In Microsoft Access 2000, you should use the CurrentDb method instead. The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0) syntax refers to the open copy of the current database. The CurrentDb method enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0) syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser database.

If you need to work with another database at the same time that the current database is open in the Microsoft Access window, use the OpenDatabase method of a Workspace object. The OpenDatabase method doesn't actually open the second database in the Microsoft Access window; it simply returns a Database variable representing the second database. The following example returns a pointer to the current database and to a database called Contacts.mdb:

Dim dbsCurrent As Database, dbsContacts As Database
Set dbsCurrent = CurrentDb
Set dbsContacts = DBEngine.Workspaces(0).OpenDatabase("Contacts
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
That doesn't really say very much about whether or not to use the Close method.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Author Comment

by:flavo
Comment Utility
yeah, same as the A97 help, but with the adp bit
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
What i believe is, if you open you must close,
but

<The CurrentDb function creates another instance of the current database >

This doesn't mean that another Db was open so we don't have to close but, rather set it to nothing or destroy it.
set db=CurrentDb
set db=nothing
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
ahh...

Help :

Closes an open DAO object.

Syntax

object.Close

The object placeholder is an object variable that represents an open Connection, Database, Recordset, or Workspace object.

Remarks

Closing an open object removes it from the collection to which it's appended. Any attempt to close the default workspace is ignored.

If the Connection, Database, Recordset, or Workspace object named by object is already closed when you use Close, a run-time error occurs.

Caution   If you exit a procedure that declares Connection, Database, or Recordset objects, those objects are closed, all pending transactions are rolled back, and any pending edits to your data are lost.

If you try to close a Connection or Database object while it has any open Recordset objects, the Recordset objects will be closed and any pending updates or edits will be canceled.  Similarly, if you try to close a Workspace object while it has any open Connection or Database objects, those Connection and Database objects will be closed, which will close their Recordset objects.

Using the Close method on either an original or cloned Recordset object doesn't affect the other Recordset object.

To remove objects from updatable collections other than the Connections, Databases, Recordsets, and Workspaces collections, use the Delete method on those collections. You can't add a new member to the Containers, Documents, and Errors collections.

An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).





Seems like i have been using 1 too many lines of code..

Dave
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
I did the worlds worst test and all 3 methods take the same time

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Dim lngStartTime As Long
Sub StartTimer()
lngStartTime = timeGetTime()
End Sub
Function EndTimer()
EndTimer = timeGetTime() - lngStartTime
End Function

Sub doIT()

Call test1
DoEvents
Call test2
DoEvents
Call test3
DoEvents

End Sub

Sub test1()

Dim db As DAO.Database
Dim i As Integer
Dim dtNow As Date

StartTimer

For i = 1 To 10000

    Set db = CurrentDb
    DoEvents
    Set db = Nothing
    DoEvents

Next
Debug.Print "test1 " & EndTimer
End Sub
Sub test2()

Dim db As DAO.Database
Dim i As Integer
Dim dtNow As Date

StartTimer

For i = 1 To 10000

    Set db = CurrentDb
    DoEvents
    db.Close
    DoEvents

Next
Debug.Print "test2 " & EndTimer
End Sub

Sub test3()

Dim db As DAO.Database
Dim i As Integer
Dim dtNow As Date

StartTimer

For i = 1 To 10000

    Set db = CurrentDb
    DoEvents
    db.Close
    Set db = Nothing
    DoEvents

Next
Debug.Print "test3 " & EndTimer
End Sub





test1 56271
test2 56265
test3 56278
0
 
LVL 5

Expert Comment

by:gwgaw
Comment Utility
CurrentDd is a METHOD that references (or points to) the currently open database. It is not a database object. Since it is not an object there is no need to use the close method. In fact, CurrentDb.Close does nothing.

gaw
0
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
Each call to CurrentDb returns a new instance of the database.  
You can (and should) close references set to it - this just closes that particular instance, not the actual database.
If you don't explicitly close an instance to CurrentDb then it will be handled by garbage collection when you deallocate all references to it - eventually.

  Set db1=CurrentDb
  Set db2=CurrentDb
  Rem: This created two seperate instances
 
  db1.Close
  Rem: db2 still points to an open instance, db1 now points to a closed one

  Set rst=db2.OpenRecordset(SQL, dbOpenSnapshot)
  Rem: That was okay, however ...

  ' Set rst=db1.OpenRecordset(SQL, dbOpenSnapshot)
  Rem: Don't try that as db1 points to a closed instance!

  rst.Close
  Set rst=Nothing
  Rem: Cleanup the recordset before closing the database that opened it.

  Set db2=Nothing
  Rem: It's Okay, Garbage Collection will close the unreference instance. Eventually.

  Set db1=Nothing
  Rem: You really should do that immediately after closing db1, and its a good idea to open and close things in reverse order
0
 
LVL 34

Author Comment

by:flavo
Comment Utility
I beg to differ gaw.

From the help it is a db object "The CurrentDb method returns an object variable of type Database"

With the .Close, i am refering to db.Close
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 200 total points
Comment Utility
 CurrentDd is a METHOD that references (or points to) the currently open database.
  It is not a database object.
  Since it is not an object there is no need to use the close method. In fact, CurrentDb.Close does nothing.

  gaw

More precisely, it creates an anonymous instance, then immediately closes it.  
Each time you call the method, it opens a new instance.

When using CurrentDb, you should either point a reference to it or access it anomynously through a WITH block.
You should not keep calling database methods through repeated instances.

  '<!-- Like This -->
  Set db=CurrentDb
  For each dt in db.TableDefs
    S = S & ", " & dt.Name
  Next
  db.Close
  Set db=Nothing

  '<!-- Or This -->
  With CurrentDb
    For each dt in .TableDefs
      S = S & ", " & dt.Name
    Next
    .Close
  End With

  '<-- But this is bad -->
  For Each dt in CurrentDb.TableDefs
    S = S & ", " & dt.Name
  Next
  CurrentDb.Close




0
 
LVL 34

Author Comment

by:flavo
Comment Utility
'<-- But this is bad -->
  For Each dt in CurrentDb.TableDefs
    S = S & ", " & dt.Name
  Next
  CurrentDb.Close


Sure is... shudder...  Err - Not enough memory to blah blah blah waiting to happen...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
> Seems like i have been using 1 too many lines of code..

While Nothing is supposed to close and de-allocate, when dealing with recordsets it doesn't always release the memory used so you should always explicitly set the recordset to Nothing. Conversely, not closing the recordset can (and often does) retain a ghost lock on the tables, and I've seen this cause corruption in many instances.

> CurrentDd is a METHOD that references (or points to) the currently open database

As Greyman pointed out, this creates a new instance of the database. It is *not* equivalent to DBEngine(0)(0) which DOES reference the current instance. This:

 For Each dt in DBEngine(0)(0).TableDefs
    S = S & ", " & dt.Name
  Next

should be as fast and have a much smaller memory footprint, but is (according to MS anyway), obsolete.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now