Delaring Global Databases And Recordsets

[DALIEN51]

Dear Readers!

I have been developing Access databases for a number of years now but naively even though I use a number of database and recordset calls repeatedly within those databases I define them each time ie:

DIM MYDB AS DATABASE
SET MYDB = CURRENTDB()

DIM MYTABLE AS RECORDSET
SET MYTABLE = MYDB.OPENRECORDSET("{name of table}")

Finally it has dawned on me that I could set:

DIM MYDB AS DATABASE
DIM MYTABLE AS RECORDSET

globally and thereby only defining them once...

Is it right that I have done thise at the top of a coding module ie

Option Compare Database
Option Explicit
Dim MYDB As Database
DIM MYTABLE AS RECORDSET

??

It would apear so as does work!

However, in the case of MYDB it will always be:

SET MYDB = CURRENTDB()

but it does not let me create:

Option Compare Database
Option Explicit
Dim MYDB As Database
SET MYDB = CURRENTDB()

can I set this anywhere or do I have to declare:

SET MYDB = CURRENTDB()

within each procedure?

Any help most appreacited.

 

[Mile-O]

You can't just put Set DB = CurrentDB outwith a method although you can dimension the Database and Recordset objects publically atop a module.

I wouldn't keep a Recordset object opened globally though as I may use the table(s) that recordset refers to (directly or indirectly) and, having it open in two locations, can cause a write conflict.