[AccessD] List fields in a querydef

[Gustav Brock]

Hi Arthur

For the fields, you can reduce it to:

Dim Field As DAO.Field
For Each Field In CurrentDb.QueryDefs("BooksandAuthors").OpenRecordset.Fields
    Debug.Print Field.Name
Next

For the table/query names, you will have to parse the SQL (look up FROM and JOIN):

    CurrentDb.QueryDefs("BooksandAuthors").SQL

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Arthur Fuller
Sendt: 8. september 2021 13:51
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] List fields in a querydef

Thanks a lot, Jack. I am slowly learning this stuff from your examples and some others I;ve found on the net. Much appreciated.

On Tue, Sep 7, 2021 at 1:22 PM jack drawbridge <jackandpat.d at gmail.com>
wrote:

> Arthur,
> Here's a sample based on one of my queries.
>
> Sub testqdfFields()
>     Dim dbs As DAO.Database
>     Dim qdf As DAO.QueryDef
>     Dim rst As DAO.Recordset
>     Dim fld As DAO.Field
>     Set dbs = CurrentDb
>     Set qdf = dbs.QueryDefs("BooksandAuthors")
>     Set rst = qdf.OpenRecordset(dbOpenDynaset)
>     Debug.Print qdf.SQL
>     Debug.Print rst.Fields.Count
>     For Each fld In rst.Fields
>         Debug.Print fld.name
>     Next fld
> End Sub
>
> Here is the result showing the qdf SQL
>
> SELECT BOOKAuthors.BookID, AUTHOR.authorId, BOOK.BookTitle, 
> BOOK.NumPages, AUTHOR.Firstname, AUTHOR.Lastname FROM BOOK INNER JOIN 
> (AUTHOR INNER JOIN BOOKAuthors ON AUTHOR.authorId =
> BOOKAuthors.AuthorId) ON BOOK.BookId = BOOKAuthors.BookID ORDER BY 
> BOOK.BookTitle, AUTHOR.Lastname;
>
>  6   <-----count of fields in qdf 
>  BookID  <---field names here and below authorId BookTitle NumPages Firstname Lastname
>
> Jack
>
> On Tue, Sep 7, 2021 at 12:40 PM Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > Given the name of a query, how can I list the fields  and their tables, in
> > the event that more than one table is involved?
> >
> > --
> > Arthur