Which one is safer?

  • Thread starter Thread starter mota
  • Start date Start date
M

mota

Hi;
In most procedures i use:
Dim DB as DataBase
Set DB=DBEngine(0)(0)
I know i must close and reset all object variables at the end of
SubRouthines or Functions.But,since i use commonly the variable DB for all
database variables,i dont know which one of the following is better to save
system resources more efficient:
DB.Close
or
Set DB=Nothing
or
DB.Close:Set DB=Nothing

I have a similar question about "Set Rs=DB.OpenRecordSet(aTableName)" and
will use your recommendation for it too.
Thank you in advance for your help.
 
If you really want to be safe...

db.Close
Set db = Nothing

And make sure your error handler takes care of this as well.
 
Paul said:
If you really want to be safe...

db.Close
Set db = Nothing

And make sure your error handler takes care of this as well.
Click to expand...

No, mota should *not* close the Database object returned by
DBEngine(0)(0). I don't think any recent version of Access will permit
this anyway, but if they would, Access would cease to function properly.
A good rule of thumb is, "If you opened it, close it; but if you didn't
open it, don't close it."

So if you open a database ...

Set db = DBEngine(0).OpenDatabase "MyDB.mdb"

.... then when you are done with it, close it before setting the Database
variable to Nothing:

db.Close
Set db = Nothing

But if you just get a reference to an existing, open database object ...

Set db = DBEngine(0)(0)

.... then when you are done with it, just set the Database variable to
Nothing:

Set db = Nothing

People used to get bitten by this in the old days, before MS put in
checks to prevent the user from closing the database that Access is
currently using.
 
As I understand it, Dirk's right when he says db.Close does nothing. Former
MVP (now Microsoft employee) Michael Kaplan categorically stated that the
Close method does "...absolutely nothing.".

Also, under most circumstances, creating a reference using DBEngine(0)(0)
will work fine, there are apparently some circumstances (notably, Michael
says, when developing addins) where DBEngine(0).Databases does not return a
fully-updated Databases collection, which is why we have a CurrentDb()
function. CurrentDb refreshes the Databases collection before returning the
"current" Database object (which is why CurrentDb has such poor performance
in comparison to DBEngine(0)(0) ).

When needing a Database object outside a loop, I always use CurrentDb
(despite the once-off performance hit), but inside a loop, I'm cautious and,
depending on the situation and whether I'm prepared to handle a performace
hit, may use DBEngine(0)(0) instead.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Then you recommend using CurrentDB() rather than DBEngine(0)(0)?Despite its
poor performance?(at least,when outside a loop)
Thank you for ur attention.
 
My case is the last one,so would use Set DB=Nothing
I dont know why one may want to open the *Current* database,instead of
setting a refference to it.Does it have any excess capability?I use it just
to open and work with a foreighn database.
Thanx.
 
Mota,

Your original question related to the safer method of closing a Database
object, which Dirk has explained.

Which method you should use to create a reference to the Current Database
object is entirely up to you, and is dependent on whether you need to ensure
that the Databases collection is up-to-date, and whether you can stand the
performance hit if doing it inside a loop.

Under most circumstances, it's highly unlikely that you'd need to worry
about the Databases collection being up-to-date, and if you were, you could
just issue a DBEngine(0).Databases.Refresh prior to calling DBEngine(0)(0).
This would still be about 60-70 times faster than calling CurrentDb.

CurrentDb and DBEngine(0)(0) are not the same object internally. Access
maintains a single permanent reference to the current database, but
CurrentDb temporarily creates a new internal object; one in which the
collections are guaranteed to be up-to-date, and returns a reference to a
Database object which refers to the current database (which occupies
position 0 in the Databases collection). I'm assuming you already know that
immediately after CurrentDb executes and returns a pointer, the internal
object is destroyed, so I won't bother going into that.

So it's still up to you which one you use. I find everyone has their
preference; some logical, some not (in my case, not). I just feel more
comfortable using CurrentDb as a standalone reference to the current
database (outside a loop), despite the performance hit. But if I'm creating
references in a loop, DBEngine(0)(0) has to be the way to go.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Mota said:
My case is the last one,so would use Set DB=Nothing
Click to expand...

I think that would be best.
I dont know why one may want to open the *Current* database,instead of
setting a refference to it.
Click to expand...

I can't think of a good reason.
Does it have any excess capability?I use
it just to open and work with a foreighn database.
Click to expand...

Right.
 
Back
Top