Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

List Access Querie properties (sql syntax) using Excel ADODB connetion

Posted on 2011-02-22
7
Medium Priority
?
678 Views
Last Modified: 2012-05-11
I have an ADODB connection to an Access database, I want to list all the queries in the database (including select and action quries) and then list there SQL syntax in the next coloumn I want to do this using VBA and Excel. I am getting stuck on using the open schema with the recordset I am using adSchemaTables becuase I cant for some reason use adSchemaViews, i also dont have the syntax for finding the SQL of that query. Can this be done?

Code I have for returning the query is
 Set rs = cn.OpenSchema(adSchemaTables)
For T = 1 To rs.RecordCount
    Range("A" & T) = rs!Table_Name
    rs.MoveNext
Next T
 rs.Close
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Author Comment

by:MrDavidThorn
ID: 34950594
I now have to code  - Is there away to find the SQL of the view?

Sub ListSQLQuries()
Dim iRowCount As Integer
iRowCount = 2
 Set rs = cn.OpenSchema(adSchemaTables)
For t = 1 To rs.RecordCount
    If rs!Table_Type = "VIEW" Then
    Range("A" & iRowCount) = rs!Table_Name
    Range("B" & iRowCount) = rs!Table_Type
    iRowCount = iRowCount + 1
    End If
    rs.MoveNext
Next t
 rs.Close
End Sub
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34950742
I can't do it using the route you have taken.
This is how I would get the query sql  in ADO.  This example runs within the Access database s it uses the currentconnection property but you can comment that line out and use the one above it to connect to another database.

Sub getQuerysql()
   Dim cat As ADOX.Catalog
   Dim cmd   As ADODB.Command
   Dim proc As ADOX.Procedure
   Set cat = New ADOX.Catalog
   ' Open the Catalog object.
  ' cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strdatabasepath
   
   cat.ActiveConnection = CurrentProject.Connection
   
   Set cmd = New ADODB.Command
   ' Get the query from the Procedures collection.
   
   For Each proc In catDB.Procedures
   
    Set cmd = proc.Command
    Debug.Print cmd.CommandText
   Next proc
   
   Set catDB = Nothing
End Sub
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 34951524
You can also use the QueryDef object of a DAO.Database object:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = OpenDatabase("Path to the Access db")

For each qdf in dbs.QueryDefs
   Range("A" & iRowCount) = qdf.Name
   Range("B" & iRowCount) = qdf.SQL
Next qdf

dbs.Close
Set dbs = Nothing
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:MrDavidThorn
ID: 34951712
do you know if I could use the DAO with a DSN connection string?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34951753
You don't need a dsn - Excel can connect to Access without it just using the code Scott posted.
0
 

Author Comment

by:MrDavidThorn
ID: 34951849
Im connecting to an access 2000 db when I use the ADO method (which is prefered) Im getting the error message number 3251 -'Object or provider is not capable of performing requested operation.'  -The DAO method works fine, but  I do need to use a DSN string as Im connecting to more that one database to compare SQL syntax and want the user to change the db path without editing the excel file.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34951977
"...want the user to change the db path without editing the excel file."

So what are you saying - that the user would edit the DSN?
That hardly seems more reliable or easier.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question