Solved

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

Posted on 2011-02-22
7
670 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
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…

776 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