ms-accessvbatablenamefield-names

Get Table and Field names used to create a query in Access VBA


I am trying to get a list of the tables and fields that are used to produce queries in an access database. I am able to identify the fields found in a query output but not the fields used to design the query.

In other words, is it possible to obtain the table and field names specified in the query builder section of the design view of a query in Access?

I have found and developed upon some VBA code that gets all of the fields that are in the output of a query (below) but this does not get the info I need. Is it possible to achieve what I am looking for?

Function listQueryFields() As String

    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Dim fld As Field
    Dim rs As Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbl_Query_Field_Names")

    For Each qry In db.QueryDefs
        If InStr(1, qry.Name, "_qry_", vbTextCompare) > 0 Then
            Debug.Print qry.Name
                For Each fld In qry.Fields
                   Debug.Print fld.Name
                   rs.AddNew
                        rs(0) = qry.Name
                        rs(1) = fld.Name
                    rs.Update
                 Next

        End If
    Next

    Set db = Nothing
    Set rs = Nothing
    Exit Function

End Function

Solution

  • Get the original field name:

    fld.SourceField
    

    Get the original table name

    fld.SourceTable
    

    Hint: You can always find available properties in the API of MSDN, in this case: https://msdn.microsoft.com/en-us/library/office/dn123487.aspx or by using the intellisense in the VBA editor.