If you’ve ever imported a text file or a Microsoft Excel worksheet into Microsoft Access, you have seen how Access defaults the text field lengths to 255 characters.  This VBA function will display the maximum length of the values currently in each field of a table so that you can decide how to correctly set the field lengths.  It also shows the data type of each field.

For example, if you call the function using the table that is in the download file below, you would get results in the Immediate Window like the following, with field name, field type, and the maximum value length currently in the table for that field:

?FieldTypeAndMaxValueLen("FieldTypes")
FieldTypeDescription, Type: Text, MaxValLen: 24
FieldTypeConstant, Type: Text, MaxValLen: 12
FieldTypeNumber, Type: Long, MaxValLen: 2
True

The FieldTypes table is a reference table that supplies the description that you see in the function results.  This table was created using DAO values; ADO has it’s own set of values.  DAO code was used because it was very simple and it works without having to add a reference a Microsoft Access object library.

Code:

Public Function FieldTypeAndMaxValueLen(TableName) As Boolean
' This function returns the maximum length of value that is in each
' field in the TableName table.

' FieldTypeAndMaxValueLen() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_FieldTypeAndMaxValueLen

    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strFieldName As String
    Dim strType As String

    ' Open the table.
    Set rst = CurrentDb.OpenRecordset(TableName)

    ' Loop through the collection of fields.
    For Each fld In rst.Fields
        ' Get the field name.
        strFieldName = fld.Name
        strType = DLookup("FieldTypeDescription", "FieldTypes", _
            "FieldTypeNumber=" & fld.Type)
        ' Print the field name and max length of value in that field
        ' to the immediate window.  Use brackets around the field name
        ' in case it has spaces.
        Debug.Print strFieldName & ", Type: " & strType & ", MaxValLen: " _
            & DMax("Len(Nz([" & strFieldName & "],''))", TableName)
    Next fld

    FieldTypeAndMaxValueLen = True

Exit_FieldTypeAndMaxValueLen:
    On Error Resume Next
    rst.Close
    Exit Function

Err_FieldTypeAndMaxValueLen:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "FieldTypeAndMaxValueLen()"
    FieldTypeAndMaxValueLen = False
    Resume Exit_FieldTypeAndMaxValueLen

End Function

Download Code:

FieldTypeAndMaxValueLen.zip