CurrentDB: object invalid or no longer set

[Mike Labosh]

I can't find anything in the help or MSDN about this, but this silly doofus
issue just wasted many hours of my time. All I needed to do was spin on the
stupid Fields collection of a TableDef. Newbie code, right? So what's
wrong with the CurrentDB method?!?

This code doesn't work:

Public Sub CreateIndexes(tableName As String)

' Creates an index on each column of the specified table
' except the surrogate key "ID" column

Dim fl As DAO.Field
Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDefs("qtmpCreateIndex")

'This line dies because .Fields dissapears before fl is assigned
For Each fl In CurrentDb.TableDefs(tableName).fields

If fl.Name <> "ID" Then
qd.sql = "CREATE INDEX " & fl.Name & "IX " & _
"ON " & tableName & " (" & fl.Name & ")"
qd.Execute
End If

Next

End Sub

BUT -- if I get CurrentDB into a variable, it DOES work:

Public Sub CreateIndexes(tableName As String)

' Creates an index on each column of the specified table
' except the surrogate key "ID" column

Dim fl As DAO.Field
Dim qd As DAO.QueryDef
Dim db As DAO.Database

Set db = CurrentDb 'cache CurrentDB in a variable

Set qd = db.QueryDefs("qtmpCreateIndex")

'only difference here is "db" instead of "CurrentDB"
'otherwise it's all the same, and this works
For Each fl In db.TableDefs(tableName).fields

If fl.Name <> "ID" Then
qd.sql = "CREATE INDEX " & fl.Name & "IX " & _
"ON " & tableName & " (" & fl.Name & ")"
qd.Execute
End If

Next

End Sub

 

[Steve Jorgensen]

You made a common error. CurrentDb is not a variable, it's a function that
returns a new DAO.Database object reference. So, a temporary Database object
reference is created on the Set qd = ... line, and it goes out of scope
immediately. Consequently, the querydef is trying to use a Database instance
that might or migh not have been disposed of yet.

What you need to do is dimension a database variable, set it = to CurrentDB(),
then use the variable. Also, DAO can have some resource problems if you don't
release objects in reverse of the order in which they were allocated, so end
your code with...

Set qd = Nothing
Set db = Nothing

 

[Mike Labosh]

You made a common error. CurrentDb is not a variable, it's a function
that

returns a new DAO.Database object reference. So, a temporary Database object
reference is created on the Set qd = ... line, and it goes out of scope
immediately. Consequently, the querydef is trying to use a Database instance
that might or migh not have been disposed of yet.

<complain>
OK, fine, but is it just me or is that just stupid? If the CurrentDB method
returns a pointer to a DAO.Database object, I should be able to use that
pointer without having to put another dumb variable on the stack.

IOW, I should be able to say, CurrentDB.TableDefs("SomeTable").Fields
without the object reference disappearing!! If CurrentDB gives me a
DAO.Database, I should be able to assume that the object actually exists!
ESPECIALLY SINCE I'M *IN* ONE!
</complain>

But in any case I have working code now. I just think the way the CurrentDB
method is implemented by Microsoft is REALLY FRIGGEN LAME!

 

[Steve Jorgensen]

<complain>
OK, fine, but is it just me or is that just stupid? If the CurrentDB method
returns a pointer to a DAO.Database object, I should be able to use that
pointer without having to put another dumb variable on the stack.

IOW, I should be able to say, CurrentDB.TableDefs("SomeTable").Fields
without the object reference disappearing!! If CurrentDB gives me a
DAO.Database, I should be able to assume that the object actually exists!
ESPECIALLY SINCE I'M *IN* ONE!
</complain>

But in any case I have working code now. I just think the way the CurrentDB
method is implemented by Microsoft is REALLY FRIGGEN LAME!

No - it's not just you. It doesn't seem so bad after you're used to it,
though. To me, the implementation was fine if the function name had been more
self-descriptive.

I always use the () after the call to make it look like a function call, not a
variable to folks maintaining my code later. I copied that idea from David
Fenton.