Re: Querydef

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: Querydef
    ... Is there a way to list the tables/queries that comprise a single query? ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)
  • Re: Define FROM target in Append Query
    ... Assuming you have code in the form frmProcessEditor perhaps you place code ... Dim qdf as DAO.QueryDef ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.queries)