SourceField, SourceTable Properties Example (MDB)

The following example creates a Recordset object by using an SQL statement that creates aliases for fields in two different tables in the database. The example then prints the name of the field, the original table, and the original field.

Sub SourceInfo()
    Dim dbs As Database, rst As Recordset, fld As Field
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Construct SQL statement.
    strSQL = "SELECT ProductID As ProductCode, " _
        & "CategoryName As TypeOfProduct FROM Categories " _
        & "INNER JOIN Products ON Categories.CategoryID " _
        & " = Products.CategoryID;"
    ' Open dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset(strSQL)
    For Each fld In rst.Fields
        ' Print field name.
        Debug.Print "Field Name: "; fld.Name
        ' Print original table name.
        Debug.Print "Source Table: "; fld.SourceTable
        ' Print original field name.
        Debug.Print "Source Field: "; fld.SourceField
        Debug.Print
    Next fld
    rst.Close
    Set dbs = Nothing
End Sub