Re: Querydef

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.

--
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: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
    ... If you are using Access 2003 or 2007, you may be able to trace the DependencyInfo object back to get the source tables. ... 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, that should list it, and you can then discover whether it is a table or query and handle that recursively. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Breaking down imported information
    ... Single-record append query: ... ' Check if at EOF of Recordset (rsDataViaCode) ... ' if at EOF Exit Do. ... Dim rsDataViaCode As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: LDAP query information
    ... Copyright 1985-2001 Microsoft Corp. ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • Re: LDAP query information
    ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... Set adoConnection = CreateObject ... ' Construct LDAP syntax query. ... Yes, the script uses ADO to query AD directly, which is very efficient - no ...
    (microsoft.public.windows.server.scripting)
  • Re: Need WMI script
    ... I also assume that strGroupName is ... If instead strDomainName is the NetBIOS name of a domain, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes, xmlDoc, ... ' Construct the LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
Loading