using system tables in queries

[tina]

hi folks.

i'm using the system tables MSysObjects and MSysQueries to do the following:

1. use a SQL statement to select the object names of all Action queries,
open a DAO.Recordset, loop through the records, and append them into a table
called tblQueries.

2. use a SQL statement to select the object names, and Type, of all Action
queries, and the "new table name" in make-table queries, and update the
corresponding records in tblQueries.

3. use a SQL statement to identify query names in tblQueries that don't
match a query name in MSysObjects, and delete the unmatched records *FROM
tblQueries*.

4. use a SQL statement to identify report names in tblReports that don't
match a report name in MSysObjects, and delete the unmatched records *FROM
tblReports*.

at no time am i editing MSysObjects or MSysQueries in any way. and i'm
running all the SQL from VBA directly, not from permanent query objects in
the database window, so as not to expose the system tables directly to the
user. the View, HiddenObjects option remains at the default setting in the
database.

my question is: are there pitfalls or drawbacks to using the data in the
system tables, that i should watch out for? any inconsistency in Type,
Flags, or Attribute values that could skew my results, for instance?

i'd appreciate any advice, suggestions, or warnings - MVPs, especially,
please pull me back in line if i'm straying from the "true path". <g>

thx
tina

 

[Allen Browne]

Hi Tina

The approach you are using is undocumented, so I can't promise you that it
will always work in future versions. MS has the right to change the internal
structure. Having said that, there is no indication that it will change; in
fact, your approach would work in all 8 existing versions of Access, so it's
a low risk choice AFAICT.

As always, you would want to be sure that Name AutoCorrect was off.

There's always the chance of a problem with a corrupt database. I recently
saw a corruption where the name of a form as shown in the Database window
and in the AllForms collection did not match the name in MSsyObjects or the
name in the Documents collection (DAO). This was in Access 2003, and the
problem was triggered by copying and pasting the form in the database window
(to make a copy.) Naturally enough, if the database is partially corrupt,
all bets are off as to what happens.

You are probably aware that you need to avoid the temp objects, which
typically have a name starting with ~. Otherwise you should be fine.

If you are trying to identify the dependencies between queries and tables,
you may need to take into account that a query can reference a table in
another database directly, even if there is not an attached table, e.g.:
SELECT * FROM Employees IN "C:\northwind.mdb";

HTH

 

[tina]

comments inline.

Hi Tina

hi Allen. thanks for taking the time to respond.

The approach you are using is undocumented, so I can't promise you that it
will always work in future versions. MS has the right to change the internal
structure. Having said that, there is no indication that it will change; in
fact, your approach would work in all 8 existing versions of Access, so it's
a low risk choice AFAICT.

ah, good point re future versions. i'll keep it in mind. also helps to know
the values used in those tables have been stable in all versions so far -
gives me more confidence in using this approach.

As always, you would want to be sure that Name AutoCorrect was off.
absolutely!


There's always the chance of a problem with a corrupt database. I recently
saw a corruption where the name of a form as shown in the Database window
and in the AllForms collection did not match the name in MSsyObjects or the
name in the Documents collection (DAO). This was in Access 2003, and the
problem was triggered by copying and pasting the form in the database window
(to make a copy.) Naturally enough, if the database is partially corrupt,
all bets are off as to what happens.

if i understand correctly, you're talking about corruption caused by...the
various things that cause db corruption - but using the system tables as i
described does not CAUSE corruption by itself. as long as that's the case,
i'm not concerned. as you say, any corrupt db can't be expected to perform
as designed.

You are probably aware that you need to avoid the temp objects, which
typically have a name starting with ~. Otherwise you should be fine.

yes, i've weeded them out in the query criteria. i didn't realize that a SQL
statement in the RowSource of a form's combo box showed up as a temp object,
so i learned something new there.

If you are trying to identify the dependencies between queries and tables,
you may need to take into account that a query can reference a table in
another database directly, even if there is not an attached table, e.g.:
SELECT * FROM Employees IN "C:\northwind.mdb";

luckily i'm not attempting to do that in this db; just getting query (and
report) names, and identifying query types and make-table table names. it's

HTH

Allen, thanks so much for your feedback.
i was nervous about using system tables for the first time (though i knew
enough to *not* edit them in any way), and not sure if my approach was
advisable. i feel much more confident now about my design. i went ahead and
finished it and i'm very pleased with the results - especially now that i

 

[Allen Browne]

Great. You have it up and working.

Correct: reading the system tables does not cause any kind of corruption.

 

[tina]

Correct: reading the system tables does not cause any kind of corruption.

okay, i'm good to go, then. thanks again!

 

[Lynn Trapp]

okay, i'm good to go, then. thanks again!

Just remember to breath deeply... ;-)