Accessing SQL code in a query

[EricY]

Hello,

I need to be able to access the SQL code in 500 queries.

Using the code below I am able to access each query object, but I have been
unable to find the property that will enable me to extract the SQL. It's just
a test program that loads a listbox with the info:

Dim accObject As Access.AccessObject

For Each accObject In CodeData.AllQueries
Me.listObjects.AddItem "Query:;" & accObject.Name
Next

Any help greatly appreciated!

Eric.

 

[Brendan Reynolds]

Hello,

I need to be able to access the SQL code in 500 queries.

Using the code below I am able to access each query object, but I have
been
unable to find the property that will enable me to extract the SQL. It's
just
a test program that loads a listbox with the info:

Dim accObject As Access.AccessObject

For Each accObject In CodeData.AllQueries
Me.listObjects.AddItem "Query:;" & accObject.Name
Next

Any help greatly appreciated!

Eric.

The AccessObject object doesn't have the property you need, you need either
a DAO.QueryDef object or an ADODB.Command object.

Heres a DAO example ...

Public Sub ListSqlDao()

Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdfs = db.QueryDefs
For Each qdf In qdfs
Debug.Print qdf.SQL
Next qdf

End Sub

I'm not very familiar with ADO, so the following example may not be the best
way to do it in ADO, but it seems to work. Requires references to the ADO
and ADOX object libraries ...

Public Sub ListSqlAdo()

Dim cat As ADOX.Catalog
Dim vws As ADOX.Views
Dim vw As ADOX.View
Dim cmd As ADODB.Command

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set vws = cat.Views
Debug.Print cat.Views.Count
For Each vw In vws
Set cmd = vw.Command
Debug.Print cmd.CommandText
Next vw

End Sub

 

[Stefan Hoffmann]

hi Eric,

I need to be able to access the SQL code in 500 queries.

Are you having a .mdb?

Dim qdf As DAO.QueryDef

For Each qdf In CurrentDb.QueryDefs
Debug.Print qdf.SQL
Next qdf


mfG
--> stefan <--

 

[Stuart McCall]

Hello,

I need to be able to access the SQL code in 500 queries.

Using the code below I am able to access each query object, but I have
been
unable to find the property that will enable me to extract the SQL. It's
just
a test program that loads a listbox with the info:

Dim accObject As Access.AccessObject

For Each accObject In CodeData.AllQueries
Me.listObjects.AddItem "Query:;" & accObject.Name
Next

Any help greatly appreciated!

Eric.

Dim s As String

For Each accObject In CodeData.AllQueries
s = accObject.SQL
'Do whatever you want to the string here.
accObject.SQL = s
Next

 

[EricY]

Thanks Brendan. I used the DAO method and it worked a treat.

Cheers!

Eric