MS Access: How to find greatest length of all fields?

I am working to move an Access database into SQL Server. For this, I need to know how long to make the VARCHAR fields.

Is there a query I can use within Access to return the longest length of a value for each column?

I have found several options using the more advanced T-SQL of SQL Server, but cannot find any way to do this with Access. Since the database has almost 200 columns, I’d prefer to not have to do this with 200 manual queries.

Opss…. sorry, Try this Sub , hope it will help you more.

Sub getFieldSize()
Const SQL As String = "SELECT Max(Len([{fld_len}])) AS Expr1 FROM {tbl}"

    Const constPad01 = 25
    Const constPad03 = 45
    Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field, rs As DAO.Recordset
    Dim sStmt As String, strSchema As String, strSQL As String
    Dim X As Long
    Set db = CurrentDb
    strSchema = "****  " & CurrentDb.Name & "  ****" & vbNewLine
    For Each tbl In db.TableDefs
        If Left(tbl.Name, 4) <> "MSys" Then
            strSchema = strSchema & "===================================" & vbNewLine & vbTab & tbl.Name & vbNewLine
            For Each fld In tbl.Fields
                If Len(fld.Name) > 25 Then
                    sStmt = " " & fld.Name & String(constPad03 - Len(fld.Name), " ")
                Else
                    sStmt = " " & fld.Name & String(constPad01 - Len(fld.Name), " ")
                End If
                Select Case fld.Type
                    Case dbText:
                        strSQL = Replace(SQL, "{fld_len}", fld.Name)
                        strSQL = Replace(strSQL, "{tbl}", tbl.Name)
                        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
                        If rs.RecordCount > 0 Then X = rs!Expr1
                        Set rs = Nothing
                        sStmt = sStmt & "text length " & " (" & X & ")"
                        X = 0
                    Case dbMemo:
                        strSQL = Replace(SQL, "{fld_len}", fld.Name)
                        strSQL = Replace(strSQL, "{tbl}", tbl.Name)
                        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
                        If rs.RecordCount > 0 Then X = rs!Expr1
                        Set rs = Nothing
                        sStmt = sStmt & "memo length" & " (" & X & ")"
                        X = 0
                    Case Else:
                        sStmt = ""
                End Select
               If sStmt <> "" Then strSchema = strSchema & sStmt & vbNewLine
            Next
        End If
    Next
    Debug.Print strSchema
    Set db = Nothing
    Open CurrentProject.Path & "Schema.txt" For Output As #1: Print #1, strSchema: Close #1

End Sub

SELECT 
Max(Len([Fieldname1])) AS Expr1, 
Max(Len([Fieldname2])) AS Expr2,  
Max(Len([Fieldname3])) AS Expr3 
FROM Tablename;

This will show you the max length of each field in the table.