[AccessD] List fields in a querydef

[jack drawbridge]

Arthur,
Here is another subroutine and test routine. It inputs the query name and
outputs the table names and field names.

' ----------------------------------------------------------------
' Procedure Name: reviewQueryDef
' Purpose: Another routine to identify tables and fields in querydef
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter qdf (QueryDef):The name of the querydef to be reviewed
' Author: Jack
' Date: 07-Sep-21
' ----------------------------------------------------------------
Sub reviewQueryDef(qdf As DAO.QueryDef)
    Dim str As Variant
    Dim FldCnt As Integer
    Dim i As Integer, sourceInfo As String
    If Len(qdf.SQL & "") > 0 Then
       FldCnt = qdf.Fields.Count
        Debug.Print qdf.SQL    'for testing/debugging
        str = Split(qdf.SQL, ",")
        For i = 0 To FldCnt - 1
            str(i) = Replace(str(i), "Select", "")
            If i = FldCnt - 1 Then
                sourceInfo = Mid(str(i), 1, InStr(str(i), "from") - 1)
            Else
                sourceInfo = str(i)
            End If
            Debug.Print sourceInfo
        Next i
    End If
End Sub
' ----------------------------------------------------------------

TEST ROUTINE:

' ----------------------------------------------------------------
' Procedure Name: testreview
' Purpose: Routine to TEST reviewQueryDef
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 07-Sep-21
' ----------------------------------------------------------------
Sub testreview()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("BooksandAuthors")
 Call reviewQueryDef(qdf)
End Sub
' ----------------------------------------------------------------

RESULT

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;

 BOOKAuthors.BookID
 AUTHOR.authorId
 BOOK.BookTitle
 BOOK.NumPages
 AUTHOR.Firstname
 AUTHOR.Lastname


<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Wed, Sep 8, 2021 at 7:51 AM Arthur Fuller <fuller.artful at gmail.com>
wrote:

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