"Phil Stanton" wrote
>I have created a database that analyses other databases - for example it
will tell you where all the queries are used (eg forms, reports, and as
subqueries, combo boxes etc)
It will tell you where all the forms are used (possibly as subforms) and
what queries or SQLs are used in them (eg as a source of a combo box).
It will tell you all the source of all the components of say a form
(queries, tables, subforms etc)
Most of the information is extracted from the hidden tables, but I am
trying to find out where the information on form, query report and table
properties are hidden
Phil
Most, probably all, of the information you need to accomplish your purpose
is maintained in the database in a form so that you can get to it through
the object model, which is documented. The system tables are undocumented,
and they are undocumented for a reason -- they may change. It is possible
that there was no change to system tables between Access 2002 and 2003,
because there were very few Access-specific changes. But there has been some
change to System Tables between every other pair of Access releases. Thus,
I'd suggest you study the Object Model, Collections, Containers, and
Properties.
Given the many and dramatic changes between Access 2003 and 2007, I would
expect equally many and dramatic changes in the System Tables.
But, if you want to know what is in MSysACES, here's some code that's a
start. Note that I accessed the TableDef, its Fields collection, and the
Properties of the Fields using Data Access Objects code, using the Object
Model, NOT by reading the MSysACES table directly. (No, I can't view
MSysACES in the UI, either... I get the same message box about permissions
that you do.) For what you want to do, it's not important that you be able
to view that table in the user interface, only that you be able to obtain
the data from it. You can, instead of using Debug.Print to print to the
Immediate Window, define tables and save the data in tables that will be
simpler to use for reporting, etc. You are on your own to determine what
the Fields are used for, and what those Properties mean. I haven't included
the step where I determined what the Properties of the Properties of a Field
were, and you can copy/modify some of the code to get the Properties of the
Properties of the Properties (I note there are 4, in all cases here).
Sub ShowTable()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim prop As DAO.Property
Set db = CurrentDb
For Each td In db.TableDefs
If td.Name = "MSysACES" Then
Debug.Print "Table " & td.Name
For Each fld In td.Fields
Debug.Print " Field " & fld.Name, "Props " &
fld.Properties.Count
For Each prop In fld.Properties
Debug.Print " " & prop.Name; ": type " &
prop.Type; "; inherited " & prop.Inherited; "; with ";
prop.Properties.Count; " properties "
Next
Next
End If
Next
Set db = Nothing
End Sub
Do you intend to go into competition with FMS? What you are trying to
accomplish sounds much like some of their Access tools, or some from
mztools, or some freeware that MVP Jeff Conrad has available on his website
at
http://home.bendbroadband.com/conrad...essjunkie.html. If you
aren't planning on selling this application, perhaps you can save yourself a
lot of trouble by looking into what's already available.
Larry Linson
Microsoft Access MVP
