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 profesional 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 occured." & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: ClearAllCaptions" & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical, "An Error has Occured!" Resume Error_Handler_Exit End If End Sub |
