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.