Solved

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

Posted on 2011-02-22
7
668 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Read about achieving the basic levels of HRIS security in the workplace.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now