We help IT Professionals succeed at work.

ADO QueryDefs SQL

colindow asked
Medium Priority
Last Modified: 2013-11-23
I would like to be able to read the SQL text associated with queries held in an access (97 or 2000) database.

I am using the ADOX.Catalog from the ADO ext 2.5 library.
Everything works fine until I try to access a querydef that is a MAKE TABLE query. At this point the process fails on trying to read the view. Has anyone got any suggestions as to how this can be done.

Unfortunately its only the MAKE TABLE querydefs I really need to get to!
Watch Question

Top Expert 2012

That is because ADOX does not consider it a View, but a Procedure.

Dim cat As ADOX.Catalog
Dim proc As ADOX.Procedure

Set cat = New ADOX.Catalog
cat.ActiveConnection = "your Jet connection goes here"
For Each proc In cat.Procedures
   Debug.Print proc.Name
Set cat = Nothing

Also, please maintain this open question:
Outlook Attachments Date: 12/10/2001 05:55AM PST



Thanks Anthony.
I tried this code for both views and procedures.
Its certainly true that none of the names of make table queries appear in the  collection for Views, but when I attempt any reference to Procedures the code fails immediately with a message:
Object or provider is not capable of performing requested operation (Even with a line like cat.procedures.count)

None of the make tables have paramaters so I tried adding some to see if this forced them to be recognised as procedures. I also tried setting the connection cursor location with both  useClient and useServer. This didn't seem to make any difference.

I am using an access 2000 database.
Top Expert 2012

That is odd because I tested it.  Let me look into it again.  In the meantime, can you let me know what version of Jet (3.51 or 4.0, I suspect the former) and also what version of ADO are you using?  This way I can attempt to duplicate the problem.



Hi I think I'm using
ADO 2.5 SP2
The access database is a 2000 mdb (I also tried a 97) but I don't actually have 2000 on this machine. I got the database from another machine.
The connection string uses:
To be honest I'm not entirely sure how to confirm the Jet version. I did read somewhere that 3.5 couldnt see action queries but assumed I was using the jet 4 engine to run with the above connect string.
Top Expert 2012

There is definitely something screwy going on.

I just checked it with the 3.51 and the 4.0 provider and in the case of the NorthWind database shipped with VB6 (Access 97), here are the identical results:
Category Sales for 1995
Current Product List
Order Details Extended
Order Subtotals
Product Sales for 1995
Products Above Average Price
Quarterly Orders
Sales by Category
Ten Most Expensive Products

Alphabetical List of Products
Customers and Suppliers by City
Employee Sales by Country
Invoices Filter
Orders Qry
Products by Category
Quarterly Orders by Product
Sales by Year
Sales Totals by Amount
Summary of Sales by Quarter
Summary of Sales by Year

Obviously many of those listed in the Procedures are not action queries. As an aside, it lists hidden queries as well.

I then checked it with the Northwind database shipped with Office 2000 and the results are pretty much the same.

What I tested yesterday was with a new database (Access 2000) and obviously Jet 4.0.  When I added a Select statement (except for Union queries, but including Crosstab queries) it showed up as a View, when I added an action query or a Select statement with a Union query then it showed up as a Procedure.

I realize all of this is totally useless to you, but sometimes the pain shared is less painful <g>

(To be continued ...)

Top Expert 2012
I just attempted using the OpenSchema method, and I got precisely the same results.  Here is my code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Set cn = New ADODB.Connection
With cn
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                        "C:\Program Files\Microsoft Office\Office\Samples\NorthWIND.MDB"
End With
Set rs = cn.OpenSchema(adSchemaProcedures)
With rs
   Do While Not .EOF
      For Each fld In rs.Fields
         Debug.Print fld.Name; "="; fld.Value,
End With
Set rs = Nothing
Set cn = Nothing

I suspect that both ADOX and the ADO's OpenSchema method are just retrieving the values from the MSysObjects table, which on its own does not appear to contain the type of query.

So I am at a loss to suggest alternatives.

[sound of Anthony throwing in towel]
Sorry I could not be more help,


Thanks Anthony
I'll get to work on a re-design.
I'll wait a few days then mark this as answered on the basis that sometimes its as useful to know the difficulties as it is the solution.


Not actually a solution as what I wanted to do doesnt seem to be achievable. Makes the info just as useful though.