今日のトリビアAccessは固定長のテーブルに勝手になっている場合がある

条件

短いテキストで255のままで複数の列を作る
文字列のあとに空白が続き、いくらやっても削除できない現象が起きている場合は成功

影響

テキストフィールドどうしを比較する場合、255文字の事実上の固定長のフィールドとは見えないスペースで埋められると全部Falseになるため254文字でスペースがないテキストフィールドと比較ができなくなるため。
"ABC"
"ABC "（実際はスペースがずっと続く）
こんな感じの比較になり、一致しないとなってしまう。
この場合の比較はNzやTrimは効果がなく、スペースが絶対に消えない状態となる。
このため、本来は一致するはずものが一致しなくなる。

解決方法

テキスト列のうち、どれか一つでいいので255字から254字にする。

✅ 関連する可能性がある技術的知見

1. 固定長TEXT列の作成方法

通常のテーブルデザインではTEXT型は可変長。
ただし、CHAR(255) のように DDL（データ定義クエリ）で CHAR を使うと固定長になる。
この場合、未入力でも末尾がスペースで埋められる。

2. AccessのTEXT型は基本的に可変長

TEXT(255) であっても、通常は可変長として扱われる。
ただし、「すべてのTEXT列が255文字」だとAccessが内部的に固定長とみなすという挙動については、公式文書や技術記事では明示されていない。

3.

この挙動は、Accessの内部最適化やストレージエンジンの仕様に起因する可能性が高いが、公式には未公開の仕様とみられる。
🔍 考察：なぜこのような挙動が起きるのか？
AccessはJet/ACEエンジンでテーブル構造を最適化する際、全列が最大長（255）のTEXT型である場合、固定長として最適化する可能性があるのかもしれない。確かに閉じるとき最適化オプションを使っている。。
これは、データの読み書き効率を上げるための内部最適化と推測される。
しかし、この最適化が逆に「ゴミ文字の混入」や「文字列末尾の異常」などの副作用を引き起こすことがある。
✅ 実務上の対策（ユーザーの知見に基づく）
TEXT列のうち1つでも254文字にすることで、固定長最適化を回避できる。
これにより、可変長テーブルとして扱われ、文字列の混入や末尾の異常が発生しなくなる。
VBAで行う場合は、テキスト列を追加し、それにコピー、もとの列を削除し、リネームする。
TTest1というテーブルのtxtF001をコピーする

Option Compare Database
Option Explicit

'=== ユーティリティ：フィールド存在確認
Private Function FieldExists(ByVal tdf As DAO.TableDef, ByVal fldName As String) As Boolean
    On Error GoTo EH
    Dim f As DAO.Field
    Set f = tdf.Fields(fldName)
    FieldExists = True
    Exit Function
EH:
    FieldExists = False
End Function

'=== 旧列にぶら下がるインデックスを削除
Private Sub DropIndexesOnField(ByVal tdf As DAO.TableDef, ByVal fldName As String)
    Dim idx As DAO.Index
    Dim i As Long, j As Long
    For i = tdf.Indexes.Count - 1 To 0 Step -1
        Set idx = tdf.Indexes(i)
        For j = 0 To idx.Fields.Count - 1
            If idx.Fields(j).Name = fldName Then
                tdf.Indexes.Delete idx.Name
                Exit For
            End If
        Next
    Next
    tdf.Indexes.Refresh
End Sub

'=== テーブル間リレーションから当該フィールドを参照するものを削除
Private Sub DropRelationsOnField(ByVal db As DAO.Database, ByVal TBL As String, ByVal fld As String)
    Dim rel As DAO.Relation, rf As DAO.Field
    Dim i As Long, j As Long
    For i = db.Relations.Count - 1 To 0 Step -1
        Set rel = db.Relations(i)
        If (rel.Table = TBL) Or (rel.ForeignTable = TBL) Then
            For j = 0 To rel.Fields.Count - 1
                Set rf = rel.Fields(j)
                ' DAO.Relation.Fields(j).Name と .ForeignName の双方を判定
                If rf.Name = fld Or rf.ForeignName = fld Then
                    db.Relations.Delete rel.Name
                    Exit For
                End If
            Next
        End If
    Next
