Read MSysACEs

[Phil Stanton]

There are various hidden tables in Acees 2000 including MSysACEs

The owner in Engine (I presume the Microsoft Jet Engine); I can't read the
data or change the permissions. Any ideas

Thanks

Phil

[David W. Fenton]

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in
news:45******** **************@ ptn-nntp-reader02.plus.n et:

There are various hidden tables in Acees 2000 including MSysACEs

The owner in Engine (I presume the Microsoft Jet Engine); I can't
read the data or change the permissions. Any ideas

Um, why do you care what's in it?

The fact that you're prohibited from seeing the data is probably a
good indication that you have no business seeing it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Phil Stanton]

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

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in
news:45******** **************@ ptn-nntp-reader02.plus.n et:

>There are various hidden tables in Acees 2000 including MSysACEs

The owner in Engine (I presume the Microsoft Jet Engine); I can't
read the data or change the permissions. Any ideas

Um, why do you care what's in it?

The fact that you're prohibited from seeing the data is probably a
good indication that you have no business seeing it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"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

[Phil Stanton]

Thanks, Larry.

Looks as if I am trying to re-invent the wheel.

Code works a treat though

Phil

Will have a good loo
"Larry Linson" <bo*****@localh ost.notwrote in message
news:8wqdh.2147 $5D1.497@trnddc 06...

"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

[Larry Linson]

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

Thanks, Larry.

Looks as if I am trying to re-invent the wheel.

Code works a treat though

Phil

You're welcome.

Sometimes it's fun (at least, interesting) to get down to the "brass tacks."

The first "running of the collections and containers" that I did was back in
early Access days before any of these tools were available or, at least,
before I was aware of any of them. If nothing else, it's interesting to do
enough to understand "How did they do that?"

Larry Linson
Microsoft Access MVP

[Phil Stanton]

Have had a quick look at FMS Demo OK, but the thing I have written shows the
relationship between objects, eg where subforms are used.

For example FMS say on their Northwind Db that the Customer Order form
contains Customer Orders Subform1 and Customer Orders Subform2. My effort
shows that Customer Orders Subform1 is used in Customer Order form.
Particularly useful in detecting queries that are not used.

Phil
"Larry Linson" <bo*****@localh ost.notwrote in message
news:fg2eh.3518 $hh.2392@trnddc 01...

>
"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

>Thanks, Larry.

Looks as if I am trying to re-invent the wheel.

Code works a treat though

Phil

You're welcome.

Sometimes it's fun (at least, interesting) to get down to the "brass
tacks."

The first "running of the collections and containers" that I did was back
in early Access days before any of these tools were available or, at
least, before I was aware of any of them. If nothing else, it's
interesting to do enough to understand "How did they do that?"

Larry Linson
Microsoft Access MVP

[Larry Linson]

My recollection, from the last time I looked at TAA, was that there are more
options than I cared to try to remember. Thus, there _may_ be a report, or
option, to do what you want, even if it is not the "default."

But, they may not have a report, or option, to show all objects in the way
that you want to see them -- did you look at the code at Jeff Conrad's site?

Larry Linson
Microsoft Access MVP

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

Have had a quick look at FMS Demo OK, but the thing I have written shows
the relationship between objects, eg where subforms are used.

For example FMS say on their Northwind Db that the Customer Order form
contains Customer Orders Subform1 and Customer Orders Subform2. My effort
shows that Customer Orders Subform1 is used in Customer Order form.
Particularly useful in detecting queries that are not used.

Phil
"Larry Linson" <bo*****@localh ost.notwrote in message
news:fg2eh.3518 $hh.2392@trnddc 01...

>>
"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******* *************** @ptn-nntp-reader02.plus.n et...

>>Thanks, Larry.

Looks as if I am trying to re-invent the wheel.

Code works a treat though

Phil

You're welcome.

Sometimes it's fun (at least, interesting) to get down to the "brass
tacks."

The first "running of the collections and containers" that I did was back
in early Access days before any of these tools were available or, at
least, before I was aware of any of them. If nothing else, it's
interesting to do enough to understand "How did they do that?"

Larry Linson
Microsoft Access MVP

[Phil Stanton]

The information on Jeff Conrad's site indicates that as with FMS the
principal is "here is an object and this is what is in it" as opposed to
"here is an object and this is where it's used"

Can't be sure of my facts as the demo program has a number of restrictions
and I cant afford £150 ( even with the good $ to £ rate)

Phil

"Larry Linson" <bo*****@localh ost.notwrote in message
news:%P4eh.242$ 7h.117@trnddc02 ...

My recollection, from the last time I looked at TAA, was that there are
more options than I cared to try to remember. Thus, there _may_ be a
report, or option, to do what you want, even if it is not the "default."

But, they may not have a report, or option, to show all objects in the way
that you want to see them -- did you look at the code at Jeff Conrad's
site?

Larry Linson
Microsoft Access MVP

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

>Have had a quick look at FMS Demo OK, but the thing I have written shows
the relationship between objects, eg where subforms are used.

For example FMS say on their Northwind Db that the Customer Order form
contains Customer Orders Subform1 and Customer Orders Subform2. My effort
shows that Customer Orders Subform1 is used in Customer Order form.
Particularly useful in detecting queries that are not used.

Phil
"Larry Linson" <bo*****@localh ost.notwrote in message
news:fg2eh.351 8$hh.2392@trndd c01...

>>>
"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45****** *************** *@ptn-nntp-reader02.plus.n et...
Thanks, Larry.

Looks as if I am trying to re-invent the wheel.

Code works a treat though

Phil

You're welcome.

Sometimes it's fun (at least, interesting) to get down to the "brass
tacks."

The first "running of the collections and containers" that I did was
back in early Access days before any of these tools were available or,
at least, before I was aware of any of them. If nothing else, it's
interesting to do enough to understand "How did they do that?"

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in
news:45******** **************@ ptn-nntp-reader02.plus.n et:

The information on Jeff Conrad's site indicates that as with FMS
the principal is "here is an object and this is what is in it" as
opposed to "here is an object and this is where it's used"

Have you considered that for about $100 you could get SpeedFerret to
do all of this for you?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/