I recently took over a database in which the previous dba had set a number of table fields to act as lookup displaying combo boxes. This is a horrible thing to do, IMHO, and since users should never directly have access to tables, and should be working through forms, there is absolutely no reason for this in the first place. Now not wanting to have to go through hundreds of tables and countless fields resetting this property manually, I decided to write a simple function to perform this cleanup for me. Hopefully this might help someone else out with this issue.

'---------------------------------------------------------------------------------------
' Procedure : RemoveAllTableLookupCboLst
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loops through all the tables in a database and removes/resets the Lookup
'             Display Control property to reset it to a simple textbox rather than a
'             combo box or listbox.  Never needed as users should never access tables
'             directly!
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' Call RemoveAllTableLookupCboLst
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-05-17              Initial Release
' 2         2019-02-23              Updated Header
'                                   Updated Error Handler
'---------------------------------------------------------------------------------------
Function RemoveAllTableLookupCboLst() As String
    Dim db              As DAO.Database
    Dim td              As DAO.TableDefs
    Dim t               As DAO.TableDef
    Dim fld             As Field
 
    On Error GoTo Error_Handler

    Set db = CurrentDb()
    Set td = db.TableDefs
    On Error Resume Next
    For Each t In td    'Loop through each table
        If Left(t.Name, 4) <> "MSys" Then    'Don't mess with system tables!
            For Each fld In t.Fields    'loop through each field
                If fld.Type = dbText Then    'Only modify Text fields
                    fld.Properties("DisplayControl") = acTextBox
                End If
            Next fld
        End If
    Next t

Error_Handler_Exit:
    On Error Resume Next
    If Not td Is Nothing Then Set td = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveAllTableLookupCboLst" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

I used to use table field captions extensively until I started to run into a weird problem in which I couldn’t reassign a fieldname in a query with a new name.  No matter what I did, it always reverted to the field caption.  After a lot of testing, I eventually found the link.  Delete the link and the SQL Alias worked, put the caption back and again the Alias wouldn’t work?!

It has now become common practice for me, as well as many other professional developers, to delete all the field captions from any database I work on to ensure the flexibility of ALIASes in my queries.  As such, I developed the procedure below to quickly delete the captions from all the tables within a database, instead of trying to do this manually.

'---------------------------------------------------------------------------------------
' Procedure : ClearAllCaptions
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove all captions from all the fields in all the non-system tables
'             because of issues that caption cause, mainly:
'               Captions prevent you from being able to assign a new name in a query
'               unless you do a calculation with it
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call ClearAllCaptions
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jun-19                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ClearAllCaptions()
    On Error GoTo Error_Handler
    Dim Db              As DAO.Database
    Dim sPropName       As String
    Dim fld             As Field
    Dim iTbls           As Integer
    Dim iNonSysTbls     As Integer
    Dim ifldCount       As Integer

    Set Db = CurrentDb
    sPropName = "Caption"
    iNonSysTbls = 0
    ifldCount = 0

    For iTbls = 0 To Db.TableDefs.Count - 1             'Loop through the table collection
        If (Db.TableDefs(iTbls).Attributes And dbSystemObject) = 0 Then    'Ensure the table isn't a system table
            'we don't want to mess around with them
            For Each fld In Db.TableDefs(iTbls).Fields  'Loop through the table fields
                fld.Properties.Delete (sPropName)   'Delete any captions
                ifldCount = ifldCount + 1
            Next fld
            iNonSysTbls = iNonSysTbls + 1
        End If
    Next iTbls

    If iTbls > 0 Then
        MsgBox "Out of a total of " & iTbls & " tables in the current database, " & _
               iNonSysTbls & " non-system tables " & _
               " were processed, in which a total of " & ifldCount & " fields " & _
               "had their '" & sPropName & "' property deleted."
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set Db = Nothing
    Exit Sub

Error_Handler:
    If Err.Number = 3265 Then
        Resume Next
    Else
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ClearAllCaptions" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End If
End Sub

This was the question put forth by someone on an Access Forum recently and I thought I’d share one possible way to determine this.

This is a brute force method, but it works! I simply loop through all the tables one by one and loop through all the fields within each table one by one. It is that simple. Here is the code.

'---------------------------------------------------------------------------------------
' Procedure : WhereFieldLocated
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine/Locate in which Table(s) a field is located
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFieldName: The name of the field you are trying to locate
'
' Usage:
' ~~~~~~
' WhereFieldLocated "Filed1"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-Aug-17                 Initial Release
'---------------------------------------------------------------------------------------
Function WhereFieldLocated(sFieldName As String)
    Dim db            As DAO.Database
    Dim td            As DAO.TableDefs
    Dim fld           As DAO.Field

    Set db = CurrentDb()
    Set td = db.TableDefs
    For Each t In td    'loop through all the tables in the database
        If Left(t.Name, 4) = "MSys" Then GoTo Continue
        For Each fld In t.Fields    'loop through all the fields of the table
            If fld.Name = sFieldName Then
                Debug.Print t.Name
            End If
        Next
Continue:
    Next

    Set fld = Nothing
    Set td = Nothing
    Set db = Nothing
End Function