Not setting database objects correctly

[William Benson]

I wrote two methods of looping through fields in a table. One doesn't
*always* work -- maybe it never works -- ... and I am trying to find out
why not. Can someone please explain the reason METHOD2 results in the
runtime error "Object invalid or no longer set."? Could it have to do with
having instances of Access 2003 running?

METHOD1 (good)
Sub CountRecords()
Dim ws, db, tdf, F
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set tdf = db.TableDefs("MyTable")
For Each F In tdf.Fields
Debug.Print F.Name
Next F
End Sub

METHOD2 (not working now, but I thought maybe it sometimes has worked)
Sub CountRecords1()
Dim tdf, F
Set tdf = CurrentDb.TableDefs("MyTable")
For Each F In tdf.Fields
Debug.Print F.Name
Next F
End Sub

 

[William Benson]

I think this article comes the closest to answering my question, but I am
still confused why CurrentDB isn't sufficient. Thanks if any more depth can
be offered.

http://msdn.microsoft.com/library/d...us/vbaac11/html/acmthCurrentDb_HV05186439.asp

 

[Alex Dybenko]

Hi
i think Ken Getz has a very good explanation of this in his Access
Developers Handbook.
the idea that CurrentDb is a special kind of object, and it do not hold
reference itself, so you have to declare a db variable and use it.

Set db = CurrentDb

 

[William Benson]

Thanks - I have Ken Getz VBA handbook, not Access Developers Handbook. I
will buy the one you mentioned.

Thanks.

 

[Larry Linson]

Dim ws, db, tdf, F

Dim tdf, F

On another subject, are you aware that each of the variables is Dim'd as a
Variant? It may or may not affect the execution of specific code, but will
slow execution of code using Variants instead of specific

Dim <something> As <variabletype>

Further, each variable must have its own "As" clause. For example: Dim
i,j,k as Integer -- i and j are Variants, only k is Integer.

Larry Linson
Microsoft Access MVP

 

[Dirk Goldgar]

I wrote two methods of looping through fields in a table. One doesn't
*always* work -- maybe it never works -- ... and I am trying to find
out why not. Can someone please explain the reason METHOD2 results in
the runtime error "Object invalid or no longer set."? Could it have
to do with having instances of Access 2003 running?

METHOD1 (good)
Sub CountRecords()
Dim ws, db, tdf, F
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set tdf = db.TableDefs("MyTable")
For Each F In tdf.Fields
Debug.Print F.Name
Next F
End Sub

METHOD2 (not working now, but I thought maybe it sometimes has worked)

Nope, I assure you that it has never worked.

Sub CountRecords1()
Dim tdf, F
Set tdf = CurrentDb.TableDefs("MyTable")
For Each F In tdf.Fields
Debug.Print F.Name
Next F
End Sub

A TableDef object is valid only so long as the Database object from
which it was opened is valid. Unlike the Recordset object, the TableDef
object doesn't maintain an internal reference to its parent Database
object. Therefore, the TableDef object itself can't keep its parent
"alive".

When you call CurrentDb(), a Database object is created and returned.
However, it then remains in existence only so long as a reference to it
exists. In your METHOD2, no object is set to refer to the Database
object returned by CurrentDb, so that object remains in existence only
for the duration of the statement, and is then destroyed. The TableDef
object tdf is then left parentless, and hence invalid.

 

[William Benson]

Thanks for the great explanation. I see now why I have to set a database
object = CurrentDB.

Thanks again!
Bill

 

[William Benson]

Larry, It was bad form on my part and invites evil type conversions, excess
memory consumption, etc as you say -- was doing something quick and dirty to
convey the idea of what was happening (which Dirk explained to me).

Thanks for the comments.