Retrieve SQL statements from MS-Access in VS2008

[KevinInstructor]

One of the .NET MVP's suggested I post in this forum

I am looking for assistance in getting all pre-existing query names and SQL
text for all queries in an MS-Access database (such as North Wind).



I pieced together some code from bits and pieces off the Internet done in
VB6 below in VS2008/VB.NET which when I run it against NorthWind database not
all queries are returned and some report data sources (query statements) are
returned.

Any thoughts on a better avenue to obtain the query names and actual query
statements in VS2008?


Private dbItems As XDocument = <?xml version="1.0"
encoding="utf-8"?><Procedures/>
Private dbConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DataTest\Nwind_Converted.mdb;Persist Security Info=False"

....

Dim cn As New ADODB.Connection
Dim cat As ADOX.Catalog = New ADOX.Catalog

Try
cn.ConnectionString = dbConnection
cn.Open()

cat.ActiveConnection = cn

Dim Count As Integer = cat.Procedures.Count

For Item As Integer = 0 To Count - 1
Dim cmd As ADODB.Command = New ADODB.Command
cmd = DirectCast(cat.Procedures.Item(Item).Command,
ADODB.Command)
Dim QueryStatement As String = cmd.CommandText.TrimEnd

If Not String.IsNullOrEmpty(QueryStatement) Then
dbItems.<Procedures>(0).Add( _
<Item>
<Name><%= cat.Procedures.Item(Item).Name %></Name>
<Query><%= cmd.CommandText %></Query>
</Item>)
End If
Next

ListBox1.DisplayMember = "Name"
ListBox1.ValueMember = "Query"
ListBox1.DataSource = _
( _
From item In dbItems...<Item> _
Select _
Name = item.<Name>.Value, _
Query = item.<Query>.Value _
).ToList

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

 

[Klatuu]

Unless there is some real need to do it in code, the easy way is to use the
Database Documenter. You can selct just query objects and in Options, select
what information you want.

 

[Stefan Hoffmann]

hi Kevin,

Any thoughts on a better avenue to obtain the query names and actual query
statements in VS2008?

To retrieve all query names:

SELECT [Name]
FROM [MSysObjects]
WHERE [Type] = 5;

Enumerate cat.Procedures and cat.Views for this names.


mfG
--> stefan <--