When can I us CurrentDb??

  • Thread starter Thread starter Fia
  • Start date Start date
F

Fia

Hi
I wonder when I can use CurrentDb or when I have to make an object of type
Database.
I tried to use this code

Dim fld as Field
Dim tblDef as TableDef
Set tblDef = CurrentdB.TableDefs("Categories")
With tblDef
For Each fld In .Fields
Debug.Print " " & fld.Name & " " & fld.Type
Next
end With

,but I got the error 3420 "object invalid or no longer set"
Then I used an object of type Database and it worked.

Are there any prescriptions on when you can use CurrentDb and when you have
to make an object of type Database?

Thank's
Fia
 
The code is DAO. In the code editor, open the References window
(Tools|References) and see if Microsoft DAO x.x Object Library" has a check
next to it. If not, scroll down the list and check it. Also, make sure that
you have

Option Explicit

Right below or above the line Option Compare Database at the top of the
module. This will help catch typos.
 
CurrentDB disappears when you move to the next line. If you
want to use it some more (for example to look at a tabledef
in it), you have to create a reference to it. Objects don't
disappear until all the references are removed.

One way to hold onto a reference to CurrentDB is to use
a DB variable:
set DB = CurrentDB.

Another way is to use an object that internally creates
a reference. A recordset is the only common object which
does that:
set RS = CurrentDB.OpenRecordset

Where you don't mind that CurrentDB disappears when you
move to the next line, you can continue to use it:

CurrentDB.Execute sSQL

If you have a lot of lines that use CurrentDB.Execute,
you will want to use a DB variable anyway:
set DB = CurrentDB.
DB.Execute sSQL
because getting a new CurrentDB on every line, and then
letting it go, is slower than just referring to the same
copy over again.

(david)
 
david said:
Another way is to use an object that internally creates
a reference. A recordset is the only common object which
does that:
set RS = CurrentDB.OpenRecordset
Click to expand...

I didn't know that. Can you reference any additional documentation of that? TIA.

-Greg.
 
Back
Top