Solved

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

Posted on 2011-02-22
7
673 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Technology Partners: 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

Suggested Solutions

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 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