DAO Recordset

  • Thread starter Thread starter Michael S. Montoya
  • Start date Start date
M

Michael S. Montoya

I have a question regarding setting a recordset
Currently I use

set rst as DAO.Recordset
rst = currentdb.openrecordset(strsql)

I have seen others first set db as database and rst =
db.openrecordset(strsql)

I usually try and reduce my code as much as possible and use as few
variables as possible, thinking it will run faster. However, I am wondering
if there is a reason I see alot of the database defining. Is it more
efficient or does it run faster?

TIA
 
Quick correction, although you guys probably understood what I meant:

I would use DIM rst as dao.recordset
and SET rst = currentdb.openrecordset(strsql)

got my dim and set backwords, but the question still is out there on the
table if you please. :) :)
 
Michael S. Montoya said:
I have a question regarding setting a recordset
Currently I use

set rst as DAO.Recordset
rst = currentdb.openrecordset(strsql)

I have seen others first set db as database and rst =
db.openrecordset(strsql)

I usually try and reduce my code as much as possible and use as few
variables as possible, thinking it will run faster. However, I am
wondering if there is a reason I see alot of the database defining.
Is it more efficient or does it run faster?

TIA
Click to expand...

If you're only going to use one reference to CurrentDb, then it really
makes no difference whether you write

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strsql)
' ...
Set rst = Nothing

or

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
' ...
Set rst = Nothing
Set db = Nothing

The exact same operations are going to be performed: a database object
will be created that is a copy of the current database, a recordset
object will be created and opened from that database object, then later
the recordset object will be destroyed and then the database object will
be destroyed. In the case where you don't actually define a database
object variable, you aren't really saving much. The database object
still has to be created, and it will remain in existence as long as
there is a reference to it, which in this case means as long as rst
exists, because recordset objects maintain an internal reference to
their parent objects.

If you're going to use the database object multiple times -- for
example, to open multiple recordsets -- then it makes a significant
difference in efficiency to get one copy of the database and use that
copy repeatedly, rather than call CurrentDb multiple times. Each call
to CurrentDb will create a new copy of the database object, and you
don't need that.

So this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
Set rst2 = db.OpenRecordset(strsql2)
' ...
Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

will be more efficient than this:

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strsql)
Set rst2 = CurrentDb.OpenRecordset(strsql2)
' ...
Set rst = Nothing
Set rst2 = Nothing

One reason that people tend to explicitly declare and destroy their
object references even when it isn't absolutely necessary, as in the
one-time use of CurrentDb, is that there have been bugs in the past
where Access/VBA "lost track" of implicitly declared objects, failing to
destroy them when they go out of scope. That would lead to an inability
to fully close Access -- it would remain in memory, and you had to kill
it from the task manager. As far as I know, such bugs are in the past,
but people who've been around a while tend to take the safer course and
explicitly declare and destroy all object references, just in case.
 
Wow, thanks for such a great reply. It was very informative.

There are cases in where I have multiple OpenRecordsets so, I will
definately go back and define the db.

On occasion I have seen where I went to open a database and it wouldnt open,
however the ldb file was created. I would go to Task Manager and seen that
Access was still open. This only happens once in a while with some of my
more complex databases, so I should get in the habit of defining db and look
for other variables that may not be defined.
 
Michael S. Montoya said:
Wow, thanks for such a great reply. It was very informative.

There are cases in where I have multiple OpenRecordsets so, I will
definately go back and define the db.

On occasion I have seen where I went to open a database and it
wouldnt open, however the ldb file was created. I would go to Task
Manager and seen that Access was still open. This only happens once
in a while with some of my more complex databases, so I should get in
the habit of defining db and look for other variables that may not be
defined.
Click to expand...

There are a couple of other bugs that can cause this, too, including one
involving conditional formatting. That last was supposed to have been
fixed by a service pack, so make sure you're up to date.
 
Back
Top