Inconsistency of CurrentDb

[JGPatrick]

In the first procedure below, I define two DAO.database objects. I set
dbsDbs1 = CurrentDb(), and I set dbsDbs2 = dbsDbs1. I then add
a user-defined property to dbsDbs1. This property appears when I print
out the properties for dbsDbs2, as it should. I then delete the property from
dbsDbs2, and this turns out to delete the property from dbsDbs1 as well.

ie, dbsDbs1 and dbsDbs2 are the same database, so modifying one
also modifies the other.

In the second procedure below, I do the same thing except that I
set dbsDbs2 = CurrentDb() rather than dbsDbs1. According to what is
written in the documentation for CurrentDb()
http://msdn.microsoft.com/en-us/library/bb237861.aspx, dbsDbs1 should be the
same database as dbsDbs2, so this should produce the exact same results
as the previous procedure. While adding the property to dbsDbs1 also
adds the property to dbsDbs2, deleting the property from dbsDbs2 does not
delete the property from dbsDbs1.

Can anyone explain this discrepancy?

'This little sub demonstrates the statement that a Set A = B makes A and B
both refer to the same
'place in memory, so that changes to one affect the other.

Sub TestSet()

Dim dbsDbs1 As DAO.Database
Dim dbsDbs2 As DAO.Database
Dim prpPrp As DAO.Property
Dim prpIndex As DAO.Property

Set dbsDbs1 = CurrentDb()
Set dbsDbs2 = dbsDbs1

Set prpPrp = dbsDbs1.CreateProperty("MyProperty", dbText, "Hello")

dbsDbs1.Properties.Append prpPrp

'Both of the lists printed out below should be the same, and should
'contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

dbsDbs2.Properties.Delete "MyProperty"

'Both of the lists printed out below should be the same,
'and should not contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

Set prpIndex = Nothing
Set prpPrp = Nothing
Set dbsDbs1 = Nothing
Set dbsDbs2 = Nothing

End Sub

'The sub below explores what is meant by CurrentDb()

Sub TestCurrentDb()

Dim dbsDbs1 As DAO.Database
Dim dbsDbs2 As DAO.Database
Dim prpPrp As DAO.Property
Dim prpIndex As DAO.Property

Set dbsDbs1 = CurrentDb()
Set dbsDbs2 = CurrentDb()

Set prpPrp = dbsDbs1.CreateProperty("MyProperty", dbText, "Hello")

dbsDbs1.Properties.Append prpPrp

'Both of the lists printed out below should be the same, and should
'contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

dbsDbs2.Properties.Delete "MyProperty"

'Both of the lists printed out below should be the same,
'and should not contain "MyProperty". But dbsDbs1 still
'contains MyProperty.

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

Set prpIndex = Nothing
Set prpPrp = Nothing
Set dbsDbs1 = Nothing
Set dbsDbs2 = Nothing

End Sub

 

[Marshall Barton]

In the first procedure below, I define two DAO.database objects. I set
dbsDbs1 = CurrentDb(), and I set dbsDbs2 = dbsDbs1. I then add
a user-defined property to dbsDbs1. This property appears when I print
out the properties for dbsDbs2, as it should. I then delete the property from
dbsDbs2, and this turns out to delete the property from dbsDbs1 as well.

ie, dbsDbs1 and dbsDbs2 are the same database, so modifying one
also modifies the other.

In the second procedure below, I do the same thing except that I
set dbsDbs2 = CurrentDb() rather than dbsDbs1. According to what is
written in the documentation for CurrentDb()
http://msdn.microsoft.com/en-us/library/bb237861.aspx, dbsDbs1 should be the
same database as dbsDbs2, so this should produce the exact same results
as the previous procedure. While adding the property to dbsDbs1 also
adds the property to dbsDbs2, deleting the property from dbsDbs2 does not
delete the property from dbsDbs1.

Can anyone explain this discrepancy?

'This little sub demonstrates the statement that a Set A = B makes A and B
both refer to the same
'place in memory, so that changes to one affect the other.

Sub TestSet()

Dim dbsDbs1 As DAO.Database
Dim dbsDbs2 As DAO.Database
Dim prpPrp As DAO.Property
Dim prpIndex As DAO.Property

Set dbsDbs1 = CurrentDb()
Set dbsDbs2 = dbsDbs1

Set prpPrp = dbsDbs1.CreateProperty("MyProperty", dbText, "Hello")

dbsDbs1.Properties.Append prpPrp

'Both of the lists printed out below should be the same, and should
'contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

dbsDbs2.Properties.Delete "MyProperty"

'Both of the lists printed out below should be the same,
'and should not contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

Set prpIndex = Nothing
Set prpPrp = Nothing
Set dbsDbs1 = Nothing
Set dbsDbs2 = Nothing

End Sub

'The sub below explores what is meant by CurrentDb()

Sub TestCurrentDb()

Dim dbsDbs1 As DAO.Database
Dim dbsDbs2 As DAO.Database
Dim prpPrp As DAO.Property
Dim prpIndex As DAO.Property

Set dbsDbs1 = CurrentDb()
Set dbsDbs2 = CurrentDb()

Set prpPrp = dbsDbs1.CreateProperty("MyProperty", dbText, "Hello")

dbsDbs1.Properties.Append prpPrp

'Both of the lists printed out below should be the same, and should
'contain "MyProperty"

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

dbsDbs2.Properties.Delete "MyProperty"

'Both of the lists printed out below should be the same,
'and should not contain "MyProperty". But dbsDbs1 still
'contains MyProperty.

For Each prpIndex In dbsDbs1.Properties

Debug.Print prpIndex.Name

Next prpIndex

For Each prpIndex In dbsDbs2.Properties

Debug.Print prpIndex.Name

Next prpIndex

Set prpIndex = Nothing
Set prpPrp = Nothing
Set dbsDbs1 = Nothing
Set dbsDbs2 = Nothing

End Sub

As has been explained ad nauseum in a couple of recent
threads, CurrentDb is a function/methid that returns a
refreshed copy of the default db object. Each call to
CurrentDb returns a different object so the result in your
second example is expected.

Reading VBA Help on the Refresh method (DAO) might help you
understand about persistent changes. E.g. you should get a
different result if you move the line
Set dbsDbs2 = CurrentDb()
down after the line:
dbsDbs1.Properties.Append prpPrp

 

[JGPatrick]

Thanks for the reply.

I did try the code change that you suggested, and it did not change the
outcome.
You get the exact same Debug.Print output.

You have confirmed what I thought, that what Microsoft has written on their
documentation page for CurrentDb is incorrect. dbsDbs1 and dbsDbs2 do not
refer to the same database. How can they be the same if they can have
different properties?

 

[Marshall Barton]

I did try the code change that you suggested, and it did not change the
outcome.
You get the exact same Debug.Print output.

You have confirmed what I thought, that what Microsoft has written on their
documentation page for CurrentDb is incorrect. dbsDbs1 and dbsDbs2 do not
refer to the same database. How can they be the same if they can have
different properties?

CurrentDb returns a REFRESHED COPY of the default database.
You can set the properties in one copy differently than in
another copy. The collections with the new properties need
to be refreshed before they can be seen anywhere else.

You are chasing a near irrelevant problem because using two
calls to CurrentDb as in you example is not a practical
solution to any programing issue I have ever run into.