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 DOA values; ADO has it's own set of values. DOA code was used because it was very simple and it works without having to add a reference in Access 2007.
FieldTypes
| FieldTypeDescription |
FieldTypeConstant |
FieldTypeNumber |
| Big Integer |
dbBigInt |
16 |
| Binary |
dbBinary |
9 |
| Boolean |
dbBoolean |
1 |
| Byte |
dbByte |
2 |
| Char |
dbChar |
18 |
| Currency |
dbCurrency |
5 |
| Date/Time |
dbDate |
8 |
| Decimal |
dbDecimal |
20 |
| Double |
dbDouble |
7 |
| Float |
dbFloat |
21 |
| GUID |
dbGUID |
15 |
| Integer |
dbInteger |
3 |
| Long |
dbLong |
4 |
| Long Binary (OLE Object) |
dbLongBinary |
11 |
| Memo |
dbMemo |
12 |
| Numeric |
dbNumeric |
19 |
| Single |
dbSingle |
6 |
| Text |
dbText |
10 |
| Time |
dbTime |
22 |
| Time Stamp |
dbTimeStamp |
23 |
| VarBinary |
dbVarBinary |
17 |
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 support@extramiledata.com.
' 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