We all know about the evils of Lookup fields and why they are to be avoided at all costs! If you aren’t already familiar with why they are to never be used, then take a few moments to review The Evils of Lookup Fields in Tables (pay special attention regarding Upsizing should you ever think you will need to eventually port your db to SQL Server, or another RDMS one day!) and Did I Mention I Have a Thing Against Lookup Fields in Tables?.

The problem is that many novice developers are not aware of the above and use them, some abundantly. So when I take over their projects I need to easily find these fields so I can remove them and set things up properly. As such, here is a simple little procedure I put together that will list all the table lookup field in a database.

Continue reading →

Sometimes it can be useful to quickly evaluate the databases tables structures.  One aspect that can tell an experienced developer a whole lot is the number of fields in a table.  Unlike Excel, a properly normalized database is one that does not have tables with large numbers of fields.  I personally start to question database design when I start seeing tables with 20+ fields in them.  That isn’t to say they’re aren’t legitimate instances for tables with more fields, but in general terms this is a good indicator that there could be basic data normalization problems.

As such, I created 2 (just to illustrate that is more than one way to get the information) simple functions to get this information.

'---------------------------------------------------------------------------------------
' Procedure : GetTblFldCount
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the number of fields in the specified table
' 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:
' ~~~~~~~~~~~~~~~~
' sTblName  : Name of the table you which to get the field count of
'
' Usage:
' ~~~~~~
' GetTblFldCount("Contact")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-02-07              Initial Release
'---------------------------------------------------------------------------------------
Function GetTblFldCount(sTblName As String)
    On Error GoTo Error_Handler

    GetTblFldCount = CurrentDb.TableDefs(sTblName).Fields.Count

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number = 3265 Then
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: 3265" & vbCrLf & _
               "Error Source: GetTblFldCount" & vbCrLf & _
               "Error Description: The table '" & sTblName & "' could not be found in the current database.", _
               vbCritical, "An Error has Occurred!"
    Else
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: GetTblFldCount" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetTblFldCount2
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the number of fields in the specified table
' 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:
' ~~~~~~~~~~~~~~~~
' sTblName  : Name of the table you which to get the field count of
'
' Usage:
' ~~~~~~
' GetTblFldCount("Contact")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-02-07              Initial Release
'---------------------------------------------------------------------------------------
Function GetTblFldCount2(sTblName As String)
    On Error GoTo Error_Handler
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [" & sTblName & "] WHERE (FALSE);")
    GetTblFldCount2 = rs.Fields.Count

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Function

Error_Handler:
    If Err.Number = 3265 Then
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: 3265" & vbCrLf & _
               "Error Source: GetTblFldCount2" & vbCrLf & _
               "Error Description: The table '" & sTblName & "' could not be found in the current database.", _
               vbCritical, "An Error has Occurred!"
    Else
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: GetTblFldCount2" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

and then to simplify the process of manually inputting each table name one by one, we could create a simple procedure to quick check every table within the current database, and it would look something like:

Public Sub CheckNoTblFlds()
    On Error GoTo Error_Handler
    Dim db As DAO.Database
    Dim sTblName As String
 
    Set db = CurrentDb()
    For i = 0 To db.TableDefs().Count - 1
        sTblName = db.TableDefs(i).Name
        If Not (sTblName Like "MSys*") And Not (sTblName Like "~*") Then 'Let ignore system tables
            Debug.Print sTblName & vbTab & GetTblFldCount(sTblName)
            'Debug.Print sTblName & vbTab & GetTblFldCount2(sTblName)
        End If
    Next i
    
Error_Handler_Exit:
    On Error Resume Next
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CheckNoTblFlds" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

So now, by entering one command into the immediate window I can get a report on every single (non-system) table in the database.

Part of the process of trying to optimize client databases always involves analyzing tables to try and find potential problems. For instance, I like to quickly identify tables containing Attachment and Memo fields. Now if you’re dealing with a simple database it may be conceivable to open each table one by one and look over the fields manually, but this isn’t efficient, can lead to human error, and certainly is not an option for large databases. As such I put together a simply procedure to perform the check for me and I thought I’d share with the community at large.

