A fundamental difference between object variables and other variab

J

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
 
C

Clifford Bass via AccessMonster.com

Hi,

I cannot really explain why, but if you modify it to so it will work:

Sub Prac6()

Dim Dbs As DAO.Database
Dim Fld As DAO.Field

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

Debug.Print Fld.Name

End Sub

For whatever reason when you use CurrentDB.SomeObjectProperty directly
it works. But when you use use it indirectly, such as:

Set SomeObjectVar = CurrentDB.SomeObjectProperty

It does not copy the reference of the SomeObjectProperty item into the
SomeObjectVar variable. It may have something to do with call stacks and
references to objects and scopes of variables.

When you do:

strValue = CurrentDB.SomeObjectProperty.SomeNonObjectProperty

It works because you are returning a non-object based value, such as a
string. This is why your Prac4 works.

The solution, as shown above, is always to declare a local database
variable when you are going to reference objects in CurrentDb and then set it
to CurrentDb.

Hope that helps,

Clifford Bass
 
M

Marshall Barton

JGPatrick said:
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
Click to expand...


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 = ....
 
C

Clifford Bass via AccessMonster.com

Hi,

Some added information that may explain it. Application.CurrentDB is
actually a method, not a property. Unlike Application.CurrentProject, which
is a (persistent) property. The help says "In Microsoft Access the CurrentDb
method establishes a hidden reference to the Microsoft Office 12.0 Access
Conectivity [sic] Engine object library in a Microsoft Access database ." So,
if you do not capture that reference by assigning it to something, it goes
out of scope and is probably deleted as soon as the method is completed.
This is my guessing and could be inaccurate.

Clifford Bass
 
D

David W. Fenton

Public Sub Prac6()
Dim Fld As DAO.Field

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

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.
 
C

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
 
D

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."
 
J

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.
 
D

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>.
Click to expand...

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.
 
C

Clifford Bass via AccessMonster.com

Hi David,

Yeah, it does seem fuzzy. Because the CurrentDB method looks like a
property, I was contrasting it with an actual object property (DBEngine) to
show the difference. And you may be right in calling CurrentDB a function
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:

------------------------------------------------------------------------------
--
Private m_DB As DAO.Database

Public Property Get DB() As DAO.Database

If m_DB Is Nothing Then
Set m_DB = DBEngine.OpenDatabase("Database1.mdb")
End If
Set DB = m_DB

End Property

Public Function GetDB() As DAO.Database

Dim dbTemp As DAO.Database

Set dbTemp = DBEngine.OpenDatabase("Database1.mdb")
Set GetDB = dbTemp

End Function

Public Sub Testing()

Dim DBFld1 As DAO.Field
Dim DBFld2 As DAO.Field

On Error Resume Next

Debug.Print "---------------------------"
Debug.Print "DB.TableDefs(0).Fields(0).Name = " & _
DB.TableDefs(0).Fields(0).Name
If err.Number <> 0 Then
Debug.Print "Unable to access DB.TableDefs(0)." & _
"Fields(0).Name Err #" & err.Number & ": " & _
err.Description
err.Clear
End If

Set DBFld1 = DB.TableDefs(0).Fields(0)
If err.Number <> 0 Then
Debug.Print "Unable to set DBFld1. Err #" & _
err.Number & ": " & err.Description
err.Clear
End If
Debug.Print "DBFld1.Name = " & DBFld1.Name
If err.Number <> 0 Then
Debug.Print "Unable to access DBFld1.Name. Err #" & _
err.Number & ": " & err.Description
err.Clear
End If

Debug.Print "GetDB.TableDefs(0).Fields(0).Name = " & _
GetDB.TableDefs(0).Fields(0).Name
If err.Number <> 0 Then
Debug.Print "Unable to access GetDB.TableDefs(0)." & _
"Fields(0).Name Err #" & err.Number & ": " & _
err.Description
err.Clear
End If

Set DBFld2 = GetDB.TableDefs(0).Fields(0)
If err.Number <> 0 Then
Debug.Print "Unable to set DBFld2. Err #" & _
err.Number & ": " & err.Description
err.Clear
End If
Debug.Print "DBFld2.Name = " & DBFld2.Name
If err.Number <> 0 Then
Debug.Print "Unable to access DBFld2.Name Err #" & _
err.Number & ": " & err.Description
err.Clear
End If

m_DB.Close
Set m_DB = Nothing
err.Clear

End Sub
------------------------------------------------------------------------------
--

Results:
---------------------------
DB.TableDefs(0).Fields(0).Name = DateCreate
DBFld1.Name = DateCreate
GetDB.TableDefs(0).Fields(0).Name = DateCreate
Unable to access DBFld2.Name Err #3420: Object invalid or no longer set.

Of course, I am not certain about that. But maybe my experimentation
will help someone in some way.

Clifford Bass
 
D

David W. Fenton

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

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
Click to expand...

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:
Click to expand...

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?
 
C

Clifford Bass via AccessMonster.com

Hi David,

Responses below.
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.
Click to expand...

I Agree.
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).
Click to expand...

It can be kind of like quicksand when the definitions get refined. What one
learned earlier about something and how is is to be perceived now.
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?
Click to expand...

In the one instance it provides access to the database through a
property. Which demonstrats what would happen if CurrentDB were a property
that provided direct access to the current database. And in the second
instance it provides access to the database through a function. The second
instance produces the same type of behaviour as CurrentDB. So the second
instance may demonstrate something of what is going on when you invoke
CurrentDB.

Clifford Bass
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Switching from Corel Paradox to Access 2010 5
list of table names and fields 5
Inconsistency of CurrentDb 3
Publisher crashes changing format of paragraph in Selection Textrange. 0
A general rule for using methods that create objects 5
CreateRelation to Other Tables file, throws 3409 error 0
Code wont work 2

Top