For Experts: Exploring the DAO object model

The description of the "related solution" was meant to be "previous thread in the experts series"; it didn't "take".

This is also a follow-up to:
Error 3420: Object invalid or no longer set. -- http:/Q_23355139.html

In that thread, Containers were compared to TableDef objects in that both need a "live" parent Database object to function. I was going to argue that Containers cannot be passed directly to Jet, because there is more to it then simple lookups into the system tables. I came upon the bizarre and totally virtual "database object" called "User Defined": it doesn't contain anything, does it?

(°v°)

Wow.

You are right: you can disconnect the Containers collection from the Database object. This is very interesting! I immediately tried with an individual Container, the Documents collection and an individual Document:

set x = currentdb.containers: ? x.count   ' -- only this works!
set x = currentdb.containers!databases: ? x.name
set x = currentdb.containers!databases.documents: ? x.count
set x = currentdb.containers!databases.documents!MSysDb: ? x.name

Still, the entire tree is available if you persist either the Database object (obviously) or only the Containers collection (your ctrs variable). So Leigh was right in guessing that Container objects and Document objects do not use any Database methods.

Thanks for finding that!

(°v°)

This has been turning in the back of my head until I thought of:

set tdfs = currentdb.TableDefs
set tdf = tbls!Table1
? tdf.Fields.Count

Following my previous logic, I now have to assume that top level collections of the Database object persist their parent. This is easily tested (see below), so our previous conclusions hold.

(°v°)

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Sub TestHyp()
    Dim tdf, tdfs
    With CurrentDb
        Set tdf = .TableDefs!Table1
        ' Set tdfs = .TableDefs   ' uncomment this
    End With
    Debug.Print tdf.Fields.Count  ' and this works
    Set tdfs = Nothing
    Debug.Print tdf.Fields.Count  ' but not anymore
End Sub

Miriam,

You can add properties to all DAO objects, including the root database object. However, the "Database" document "UserDefined" in the Access implementation of the document properties all Office documents now possess: (File | Properties), [Custom] tab.

ACC: Using DAO to Set and Retrieve Custom Database Properties
http://support.microsoft.com/kb/178745