'---------------------------------------------------------------------------------------
' Procedure : FindTblWFieldsType
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Identify which table contains the specified Field Type
' 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 FindTblWFieldsType(dbAttachment)
' Call FindTblWFieldsType(dbMemo)
' Call FindTblWFieldsType(dbBoolean)
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-Oct-03                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub FindTblWFieldsType(sFieldType As DataTypeEnum)
    On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim td              As DAO.TableDefs
    Dim t               As DAO.TableDef
    Dim tdfld           As DAO.TableDef
    Dim fld             As Field

    Set db = CurrentDb()
    Set td = db.TableDefs
    For Each t In td    'loop through all the fields of the tables
        If Left(t.Name, 4) = "MSys" Then 'ignore system tables
            GoTo Continue
        End If
        
        For Each fld In t.Fields    'loop through all the fields of the tables
            If fld.Type = sFieldType Then  'Find attachment fields
                Debug.Print "Processing Table: " & t.Name
                Debug.Print vbTab & "Field: " & fld.Name & " is a(n) " & FieldTypeName(fld) & " field."
                Debug.Print ""
            End If
        Next
        
Continue:
    Next

Error_Handler_Exit:
    On Error Resume Next
    Set tdfld = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindTblWFieldsType" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Function FieldTypeName(fld As DAO.Field) As String
    'Purpose: Converts the numeric results of DAO Field.Type to text.
    'Source/Copyright: Allen Browne
    'URL: http://allenbrowne.com/func-06.html
    Dim strReturn As String    'Name to return

    Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        Case dbCurrency: strReturn = "Currency"         ' 5
        Case dbSingle: strReturn = "Single"             ' 6
        Case dbDouble: strReturn = "Double"             ' 7
        Case dbDate: strReturn = "Date/Time"            ' 8
        Case dbBinary: strReturn = "Binary"             ' 9 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"        '(no interface)
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15

        'Attached tables only: cannot create these in JET.
        Case dbBigInt: strReturn = "Big Integer"        '16
        Case dbVarBinary: strReturn = "VarBinary"       '17
        Case dbChar: strReturn = "Char"                 '18
        Case dbNumeric: strReturn = "Numeric"           '19
        Case dbDecimal: strReturn = "Decimal"           '20
        Case dbFloat: strReturn = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23

        'Constants for complex types don't work prior to Access 2007 and later.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select

    FieldTypeName = strReturn
End Function

As indicated in the comments of the main procedure, you simply call the sub by:

Call FindTblWFieldsType(dbAttachment)

and it will return (in the VBE immediate window) a result like:

Processing Table: tblTransaction
    Field: Receipt is a(n) Attachment field.
    Field: RefDoc is a(n) Attachment field.

Processing Table: tblTransactionConfirmation
    Field: ConfirmationNotice is a(n) Attachment field.

You might ask what is the point of Allen Browne’s FieldTypeName function. It is used to convert the returned field type (which is numeric) into a comprehensible text. For example, it converts 101 into the word ‘Attachment’ which I use in the reporting. That said since you perform a search for a single field type that you specify, it could be stripped out of my procedure, but I left it to make it as complete and as ‘idiot proof’ as possible to avoid any possible problems.

I hope this help you quickly take control of your database analysis.

Once again, trying to help someone in a forum and quickly put together the following to enumerate a list of indexes for a given table:

'---------------------------------------------------------------------------------------
' Procedure : Table_ListIndexes
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : List the names of the indexes in the specified table
' 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: Microsoft Office XX.0 Access database engine Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTbl      : Name of the table to list the names of the Indexes from
'
' Usage:
' ~~~~~~
' Call Table_ListIndexes("tbl_Appointments")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-11-20                  Initial Release
' 2         2022-03-25                  Header Usage Correction, per blog comment
' 3         2022-03-25                  Added field enumeration per Brent Spaulding and
'                                       Tom Wickerath's recommendations
'                                       Renamed function, updated header and error
'                                       handler
'---------------------------------------------------------------------------------------
Function Table_ListIndexes(sTbl As String)
On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim tIndex          As DAO.Index
    Dim fld             As DAO.Field

    Set db = CurrentDb
    Set tdf = db.TableDefs(sTbl)

    For Each tIndex In tdf.Indexes
        Debug.Print tIndex.Name
        For Each fld In tIndex.Fields
           Debug.Print , fld.Name
        Next
    Next

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

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