Document a Legacy Access Database with AI

Step 0: Work on Copies

Step 1: Build an Inventory

Public Sub BuildInventory()
    Dim db As DAO.Database, doc As DAO.Document, ctr As DAO.Container
    Dim td As DAO.TableDef
    Set db = CurrentDb
    On Error Resume Next
    db.Execute "DROP TABLE zInventory"
    On Error GoTo 0
    db.Execute "CREATE TABLE zInventory (ObjType TEXT(20), ObjName TEXT(255), " & _
               "LinkedTo TEXT(255), Modified DATETIME)"

    For Each td In db.TableDefs
        If Left$(td.Name, 4) <> "MSys" And Left$(td.Name, 1) <> "~" Then
            db.Execute "INSERT INTO zInventory VALUES ('Table','" & td.Name & "','" & _
                       Replace(td.Connect, "'", "''") & "',#" & _
                       Format(td.LastUpdated, "yyyy-mm-dd hh:nn:ss") & "#)"
        End If
    Next td

    For Each ctr In db.Containers
        Select Case ctr.Name
            Case "Forms", "Reports", "Scripts", "Modules"
                For Each doc In ctr.Documents
                    db.Execute "INSERT INTO zInventory VALUES ('" & ctr.Name & "','" & _
                               doc.Name & "','',#" & _
                               Format(doc.LastUpdated, "yyyy-mm-dd hh:nn:ss") & "#)"
                Next doc
        End Select
    Next ctr

    Dim qd As DAO.QueryDef
    For Each qd In db.QueryDefs
        If Left$(qd.Name, 1) <> "~" Then
            db.Execute "INSERT INTO zInventory VALUES ('Query','" & qd.Name & "','',#" & _
                       Format(qd.LastUpdated, "yyyy-mm-dd hh:nn:ss") & "#)"
        End If
    Next qd
End Sub

Step 2: Export Everything as Text

Public Sub ExportAllAsText(ByVal folder As String)
    Dim db As DAO.Database, qd As DAO.QueryDef, f As Integer
    Dim obj As AccessObject
    Set db = CurrentDb
    If Right$(folder, 1) <> "\" Then folder = folder & "\"
    MkDir folder & "queries": MkDir folder & "forms"
    MkDir folder & "reports": MkDir folder & "modules": MkDir folder & "macros"

    For Each qd In db.QueryDefs
        If Left$(qd.Name, 1) <> "~" Then
            f = FreeFile
            Open folder & "queries\" & qd.Name & ".sql" For Output As #f
            Print #f, qd.SQL
            Close #f
        End If
    Next qd
    For Each obj In CurrentProject.AllForms
        Application.SaveAsText acForm, obj.Name, folder & "forms\" & obj.Name & ".txt"
    Next obj
    For Each obj In CurrentProject.AllReports
        Application.SaveAsText acReport, obj.Name, folder & "reports\" & obj.Name & ".txt"
    Next obj
    For Each obj In CurrentProject.AllModules
        Application.SaveAsText acModule, obj.Name, folder & "modules\" & obj.Name & ".bas"
    Next obj
    For Each obj In CurrentProject.AllMacros
        Application.SaveAsText acMacro, obj.Name, folder & "macros\" & obj.Name & ".txt"
    Next obj
End Sub

Step 3: Let AI Explain Each Object

For each Access query below, return a table with:
name | purpose in one sentence | tables read | tables changed (if action query)
| parameters or form references | risks (deletes, make-table, hard-coded dates).
Use plain business language.

[paste 10–20 query SQL texts with their names]

Explain this Access VBA module for a non-programmer:
1. What each procedure does and when it runs (event, button, startup).
2. Every table it reads or writes.
3. Any file paths, servers, emails or external systems it touches.
4. Anything that deletes or overwrites data.
5. Likely problems if the back end moved to SQL Server.

[paste module]

Step 4: Find What Is Actually Used

• Last-modified dates

  Objects untouched for many years are candidates, but not proof: a stable report may simply work.

• References

  Search the exported text for each query name. A query referenced by no form, report, macro or module, and not run by users directly, is probably dead.

• Usage logging

  Add a one-line call in each form's Open event and each report's Open event to log name, user and time. A month of logs tells you what people use.

• Interviews

  Ask each user to show you their weekly routine. People use objects in ways nobody planned.

Step 5: Write It Up

What Usually Turns Up

• Queries with hard-coded years that someone edits every January.

• Back end on a mapped drive letter that only works on some PCs.

• VBA using 32-bit Declare statements that will break on 64-bit Office.

• No referential integrity, and orphan records in child tables.

• Passwords for SQL Server or email in plain text in a module.

• An Excel file that is imported nightly and is the real source of truth.