I've written code in my Excel front-end to list all queries/views in program's backend db (MS Access .mdb file). I've done this by using the adSchemeTables, but I've found that the schema only includes select type queries. The other possible query types (Update, Delete, Append, etc) are *not* included in this scheme. Likewise, such queries missing from the adSchemeViews schema. Such queries also do *not* appear when I build a list using ADOX.Catalog.
I can only find the non-select queries listed in the hidden system table: MSysObjects. However, as covered in numerous posts, SQL and ADO cannot query (read-only) the table without first having assigned Admin read-only privileges to that hidden table. (Having numerous users manually change this setting in their copy of the db file is not ideal.)
MY QUESTIONS:
Other than the two schema I've tried above, is there another schema equally available that is more complete regarding these types of views/queries?
Alternatively, does anyone know another way to get a complete/comprehensive list of queries in an .mdb file using ADO/SQL?
I'm surprised no one else (as far as I can see) has posted anything about these queries not being included in the schema.
Maybe I'm doing something wrong?
This is a real head scratcher!
I can only find the non-select queries listed in the hidden system table: MSysObjects. However, as covered in numerous posts, SQL and ADO cannot query (read-only) the table without first having assigned Admin read-only privileges to that hidden table. (Having numerous users manually change this setting in their copy of the db file is not ideal.)
MY QUESTIONS:
Other than the two schema I've tried above, is there another schema equally available that is more complete regarding these types of views/queries?
Alternatively, does anyone know another way to get a complete/comprehensive list of queries in an .mdb file using ADO/SQL?
I'm surprised no one else (as far as I can see) has posted anything about these queries not being included in the schema.
Maybe I'm doing something wrong?
This is a real head scratcher!