db.Close not necessary 2

[psemianonymous]

I think this is a widely-propogated fallacy--when you use your local Database object, you do not have to use the ".Close" method when you're done with it. An example:

Sub UseLocalDB()
On Error GoTo Sub_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

'Note we did not open the database

    Set db = CurrentDb()
    'could also use:
    'Set db = DBEngine(0)(0)
    
    Set rs = db.OpenRecordset("qrySomeQuery")
    
Sub_Exit:
On Error Resume Next
    rs.Close
    Set rs = Nothing

'db.Close *NOT NECESSARY*

    Set db = Nothing
    
    Exit Sub
    
Sub_Error:
    MsgBox Err.Description
    Resume Sub_Exit
End Sub

Note that you should close Recordsets that you open. And note that you should close Databases that you opened, like in the following:

Sub UseSomeOtherDB()
On Error GoTo Sub_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

'Note we opened the database

    Set db = DBEngine(0).OpenDatabase("C:\somedir\SomeFile.MDB")
    'could also use:
    'Set db = DBEngine(0)(0)
    
    Set rs = db.OpenRecordset("qrySomeQuery")
    
Sub_Exit:
On Error Resume Next
    rs.Close
    Set rs = Nothing

'db.Close should be used here

    db.Close
    Set db = Nothing
    
    Exit Sub
    
Sub_Error:
    MsgBox Err.Description
    Resume Sub_Exit
End Sub

 

[DCCoolBreeze]

No. The close database is important. Basically the close is releasing resources. Now if you are exiting the program, the program will perform the "close"; however, if you keep the program open...please perform the close.

 

[psemianonymous]

Please re-read my post. I stick by it--when you are using a reference your local database object (meaning, for example, set db = CurrentDB() ), you do not need to use the .Close method to close your database object.


When you open a (non-local) database, however, and set a reference to *that*, then a .Close method is indeed necessary.


I don't know why I bothered with the original post--maybe just to clear up some fallacies. With VB/VBA/Access, there is a lot of 'voodoo' and ambiguous grammar and confusion, so I was just trying to clear up this one tiny bit...

 

[LNBruno]

