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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,214,606
Messages
6,120,492
Members
448,967
Latest member
visheshkotha

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