getting the sql statement from a Access stored Query

  • Thread starter Thread starter Patrick F
  • Start date Start date
P

Patrick F

I've searched for a few hours for the answer to this and I give up. Can
anyone help?
I came up with massive information about lots of stuff, but nothing on
retrieving the SQL string from a MS Access stored Query.
I could get the list of queries and all the properties as follows:

Sub showDBInfo()
Dim cn As New OleDbConnection(ConnString)
cn.Open()
Dim dt As DataTable = cn.GetOleDbSchemaTable _
(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "VIEW"})
cn.Close()
End Sub

also:
Sub fillGridFromAccessQuery(ByVal queryName As String)
Dim conn As New OleDbConnection(ConnString)
Dim cmd As New OleDbCommand(queryName, conn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As New OleDbDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(DT1)
End Sub

which executes a stored query.
but I want to return the stored SQL string from the named query.

in VB6 it was so easy: dbs.QueryDefs(queryName).SQL

Can I do this in VB9 without using the QueryDef object? I would think that
the OleDbCommand.CommandText Property would do the trick but apparently not.
 
Patrick F said:
I've searched for a few hours for the answer to this and I give up.
Can anyone help?
I came up with massive information about lots of stuff, but nothing
on retrieving the SQL string from a MS Access stored Query.
I could get the list of queries and all the properties as follows:

Sub showDBInfo()
Dim cn As New OleDbConnection(ConnString)
cn.Open()
Dim dt As DataTable = cn.GetOleDbSchemaTable _
(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing,
"VIEW"})
Click to expand...

Views:

Dim dt As DataTable = cn.GetOleDbSchemaTable _
(OleDbSchemaGuid.Views, New Object() {})

-> dt.rows(x)("VIEW_DEFINITION")



Procedures:

Use OleDbSchemaGuid.Procedures

-> dt.rows(x)("PROCEDURE_DEFINITION")



Armin
 
thank-you very much! I had this posted in the MSDN VB forums for a 2 weeks,
with hundreds of views, and the few answers I did recieve had no idea what I
was referring to :/

again, thanks a million
 
Patrick F said:
thank-you very much! I had this posted in the MSDN VB forums for a 2
weeks, with hundreds of views, and the few answers I did recieve had
no idea what I was referring to :/

again, thanks a million
Click to expand...

Was a pleasure! :-)


Armin
 
Back
Top