Re: Querydef

...not sure anyone is still following this thread. I tried SourceTable.
Unfortunately the table(s) I am looking for are used to filter records and
are not included in the query grid.


"JimP" <jpockmire@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8-udnVxZlK8re9LVnZ2dnUVZ_o7inZ2d@xxxxxxxxxxxxxx
Thanks - will try it.

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uZ%231sCkyIHA.548@xxxxxxxxxxxxxxxxxxxxxxx
Did you try the suggestion of using the SourceTable?

In John's code:
Debug.Print fld.Name & " = " & fld.SourceTable & "." & fld.SourceField

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JimP" <jpockmire@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:O_adnWEBooUQuNDVnZ2dnUVZ_oninZ2d@xxxxxxxxxxxxxx
Thank you - that worked.

However, I was hoping it would print the source table/query name in
front of the field name. So I'm back where I started.

Is there a way to list the tables/queries that comprise a single query?


"John Spencer" <spencer@xxxxxxxxxx> wrote in message
news:%23Uh9ZsXyIHA.4912@xxxxxxxxxxxxxxxxxxxxxxx
Try the following

Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")

For Each fld In qdf.Fields '<<<<<<<<<<<<<<<<
Debug.Print fld.NAME
Next fld

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


JimP wrote:
I like your last suggestion. I tried the attached code and received
the error "Operation not supported for this type of object". Ideas?

Sub ListQueryObjects()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Set db = CurrentDb
Set qdf = db.QueryDefs("CampaignRptSelNews")
For Each fld In qdf
Debug.Print fld.NAME
Next fld
End Sub

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eSUWz5JyIHA.4864@xxxxxxxxxxxxxxxxxxxxxxx
Hmm. You want to list the source "tables" that feed a query?

If you are using Access 2003 or 2007, you may be able to trace the
DependencyInfo object back to get the source tables. IME this doens't
work very well: it requires Name AutoCorrect to be turned on (heaps
of issues), and can trying to walk the DependencyInfor can actually
crash Access (typically where some tables/queries refer to objects
that no longer exist in the database), so I can't recommend that
approach.

There are some commecial utilities that trace dependencies like that.
Examples:
http://www.speedferret.com/
http://www.rickworld.com/products.html
http://www.fmsinc.com/Products/

If you prefer to trace it yourself, each field in a QueryDef has a
SourceTable, so you could loop the Fields of the QueryDef and examine
the SourceTable of each. Provided the query outputs the field (it may
not, e.g. if it's just used as an INNER JOIN), that should list it,
and you can then discover whether it is a table or query and handle
that recursively.
"JimP" <jpockmire@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:NNydnXWv1qoV89fVnZ2dnUVZ_gednZ2d@xxxxxxxxxxxxxx
Thanks, but I think this will give me a listing of the queries in an
mdb. What I want to do is list the tables and/or queries that
comprise a single query.





.

Relevant Pages

  • Re: error loading DLL
    ... > ' Contains a bitmask that specifies the valid bits in dwFileFlags. ... >> Dim refCurr As Reference ... >>> Dim qdf As DAO.querydef ... >>> ' If query exists, ...
    (microsoft.public.access.formscoding)
  • Re: error loading DLL
    ... ' Contains a bitmask that specifies the valid bits in dwFileFlags. ... > Dim refCurr As Reference ... >> Dim qdf As DAO.querydef ... >> ' If query exists, ...
    (microsoft.public.access.formscoding)
  • Re: reading from txt file into table
    ... .Fields.Append fld ... Dim tdf As DAO.TableDef ... Dim lngInputFile As Long ... Then you can create a query into the text file, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Find text in SQL
    ... database as a query named something along those lines. ... >> Dim dbs As DAO.Database ... >> Dim qdf As QueryDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: error loading DLL
    ... Dim refCurr As Reference ... > Dim qdf As DAO.querydef ... > ' create the Query for the report ... > ' If query exists, ...
    (microsoft.public.access.formscoding)
Loading