Mike said:
I'm repeatedly typing something like this:
strSQL = "Select * from..."
set rst = currentdb.openrecordset ( _
strSQL, dbopenforwardonly)
Most postings I see here suggest:
Dim db as database
strSQL = "Select * from..."
set rst = db.openrecordset ( _
strSQL, dbopenforwardonly)
What is the advantage of instantiating a db database object?
CurrentDb is a FUNCTION that constructs and returns a sort
of clone of the current database. Each time you call the
function you get a different instance of a database object.
If you want to use the same instance of the database object
returned by CurrentDb(), you need to set an object variable.
Think about the implications of this statement:
(CurrentDb Is CuurentDb) is never true
CurrentDb() does a lot of work to refresh all the database's
collections. While this guarantees a reliable result, it
does take a lot of work. CurrentDb() is somewhere around
5000 times slower than DbEngine(0).Databases(0), so you do
not want to use CurrentDb() inside a loop or any more often
than needed.
You **may** be able to use DbEngine(0).Databases(0) to refer
to the actual current database object, but there is no
guarantee that Dabases(0) is the right reference. Besides,
the collections in DbEngine may not be refreshed to the
level that CurrentDb() takes care of.
One other point, some operations set property values in the
db object, which will be immediately discarded if you don't
set the object variable. An example of this is the
RecordsAffected property that's set by the Execute method.
Oh yes, if you do do it the "right" way and Set db =
CurrentDb(), don't forget to dereference the object when
you're done with it by using Set db = Nothing