Also, if you use DBEngine(0)(0) [...] Properties, you need to refresh the collection. This is the main reason CurrentDb is recommended instead. It makes no difference for Containers (it's a fixed list), but it's essential for all other collections within the database object tree. That was the topic of the thread that prompted this one (see the first comment)...

[More later, got to run]

(°v°)

@JDettman

Yes, you are quite right: almost all containers and all user documents are exposed in the (Tools | Security | User and Group Permissions...). The containers are named <new something>, and serve mainly as template for the permissions of new objects. (I also tend to believe that the Database Window uses the DAO Documents directly to present the lists of objects, and not, say, the Project's AllXXX collections.)

However, when selecting "Database", the container is presented as <Current Database>, and the four items under scrutiny are not revealed. A quick check of the Permissions defined for them reveals nothing terribly exciting (see below a list of the -- 20 bit -- security constants and the AllPermissions property of the Database Documents).

Also, removing security flags does not affect the properties of the objects: you can still manipulate them freely. This is not a way to "lock" the custom properties, for example.

I'm guessing that your are correct: the Containers and Documents is meant primarily to allow the manipulation of Access objects, including security and other "external" properties like the description.

    Side note:
    It used to manage creation and modification dates, but the
    introduction of ADO has broken that:
    ACC2000: DAO LastUpdated Property Returns Incorrect Date/Time
    http://support.microsoft.com/kb/299554

In conclusion, if the objects AccessLayout, MSysDb, SummaryInfo, and UserDefined are not there for security, this is probably the wrong path. What are they used for?

(°v°)

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

' dbSecDBCreate      0 ---- ---- ---- ---- ---1 : &H1
' dbSecDBOpen        0 ---- ---- ---- ---- --1- : &H2
' dbSecDBExclusive   0 ---- ---- ---- ---- -1-- : &H4
' dbSecDBAdmin       0 ---- ---- ---- ---- 1--- : &H8
' dbSecNoAccess      0 ---- ---- ---- ---- ---- : &H0
' dbSecCreate        0 ---- ---- ---- ---- ---1 : &H1
' dbSecReadDef       0 ---- ---- ---- ---- -1-- : &H4
' dbSecRetrieveData  0 ---- ---- ---- ---1 -1-- : &H20
' dbSecInsertData    0 ---- ---- ---- --1- ---- : &H32
' dbSecReplaceData   0 ---- ---- ---- -1-- ---- : &H64
' dbSecDeleteData    0 ---- ---- ---- 1--- ---- : &H128
' dbSecDelete        0 ---1 ---- ---- ---- ---- : &H65536
' dbSecWriteDef      0 ---1 ---- ---- ---- 11-- : &H65548
' dbSecReadSec       0 --1- ---- ---- ---- ---- : &H131072
' dbSecWriteSec      0 -1-- ---- ---- ---- ---- : &H262144
' dbSecWriteOwner    0 1--- ---- ---- ---- ---- : &H524288
' dbSecFullAccess    0 1111 1111 1111 1111 1111 : &H1048575
 
' AccessLayout       0 ---- ---- ---- ---- ---- : &H0
' MSysDb             0 -11- ---- ---- ---- 111- : &H393230
' SummaryInfo        0 ---1 ---- ---- ---- ---- : &H65536
' UserDefined        0 ---1 ---- ---- ---- ---- : &H65536

The second avenue is Properties.

@Leigh: You seem to think that UserDefined is a pure properties "holder" (I was going to write: properties container, but in this context it's not too sharp). I think so too, and since we don't know of any other practical use of the object, it's a sound empirical conclusion.

@Jim: In your comment http:#a21453982, you try to make a distinction between the "document object" and the "actual object it represents". I prefer to distinguish "external" and "internal" properties.

For example:

    ? CurrentDb.Containers!Forms!Form1.Properties!Description
    My very first form!
    ? Forms!Form1.Properties!Description ' error!
    ? Forms!Form1.Properties!RecordSource
    Table1

So "Description" is an "external" property, or of the Document object, while RecordSource is "internal" and is available only from an instanciated object.

Then of course, there are Custom and Built-in properties. (The second line raises "Cannot delete a built-in property".)

    CurrentDb.Containers!Forms!Form1.Properties.Delete "Description"
    CurrentDb.Containers!Forms!Form1.Properties.Delete "LastUpdated"


Anyway. Are all the four objects used only as property "holders"?

(°v°)

Well, I guess that my last position holds. This thread has stalled, so I went a bit further and did some software archaeology (it's a new discipline) to gain some insights into the ancient history of Access development.

The snippet below lists all database properties: CurrentDb, Databases Container, and all Documents therein. For Documents, the first eight are skipped; they are totally (well, mostly) uninteresting.

We already know that the Databases *Container* holds information about security, and nothing more.

Trivia: Some settings are only meaningful for Workgroup Information Files (mdw).

    PermissionEnum Enumeration
    http://msdn.microsoft.com/en-us/library/bb242668.aspx

    ACC2000: How to Prevent Users from Creating New Databases
    http://support.microsoft.com/kb/210329

The security settings of the Documents, however, are random, and have no effect {http:#a21459327}. They provide some archaeological insight, however, in that they indicate three different groups, different times, probably different programmers.

MSysDb
¯¯¯¯¯¯¯
The properties of MSysDb are clearly a subset of CurrentDb's. In fact, a little experimenting shows that they are not copies: a property added to MSysDb becomes part of CurrentDb's. There are about a dozen properties belonging only to CurrentDb, but essentially MSysDb *is* the holder of the database's DAO properties. Adding a property to MSysDb adds it also to CurrentDb. Note that some properties are managed in (Tools | Options), but not all.

[This answers more precisely Miriam's question http:#a21454945]

Globally, some properties are relatively recent, e.g. "ANSI Query Mode" or "Themed Form Controls". It would be interesting to examine an Access 97 database. There is very little reason to mess with those, except to obfuscate code.

    ? Application.GetOption("Auto Compact")
    ? CurrentDb.Properties![Auto Compact]
    ? CurrentDb.Containers!Databases.Documents!MSysDb.Properties![Auto Compact]

Trivia:
* Deleting the property "AccessVersion" prevents the database from opening and displays a scary error message ("You misbehaved badly. Loose all hope of retrieving your database objects." -- from memory). Setting the property back fixes it.
* Setting "HasCustomGroups" to False effectively deletes them...

AccessLayout
¯¯¯¯¯¯¯¯¯¯¯¯
A single property in my case: KeepLocal, with value T or V. It's related to replication, so it could be older than MSysDb. Given the name, it's unlikely that more properties are added in a master or replica.

    Changes made to your database when you use replication (MDB)
    http://office.microsoft.com/en-us/access/HP052167961033.aspx

SummaryInfo
¯¯¯¯¯¯¯¯¯¯¯
This one is easy: it's the fields in (File | Database Properties...), [Summary Info]. No more, no less. New properties are of course hidden from the interface.

UserDefined
¯¯¯¯¯¯¯¯¯¯
Similarly, this is (File | Database Properties...), [Custom Properties]. This Document and Summary Info were clearly added to conform to the new Office standard, as Leigh pointed out.

Problem: Properties can have all the basic DAO data types (yes, even dbMemo with 60'000 characters), but Custom Properties are more restrictive: Text, Date, Number and Boolean.
Solution: (up to) Access 2002, the properties were cast back to String. In Access 2003, they are no longer displayed. However, they can be silently overwritten from the interface.

Goofs: Replicate Project was probably not meant to be in that collection, but rather in the Options, or in a Replica options dialog. This is the only User Defined Property used by Access.

    Troubleshoot replication (MDB)
    I don't want to replicate my forms, reports, macros, or modules ...
    http://office.microsoft.com/en-us/access/HP052167711033.aspx

Trivia: You can create a custom property having the same name as a built-in property, e.g. 'Permissions' or 'LastUpdated'. Tricky.


Conclusion
¯¯¯¯¯¯¯¯¯
The only apparent use of the Databases Container Documents is to hold properties. To us programmers, User Defined is of course the most useful. However, properties can also be "hidden" in any other Documents.

I have often created custom properties for CurrentDb; I will refrain from that in the future. Overwriting "Access Version" or any other system property is too risky, as unlikely as it seems; and new system properties can be added with each new version.


Cheers all, thanks for reading!

Markus ~ (°v°)

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

Function PrpValue(pprp As DAO.Property) As String
On Error Resume Next
    PrpValue = Nz(pprp.Value, "(null)")
    If Err Then
        PrpValue = "#Error " & Err.Number
        Err.Clear
    End If
End Function
 
Sub DatabaseProperties()
    Dim doc As DAO.Document
    Dim prp As DAO.Property
    Dim i As Integer
    With CurrentDb
        Debug.Print .Name
        For Each prp In .Properties
            Debug.Print "  "; prp.Name; ":"; Tab(40); PrpValue(prp)
        Next prp
        With .Containers("Databases")
            Debug.Print .Name
            For Each prp In .Properties
                Debug.Print "  "; prp.Name; ":"; Tab(40); PrpValue(prp)
            Next prp
        End With
        For Each doc In .Containers("Databases").Documents
            Debug.Print doc.Name
            i = 0
            For Each prp In doc.Properties
                i = i + 1
                If i > 8 Then
                    Debug.Print "  "; prp.Name; ":";
                    Debug.Print Tab(40); PrpValue(prp)
                End If
            Next prp
        Next doc
    End With
End Sub