adSchemaTables is missing some types of queries/views

PaulZak

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

Forum statistics

Threads
1,082,128
Messages
5,363,325
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top