IMHO: it's good programming practice to do things the right way the first time. It may be unnecessary, but I've seen too many shortcuts cause problems when people move to a new/'next gen' platform/code language (e.g., referencing a control's default value like "txtName =" instead of "txtName.Text =" when moving from VB to .Net). Falling in behind the resident "guru" to perform fixes/upgrades has been a nightmare sometimes because speed was more important than quality.

Formula for SW dev: Fast, Good, Cheap; Pick two. Since CFO's seem to rule, it's usually Fast and Cheap (which is why I stay employed!)

 

[CajunCenturion]

foolio and DCCoolBreeze are both saying the same thing, that you need to close and clear your objects. However, I would take it a step further, with checks along the way, to insure that it's a valid time to perform the desired operation. For example, this is how I clean up a RecordSet (this is ADO)

If Not (RecSet Is Nothing) Then
   If (RecSet.State = adStateOpen) Then
      If (RecSet.EditMode <> adEditNone) Then
         RecSet.CancelUpdate
      End If
      RecSet.Close
   End If
   Set RecSet = Nothing
End If

with of course similar checks on the Connection Object (ADO) or the Database Object (DAO) before cleaning them up.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[JeremyNYC]

CC,

You use that code every time you close an object? That's a lot of code. Not saying it's wrong to do, I've just never seen it before.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JeremyNYC]

Foolio,

I don't think that's right. I have a database that never uses any references to any external databases. It wasn't closing correctly (db would shut but I'd have to use the three-finger salute to close Access). Quick look and there was no close/set to nothing in the routine that closes the database (I keep a db object open the whole time I'm in there). Put it in there, and it closes just fine.

Very Weird: I just went to test this same database, by commenting out those two lines. Closes fine now. But when I was fixing this, I very specifically closed the database, added those two lines, and closed the database again, just to make sure that that was going to be the fix. And it was.

So...it may not be a problem every time, but it still looks to me as if it's a potential problem if you don't include that code in your database, and one I don't want to stick my users with.

Jeremy



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[strongm]

If foolio12 would like to explain why there is a difference and why, therefore, you can get away with his advice...

 

[CajunCenturion]

JeremyNYC, yes, perhaps it is a bit of code, but I do use it.

The reason that I use it all to prevent run-time errors and memory leaks.

If you try to close a RecordSet with an edit pending, then you will a run-time error 3219, "Operation not allowed in this Context". That is why I check the Edit status before attempting the close. Also, if you try to close a RecordSet (or check the editstate) when the RS is not open, you will get a run time error 3704, "Operation not allow when the object is closed", hence the State check. And of course, to avoid the object error 91, the check for not nothing to begin with.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[JeremyNYC]

CC,

Hmm. Makes tons of sense. Do you just keep this in a separate function and call it at the end of each sub or funciton where appropriate? It's actually quite nice, and quite smart.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[psemianonymous]

Here's where I got the information. Boom:

http://groups.google.com/groups?hl=...a+no-op&meta=group%3Dcomp.databases.ms-access

It basically says that when you use the .Close method on your local DAO.Database object (via CurrentDB() or DBEngine(0)(0) ), the .Close method does nothing.


But if you're actually having problems with code if .Close is not specifically used, then I stand corrected...but you just retracted that statement.

In earlier versions of Access (2 and earlier), apparently this (db.Close) was a source of bugs.


Enjoy your day.


Pete

 

[psemianonymous]

And of course, note that I'm not linking to a Microsoft whitepaper on the subject, but a newsgroup posting on USENET. Take it as you will. I trust the source, though.

 

[CajunCenturion]

I've been known to have a Public Sub in a module

Public Sub CloseRecordSet (RecSet as ADODB.RecordSet)
...

and from appropriate sections of code

CloseRecordSet CurrentRS

but to be honest, I wonder if that setup does exactly what I want it to do.

I have also used a sister sub (or standard in-line code) to handle the connection objects, and although I definately use that in VB projects, I'm not sure that I've ever (yet) had to create a new connection object from within VBA, but I can't remember having multiple DB's projects in VBA either. Normally in VBA I just use CurrentProject.ActiveConnection.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[JeremyNYC]

Foolio,

Michka worked on the development team for Access, at MS. I don't like his attitude much (though it doesn't even really rear its head in this post), but I don't disbelieve too much of anything he says. He knows his stuff.

I somehow remembered this post as saying that db.close didn't do anything but that set db = nothing did. I just put those two lines together by reflex, because that's what I learned to do. But I was wrong about what this post says, clearly.

But here's the thing: even though my test of this database shows that Access closes without this, I also know htat it wasn't closing until I added those two lines. I'm still a little unsettled by this. Hopefully later today I'll get a chance to revivew a backup copy and check out what's going on with this issue.

In any case, thanks for the thread, and the link to Michael's post.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[LNBruno]

Okay, I just had an ugly thought: I've been putting all my code/forms/queries/etc in one MDB and leaving the data in another MDB to allow me to modify the programming without impacting the data. Has that concept suddenly become "old school?"

 

[JeremyNYC]

LNB,

That's absolutely the right thing. What here made you think otherwise?

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[faeryfyrre]

This is one of the more interesting threads i've read recently.

I think I agree with Foolio that db.close does nothing when your connected to the CurrentDB. The database can't/shouldn't close when your in it.
However, i do think it's good practice to Set db = NOTHING so that the system resources assigned for/to the local variable db are released.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Quehay]

I've wondered about this too. Based on recent reading in the Getz,Litwin "Bible" here's my understanding (subject to correction):

DBEngine(0)(0) is a reference to objects/collections that are persistent when Access is open, so you would close this.

CurrentDb() is a function that returns a pointer to the above, so you've got the resources, admittedly small amount, tied up in keeping that pointer variable alive.



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com