[AccessD] List fields in a querydef

Bill Benson bensonforums at gmail.com
Sat Sep 11 09:21:57 CDT 2021


Not sure you need to open the querydef, can’t you get all those details
from the properties of the querydef?

I think you run into problems regarding what will be field names only with
crosstab deoendency queries whose columns aren’t known without actually
running predecessor queries.

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
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list