Accessing SQL code in a query

  • Thread starter Thread starter EricY
  • Start date Start date
E

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.
 
EricY said:
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.
Click to expand...


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
 
hi Eric,
I need to be able to access the SQL code in 500 queries.
Click to expand...
Are you having a .mdb?

Dim qdf As DAO.QueryDef

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


mfG
--> stefan <--
 
EricY said:
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.
Click to expand...

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
 
Back
Top