Solved

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

Posted on 2011-02-22
7
672 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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