End Sub
Function isTableExist(tName As String)
Dim tdf As DAO.TableDef
Dim bl As Boolean: bl = False
For Each tdf In CurrentDb.TableDefs
If tdf.Name = tName Then bl = True: isTableExist = True: Exit Function
Next
isTableExist = False
End Function
'=== 開いているオブジェクトを全部閉じる（あなたの方針を尊重）
Public Sub AllObjectClose()
    Dim ao As AccessObject
    If MsgBox("開いているTable/Query/Report/Formを保存せず閉じる（acSaveNo）。キャンセルで中止。", _
              vbOKCancel + vbCritical, "確認") = vbCancel Then Exit Sub

    For Each ao In CurrentProject.AllReports
        If SysCmd(acSysCmdGetObjectState, acReport, ao.Name) <> 0 Then DoCmd.Close acReport, ao.Name, acSaveNo
    Next
    For Each ao In CurrentProject.AllForms
        If SysCmd(acSysCmdGetObjectState, acForm, ao.Name) <> 0 Then DoCmd.Close acForm, ao.Name, acSaveNo
    Next
    Dim qd As DAO.QueryDef
    For Each qd In CurrentDb.QueryDefs
        If SysCmd(acSysCmdGetObjectState, acQuery, qd.Name) <> 0 Then DoCmd.Close acQuery, qd.Name, acSaveNo
    Next
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Left$(tdf.Name, 4) <> "MSys" Then
            If SysCmd(acSysCmdGetObjectState, acTable, tdf.Name) <> 0 Then DoCmd.Close acTable, tdf.Name, acSaveNo
        End If
    Next
End Sub

'=== 本体：txtF001 → txtF002(254)に複写 → 旧削除 → txtF002をtxtF001へ改名
Public Sub Test_Migrate254()
    Const TBL As String = "T_test1"
    Const OLD As String = "txtF001"
    Const TMP As String = "txtF002"

    Dim db As DAO.Database: Set db = CurrentDb
    Dim tdf As DAO.TableDef:
    Dim CAT As ADOX.Catalog, cTBL As ADOX.Table, cCol As ADOX.Column

    Dim ws As DAO.Workspace: Set ws = DBEngine.Workspaces(0)

    ' 0) まず全部閉じる
    Call AllObjectClose

    DoCmd.SetWarnings False
    On Error GoTo EH

    ws.BeginTrans:
    If isTableExist(TBL) Then DoCmd.RunSQL "DROP TABLE " & TBL
    DoEvents
    DoCmd.RunSQL "CREATE  TABLE T_test1(ID Counter Primary Key, txtF001 Text(255), txtF003 Text(255))"
     Set tdf = db.TableDefs(TBL)
    ' ADOXを使うなら接続だけ（処理自体はDAOで完結）
    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = CurrentProject.Connection

    ' 1) 既存TMPがあれば削除（保険）
    On Error Resume Next
    If FieldExists(tdf, TMP) Then
        tdf.Fields.Delete TMP
        tdf.Fields.Refresh
    End If
    On Error GoTo EH

    ' 2) 一時列（254文字）を追加
    tdf.Fields.Append tdf.CreateField(TMP, DAO.DataTypeEnum.dbText, 254)
    ' 必要ならゼロ長許可等を合わせる（任意）
    On Error Resume Next
    tdf.Fields(TMP).Properties("AllowZeroLength").Value = True
    On Error GoTo EH
    tdf.Fields.Refresh

    ' 3) 値の複写（OLDがある場合）
    If FieldExists(tdf, OLD) Then
        db.Execute "UPDATE [" & TBL & "] SET [" & TMP & "] = [" & OLD & "];", dbFailOnError
    End If

    ' 4) 依存の除去（インデックス→リレーションの順）
    Call DropIndexesOnField(tdf, OLD)
    Call DropRelationsOnField(db, TBL, OLD)

    ' 5) 旧列削除
    If FieldExists(tdf, OLD) Then
        tdf.Fields.Delete OLD
        tdf.Fields.Refresh
    End If

    ' 6) 一時列を旧名に改名
    tdf.Fields(TMP).Name = OLD
    tdf.Fields.Refresh
    Set cTBL = CAT.Tables(tdf.Name)
    Set cCol = cTBL.Columns("txtF001")
    ' 順番を変更はdao
    tdf.Fields(cCol.Name).OrdinalPosition = 1
    tdf.Fields.Refresh
    Set tdf = Nothing
    Application.RefreshDatabaseWindow
    ws.CommitTrans

FINALLY:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set tdf = Nothing
    Set db = Nothing
    If Not CAT Is Nothing Then Set CAT = Nothing
    Set ws = Nothing
    Exit Sub

EH:
    If ws Is Nothing = False Then
        On Error Resume Next
        ws.Rollback
    End If
    Debug.Print "Err " & Err.Number & " : " & Err.Description
    MsgBox "エラー " & Err.Number & " / " & Err.Description, vbExclamation
    Resume FINALLY
End Sub

🔚 結論
Accessでは、すべてのTEXT列が255文字だと内部的に固定長テーブルとして扱われる可能性がある。
この挙動は公式には明示されていないが、実務上は254文字にすることで回避できる。

対策方法

ロジックとしては
255文字のフィールドをF001とすると
これにたいして254文字のF001_Newを作り、
値をコピーしたうえで、
254の方をRenameする。