A fundamental difference between object variables and other variab

[JGPatrick]

I would appreciate it if someone would explain to me why the subs Prac4 and
Prac5 below work, but Prac6 does not. The error is in the debug.print
statement, not the Set statement.

Sub Prac4()

Dim FldName As String

FldName = CurrentDb().TableDefs(0).Fields(0).Name

Debug.Print FldName

End Sub

Sub Prac5()

Dim Dbs As DAO.Database

Set Dbs = CurrentDb()

Debug.Print Dbs.Name

End Sub

Sub Prac6()

Dim Fld As DAO.Field

Set Fld = CurrentDb().TableDefs(0).Fields(0)

Debug.Print Fld.Name

End Sub

 

[Douglas J. Steele]

And the error you get is what...?

 

[Marshall Barton]

I would appreciate it if someone would explain to me why the subs Prac4 and
Prac5 below work, but Prac6 does not. The error is in the debug.print
statement, not the Set statement.
[snip]
Sub Prac6()

Dim Fld As DAO.Field

Set Fld = CurrentDb().TableDefs(0).Fields(0)

Debug.Print Fld.Name


End Sub

I believe the 6th one fails because CurrentDb is destroyed
after the line is executed. I expect this to work:

Sub Prac6()
Dim db As DAO.Database
Dim Fld As DAO.Field
Set db = CurrentDb()
Set Fld = db.TableDefs(0).Fields(0)

Debug.Print Fld.Name

End Sub

There are cases, such as OpenRecordset, where it appears
that you do not need to use Set db = ..., but that's only
because Access creates an internal reference to the
recordset object. Because I can not decipher where/when
these magical things happen, I always use Set db = ....

 

[David W. Fenton]

Application.CurrentDB is
actually a method, not a property.

Actually, it's a function and an object.

 

[David W. Fenton]

Public Sub Prac6()
Dim Fld As DAO.Field

Set Fld = CurrentDb().TableDefs(0).Fields(0)
Debug.Print Fld.Name
End Sub

This works:

Public Sub Prac6()
Debug.Print CurrentDb().TableDefs(0).Fields(0).Name
End Sub

This will also not work:

Public Sub Prac7()
Dim tdf As DAO.TableDef

Set tdf = CurrentDb().TableDefs(0)
Debug.Print tdf.Name
End Sub

Get the picture? The reason is because collections of the CurrentDB
function/object are out of scope once you leave the line where
CurrentDB is called.

On the other hand Methods of CurrentDB still work:

Public Sub Prac8()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(CurrentDb().TableDefs(0).Name)
Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing
End Sub

However, that's not one I'd recommend, to be honest.

 

[ChrisO]

We can do a one-liner or the With Statement will hold the reference.


Sub Prac6a()

Debug.Print CurrentDb().TableDefs(0).Fields(0).Name

End Sub


Sub Prac6b()

With CurrentDb()
Debug.Print .TableDefs(0).Fields(0).Name
End With

End Sub


Sub Prac6c()

With CurrentDb()
With .TableDefs(0)
Debug.Print .Fields(0).Name
End With
End With

End Sub


Sub Prac6d()

With CurrentDb()
With .TableDefs(0)
With .Fields(0)
Debug.Print .Name
End With
End With
End With

End Sub


Sub Prac6e()
Dim I As Integer

With CurrentDb()
With .TableDefs(0)
For I = 0 To .Fields.Count - 1
With .Fields(I)
Debug.Print .Name
End With
Next I
End With
End With

End Sub

 

[JGPatrick]

Runtime error 3420: Object invalid or no longer set

 

[Douglas J. Steele]

David Fenton gave you the answer: "The reason is because collections of the
CurrentDB function/object are out of scope once you leave the line where
CurrentDB is called."

 

[JGPatrick]

Let me thank all of you for your informative replies.

I will post a question on objects created by methods later today which
ties this discussion to a more general question.

 

[David W. Fenton]

The online help states that it is a Method. See <
http://msdn.microsoft.com/en-us/library/bb237861.aspx>. Compare
this to DBEngine, which the documentation states is a Property.
See < http://msdn.microsoft.com/en-us/library/bb237457.aspx>.

I guess I'm foggy on the terminology, given that I thought a method
*did* something, instead of *being* something (i.e., object, having
methods and properties of its own) or *returning* something (i.e.,
property or function).

DBEngine is not a function, as it returns nothing itself. I see it
as nothing but an object. Keep in mind that the correct comparison
to CurrentDB is not DBEngine, but DBEngine(0)(0), which returns a
pointer referring to the default database open in the default
workspace.

Certainly in A97, the help file calls CurrentDB a function, and the
ADH97 and ADH2000 both still refer to it as a function, but starting
with A2000, the help file calls it a method.

I think that's pretty incoherent, myself.

 

[David W. Fenton]

Yeah, it does seem fuzzy. Because the CurrentDB method looks
like a
property,

Actually, it looks like more than a property. A property could be an
object, but CurrentDB doesn't return the same object, but instead
returns a new memory structure each time it's returned. A property
would always return the same one, seems to me.

I was contrasting it with an actual object property (DBEngine) to
show the difference. And you may be right in calling CurrentDB a
function

It's not *me* who came up with that terminology -- it was what
*Microsoft* called it until A2000, and what experts like Litwin and
Getz were still calling it in the ADH 2000 (though to be fair, that
was written before A2000 was out of beta, and was just a revision of
the ADH 97).

since functions return values whereas subroutines cannot. The
online help for CurrentDB states "In Microsoft Access the
CurrentDb method establishes a hidden reference to the Microsoft
Office 12.0 Access Conectivity Engine object library in a
Microsoft Access database." Based on that note here is some code
that MAY demonstrate what is happening:

I don't quite see how code using DBEngine to open a database
reflects at all on the nature of CurrentDB. Care to explain what you
believe your code demonstrates?

 

[david]

It's worth mentioning the apparent exception:


Public Sub Prac66()

dim rs as dao.recordset

set rs = CurrentDB.OpenRecordset("table1")
debug.print rs.name

End Sub

....unlike tabledefs and fields, RecordSet holds an internal reference to
the DB object...

(david)