Identify table or query that has been aliased in ACCESS 2003 query

Ok, I actually figured it out, but is there a way to list out all alias and what the real name of the table/query is?  He also aliased every column in a table so when you bring up the Datasheet view, you dont know what the field name is for the query referring to in the datasheet as the presentation name is different from the field name in the table properties.

Yes, it will get messy, but seems about the only way.  I was hoping there was a "magical" way, but it is going to be work.  Thank you for the Excel suggestion.

>>   The previous programmer built queries based on queries and tables based on tables and I have to try to figure out what is going on.  <<
That is always a challenge!  I have created attached a utility to this post (the original version I posted publicly can be found here.  the utility is described in detail there).  The attached is an updated version that I have been testing.  The premise of the utility is this --- you enter in a string of text (like the name of a Query object), then you identify which Access objects you wish to search for that string in (Tables, Queries, Forms, Reports, Macros, Modules).  The utility will search the definition of all the objects in the categories you selected for the string you specified.

So ... if you have a Table name of "tblMyTable" and you want to see which Query objects refer to that table, you would enter "tblMyTable", then select only the Query object category.  The utility will then list all the Query objects that have the string pattern of "tblMyTable" in ANY property of the Query object (including the SQL statement).

So, if your SQL statement for a Query object named "selMyQuery" is ...

SELECT * FROM tblMyTable As vTbl

The utility will list the Query object "selMyQuery", the property the string pattern was found in, and if that property is the SQL property, the character position where the string pattern was found is indicated.

>> He also aliased every column in a table so when you bring up the Datasheet view, you dont know what the field name is for the query referring to in the datasheet as the presentation name is different from the field name in the table properties. <<

I hate it when well meaning folks modify the .Caption property of a field in a Table object or Query object.  I especially hate it on Query objects because the datasheet view gives the impression the column is aliased, but its not -- its the .Caption property. When I have ran into this issue, I have used the following code to blank out the caption property.  The .Caption property, by default, is not part of the .Properties collection of a field, however, if the property is filled out, Access will create it for you.  The reason I mention that is because the does not really 'clear' the .Caption property, I remove the property if one exists.  Rest assured that Access will create it again if you, or anyone else, decides to add a value to the Caption property in Design view of either a Table or Query object.

Public Sub CleanCaption()
'Deletes the .Caption property on TableDefs and QueryDefs
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    
    On Error Resume Next
    For Each tdf In db.TableDefs
        If Not (tdf.Attributes And dbSystemObject = dbSystemObject) Then
            For Each fld In tdf.Fields
                fld.Properties.Delete "Caption"
                If Not (Err.Number = 0 Or Err.Number = 3265) Then
                    MsgBox Err.Number & "." & Err.Description
                Else
                    Err.Clear
                End If
            Next fld
        End If
    Next tdf
    
    For Each qdf In db.QueryDefs
        For Each fld In qdf.Fields
            fld.Properties.Delete "Caption"
            If Not (Err.Number = 0 Or Err.Number = 3265) Then
                MsgBox Err.Number & "." & Err.Description
            Else
                Err.Clear
            End If
        Next fld
    Next qdf
    
End Sub

Select allOpen in new window

I hope this information and utility helps you out!
SearchForText-2000-to-2010.zip