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