'instance' question

  • Thread starter Thread starter Tcs
  • Start date Start date
T

Tcs

I'm going back thru my recent code, trying to...ahh..."clean it up", as pertains
to DAO database and recordset objects.

In a given module, my first function is public, then my subsequent functions are
private, only for use by the preceding public function.

While going thru my code to make my changes, I've noticed that I have the
following situation:

I'll set my database object in the main (public) function (to execute queries,
etc.), then may set it again when I call one of the private functions if I want
to open a recordset. (I'll normally close the recordset, before leaving the
private function.)

Is this okay? Should I call my database and/or recordset object "1" or "2" if I
haven't closed the previous one (instance)? Or is VBA taking car of this for
me? This is probably a really basic (no pun intended) question, but I don't
"know". My code seems to run okay, but I don't know if I'm just..."lucky".

I appreciate your input, thanks in advance,

Tom
 
Within your main function, you have things like this:
Dim db As DAO.Database
Set db = CurrentDb()

Then your private functions also have their own database variable? That will
work fine.

If you want to be more efficient, you can actually pass objects from one
function to another, instead of instantiating different variables in
different procedures. Here's a basic example:

Public Function MyMainFunc()
Dim db As DAO.Database

Set db = CurrentDb()
Call MySubFunc(db)

Set db = Nothing
End Function

Private Function MySubFunc(db As DAO.Database)
Debug.Print db.Name
End Function

In the same way, you can pass recordsets and other objects as well.
 
If you have objects declared at module level, you may get
confused. I don't declare objects at module level for this
reason.

If you have objects declared at module level, you can
set and reset and reset them as often as you like. VB
takes care of closing and releasing when you reset the
object. If you do this, you may get confused...

If you have objects declared at module level, they will
never be closed/released until you close Access, unless
you explicitly close/release or reset them. If you are
using Win 3.1 with 8 MB of memory, you may wish to close
objects to release memory.

(david)
 
Back
Top