"EricY" <(E-Mail Removed)> wrote in message
news:E178C2E0-F7DD-4A58-B998-(E-Mail Removed)...
> 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
--
Brendan Reynolds
|
Similar Threads
