Combined VBA Statements with Colon on One Line 2

[kjv1611]

Previously, I had been using this sort of setup to clear out recordset and database objects after using them in code:

[green]'Setup variables[/green]
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTableName")

[green]'Run whatever code here
'
'

'Clean-up objects:[/green]
rs.Close
Set rs = Nothing
db.close
Set db = Nothing

Well, then one day I got an error, b/c the recordset object had already been closed/cleared out by an earlier statement, so I thought I would try to adjust it a little to get it to always work without error, and also to get it all to fit on one line... so I changed it to this:

If rs Is Nothing Then Else rs.Close: Set rs = Nothing
If db Is Nothing Then Else db.Close: Set db = Nothing

Well, I just got to thinking this morning (I have had an error or two pop up with this in the past, but just fixed the error and went on about my business), and thought: what if the colon doesn't cause things to work the way I was thinking...

If I remember correctly, entering a colon at the end of a line will allow you to run another line immediately after it. So that sounded like the perfect fit here. However, I'm beginning to wonder differently.

In my new method, does it actually run like this:

If rs Is Nothing Then
Else
  rs.Close
[highlight][b]  Set rs = Nothing[/b][/highlight]
End If

Or, does it run (as I'm now beginning to think it does) as this:

If rs Is Nothing Then
Else
  rs.Close
End If

[highlight][b]Set rs = Nothing[/b][/highlight]

Thanks in advance for any thoughts or references on this.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PHV]

It does as your 1st expectation, ie the set rs = nothing instruction is part of the else statement.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, I'd use this:

If Not(rs Is Nothing) Then rs.Close: Set rs = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Shouldn't you also check if the rst is Open before you close it?

Have fun.

---- Andy

 

[vbajock]

I usually just include an On Error Resume Next statement in my cleanup handler:

'******
Exit_h:
'******
On Error Resume Next
rs.Close
Set rs = Nothing
db.close
Set db = Nothing
Exit Function

If the object is closed, it's closed, why make a big deal out of it.

 

[kjv1611]

Andrzejek,

How do you check if the recordset is open?

Perhaps I've overlooked that one?

And thanks for the clarification, PHV.

The On Error Resume Next is something I do at that point as well, quite often, but I just wanted to clarify for certain that I understand what is going on.

Thanks for all the replies.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

Just a guess here (from ADODB recordset):

If rst.State = 1 Then 
    rst.Close
End If

but you can just use vbajock's logic and ignore any errors

Have fun.

---- Andy

 

[MajP]

Most people consider the colon bad practice. As you proved, it makes it hard to read and determine what is happening. It makes debugging hard because if the code breaks on that line which part is it? If you want to put a breakpoint, you can only do that for the first line of code.

 

[MajP]

Also if you are using the currentdb or dbengine(0)(0) there is absolutely no reason to close the db. That does nothing. There is an extremely rare reason to need close the recordset, and no reason to set your variable pointers to nothing. With that said you can use that code if it makes you feel better, but it is far from needed. I never set objects to nothing and rarely explictly close a recordset unless some kind of loop.

A lot of this is Access and VB myth, and some very old issues with DAO that as far as I know does not exist in ADO or current DAO.
Here are two good articles.

http://www.keysound.com/html/ch_15_much_ado_about_nothing.htm

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

 

[kjv1611]

Thanks for that information, MajP! I've heard brief mentions that it's not necessary to close/destroy objects, but never came across anything that I could say for certain that it was from a reputable source.

On Kaplan's mention on Groups.Google.com that you linked to, another item came up that I'm curious about. I've always used CurrentDb, but it sounds like using DbEngine(0)(0) would tend to be at least a little faster, according to this item:

