Retrieve Sql Statements From Ms-Access In Vs2008

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

2 Comments

  1. Re: Retrieve Sql Statements From Ms-Access In Vs2008 by UtfBSxhdHV

    UtfBSxhdHV 2009-10-22

    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.
    --
    Dave Hargis, Microsoft Access MVP


    "KevinInstructor" wrote:

    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

  2. Re: Retrieve Sql Statements From Ms-Access In Vs2008 by Stefan Hoffmann

    Stefan Hoffmann 2009-10-22

    hi Kevin,

    KevinInstructor wrote:
    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 <--