We help IT Professionals succeed at work.

ADO QueryDefs SQL

colindow
colindow asked
on
Medium Priority
1,013 Views
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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
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
Next
Set cat = Nothing

Also, please maintain this open question:
Outlook Attachments Date: 12/10/2001 05:55AM PST
http://devx.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20244897

Thanks,
Anthony

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Anthony

Author

Commented:
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:
Provider=Microsoft.Jet.OLEDB.4.0.
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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:
Views:
Category Sales for 1995
Current Product List
Invoices
Order Details Extended
Order Subtotals
Product Sales for 1995
Products Above Average Price
Quarterly Orders
Sales by Category
Ten Most Expensive Products

Procedures:
Alphabetical List of Products
Catalog
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 ...)

Anthony
CERTIFIED EXPERT
Top Expert 2012
Commented:
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"
   .Open
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,
      Next
      Debug.Print
      .MoveNext
   Loop
End With
Set rs = Nothing
cn.Close
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,
Anthony

Author

Commented:
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.

Author

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