4) The only "official" benefit that CurrentDb has over
DBEngine.Workspaces(0).Databases (a.k.a. DBEngine(0)(0) is the fact
that all of the collections are guaranteed to up to date. This is easy
to do internally in Access but is a bit harder to do from DAO -- I
believe a function in DAO that had all collections up to date would be
about 8 times slower then CurrentDB (which is itself over 5000 times
slower than DBEngine(0)(0). Note that if you do not need collections
up to date that this benefit is not too terribly important. It is
terribly important in Access wizards, which create objects and then
have to be able to count on them being present. It has proven
important in other apps I have written, enough so that I have my own
methods of getting the current db that will selectively refersh
particular collections.

So, if you're just running a quick one-time process, or have something that is just going to loop through the recordset while no one else is affecting it, then is it always safe to just use DBEngine(0)(0)?

I don't even know what DBEngine(0)(0) means. I've just always used CurrentDb, b/c well, that's the way I initially learned to it, and stuck with that. Not to mention, it's easier to remember than the other, at least I think so.

Maybe I need to do some research into the DBEngine() usage.

The whole idea of the collections is another thing that I've only briefly read/heard about. If I remember correctly, the collections are each group of tables, queries, forms, etc... each object type is split out into a collection? Well, another item which probably deserves more research on my part.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

Speed vs up to date. In most cases the difference is not noticeable

Database Objects

The Databases collection contains all Database objects currently open within a Workspace object. In DAO, a Database object represents a currently open database. This can be a native Microsoft Jet database or an external database. You can have multiple databases open at one time, even databases of different types.

In Visual Basic, after you open a database and return a reference to it by using the OpenDatabase method, you can refer to the database according to its position in the Databases collection. If you open only one database, then you can refer to that database in any of the following ways:

DBEngine.Workspaces(0).Databases(0) DBEngine.Workspaces(0)(0) DBEngine(0)(0)

If you're working in Microsoft Access, 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 the collections in the current database. On the other hand, the DBEngine(0)(0) syntax doesn't refresh any collections, so it may perform faster.

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

Note If you specify a value for the connect argument of the OpenDatabase method, you must also specify a value for the preceding read-only argument.

There is a good description and diagram of the data model

http://technet.microsoft.com/en-us/library/cc936710.aspx

There are two things to note in that article. Remember this is an older DAO article (they reference Access 97) and they are talking about doing this using VB external to access. Back then DAO had a bug that could keep the database from releasing if the recordset did not close.

Closing Database Objects

If you open a database with the OpenDatabase method (does not say current db or a pointer to dbengine(0)(0), you should close it explicitly when you are done using it. To close a database explicitly, first use the Close method to close the database, then set the Database object variable to Nothing.

In DAO 3.0 and 3.5, if you explicitly close a Database object that has an open Recordset object with pending edits or uncommitted transactions, the edits are canceled, the transactions are rolled back, and the Recordset object is closed. If you implicitly close the same Database object (by exiting a procedure that has the Database object as a local object variable), the Recordset object stays open.

Note This behavior is different from the behavior of DAO version 2.x. In DAO 2.x, if you explicitly close a Database object that has an open Recordset object, an error occurs. If you implicitly close the same Database object, the Recordset object is closed as well. DAO 2.x had Dynaset objects (replaced by dynaset-type Recordset objects in DAO 3.0). If you explicitly or implicitly close a Database object that has an open Dynaset object, the Dynaset object stays open.

so my point about not needing to close the current db or (0)(0) is still completely valid. It does nothing. It is ignored. They state this here.

Microsoft Access Users In Microsoft Access, a default workspace, Workspaces(0), is automatically created when you open a database through the user interface. Microsoft Access ignores attempts to close this default Workspace.

But if working with multiple databases or external databases then I would close them.

Closing Recordset Objects
You should always close a Recordset object after you finish using it. Close the Recordset object by using its Close method. Then set the Recordset object variable equal to Nothing.

I still believe that this is the social myth, because early DAO did not function as designed. See the discussion of DAO 2.0. I do not believe this is required in newer versions of DAO or ADO. If someone differs then I would be interested in an explanation.

 

[vbajock]

The reason given for not explicitly closing objects is that the VBA garbage collection routines handle it for you automatically when your function or sub ends. Since I don't like depending on code I can't see to do something, I prefer to close these objects explicitly. While I can't point at anything specific, my applications seem less liable to suffer abnormal terminations and unexplained errors since I have made a habit of doing so.

 

[MazeWorX]

I have to agree with vbajock here it doesn't hurt to close these objects in your own code. As a rule I will close db and rs when I'm finished with them then I know for a fact they are closed.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[strongm]

>the VBA garbage collection routines

VBA does not have garbage collection

>handle it for you automatically when your function or sub ends

VBA always sets an object to nothing (i.e it simply decrements the reference count to the underlying COM object) when it goes out of scope. That's just how VBA implements objects (and, in fact, all variables). There are no exceptions - VBA doesn't sometimes decrement the reference count and other times not. It always does. And it is COM, not VBA, handles the destruction of the object once the reference count reaches 0. (there are some extrememely rare situations where the dereferencing order is important and, since you cannot control the order in which VBAs own dereferencing occurs then you might have to do it manually

> Since I don't like depending on code I can't see to do something

You can't see how it disposes of strings or other variables either. Do you manually zero those?

 

[vbajock]

Reference counter, garbage collection, whatever.