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
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.