adSchemaTables is missing some types of queries/views

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
105
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top