What's Benefit of Dim db vs Currentdb ?
Mike Thomas
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?
Many thanks
Mike Thomas
Marshall Barton
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
--
Marsh
MVP [MS Access]
George Nicholson
This article is for Access 95 & 97 but the behaviour is unchanged in
subsequent versions (at least 2000, XP/2002), AFAIK (and if it *IS* changed,
I support multiple versions so its immaterial).
Set rst = CurrentDB.OpenRecordset...
-CurrentDB is a temp pointer that loses it's value as soon as the rst is
set, leaving the rst without a db reference in any subsequent usage, causing
compile errors.
Set db = CurrentDB
Set rst = db.OpenRecordset
-1) db is declared as a database object and gets an assignment
- 2) the non-temp database object is used to qualify the rst object. This
reference is maintained.
I think I've seen some people indicate that it is no longer necessary to do
this. However, the few times I've tried to do it without explicitly
declaring a db variable I get errors, so I ignore "it isn't necessary"
suggestions.
--
George Nicholson
Remove 'Junk' from return address.
"Mike Thomas" <mi...@ease.com> wrote in message
news:%23Xhl%23SG6E...@TK2MSFTNGP15.phx.gbl...
Dirk Goldgar
news:e13CuIH...@TK2MSFTNGP15.phx.gbl
> http://support.microsoft.com/default.aspx?scid=kb;en-us;167173
>
> This article is for Access 95 & 97 but the behaviour is unchanged in
> subsequent versions (at least 2000, XP/2002), AFAIK (and if it *IS*
> changed, I support multiple versions so its immaterial).
>
> Set rst = CurrentDB.OpenRecordset...
> -CurrentDB is a temp pointer that loses it's value as soon as the
> rst is set, leaving the rst without a db reference in any subsequent
> usage, causing compile errors.
>
> Set db = CurrentDB
> Set rst = db.OpenRecordset
> -1) db is declared as a database object and gets an assignment
> - 2) the non-temp database object is used to qualify the rst object.
> This reference is maintained.
>
> I think I've seen some people indicate that it is no longer necessary
> to do this. However, the few times I've tried to do it without
> explicitly declaring a db variable I get errors, so I ignore "it
> isn't necessary" suggestions.
If you look closely at that KB article, you'll find that it never
mentions the Recordset object. It uses a TableDef object as an example,
and the behavior is the same for QueryDef objects, but it is
specifically *not* the same for Recordset objects (though the article
doesn't mention this fact). In all versions of Access since A97, and
probably A95 though I don't have a copy to test, the DAO Recordset
object maintains an internal reference to the Database object from which
it was opened, thus keeping that object from going out of scope and
being destroyed.
What this means is that like this:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset( ... )
' ... do things with rs ...
rs.Close
Set rs = Nothing
will work without errors. However, code like this:
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs("MyTable")
' ... do things with tdf ...
Set tdf = Nothing
will *not* work, because the database object returned by CurrentDb, on
which tdf depends, goes out of scope and is destroyed as soon as
execution passes to the next statement after "Set tdf = ...".
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Mike Thomas
than I expected.
Mike Thomas
"Mike Thomas" <mi...@ease.com> wrote in message
news:%23Xhl%23SG6E...@TK2MSFTNGP15.phx.gbl...
George Nicholson
Actually, this thread made me wonder if that was the case (OpenRecordset
handling database references differently than TableDefs or QueryDefs).
Thanks for the confirmation.
If I recall, the article does state that the problem is encountered with
objects that specifically require a database object (and *only* a database
object), which TableDefs and QueryDefs do. OpenRecordset is different in
that 1) it is a method and 2) its "Applies To" list consists of more than
just the Database object. I guess the superficial similarities outweighed
the differences in my head and I've always handled declarations of all 3 the
same way.
I will incorporate this in my work going forward, but I don't see myself
rushing off to "correct" any of my older "verbose" code anytime soon. :-)
Thanks again,
--
George Nicholson
(Please post responses to newsgroup but remove "Junk" from return address if
used)
"Dirk Goldgar" <d...@NOdataSPAMgnostics.com> wrote in message
news:e6MagzL6...@TK2MSFTNGP10.phx.gbl...
Dirk Goldgar
>
> I will incorporate this in my work going forward, but I don't see
> myself rushing off to "correct" any of my older "verbose" code
> anytime soon. :-)
Oh, I'm not even suggesting that you change your ways. Using an
explicit database object has some arguable advantages, even if the
object will only be used once. I just wanted to clarify that the
occasional "Set rs = CurrentDb.OpenRecordset(...)" won't do any harm.