Export All Database Objects Into Text Files

[ghudson]

The ExportDatabaseObjects() function will allow you to export all of your database objects into individual text files for each object [tables, forms, queries, macros, modules]. You can specify where the text files will be exported to by changing the sExportLocation string to a valid path. The Application.SaveAsText method is another undocumented Access option. The exported text files contain everything [properties, VBA, data, etc.] about the object. Similar to the Access documenter but better and this allows you to export the objects and then import them into the same database or any other database of your choosing. You can reverse the method and import [an exported] a text object by switching the Application.SaveAsText with Application.LoadFromText. The ExportDatabaseObjects() function can be used as an alternative method to back up your database objects.

Option Compare Database
Option Explicit

Public Sub ExportDatabaseObjects()
On Error GoTo Err_ExportDatabaseObjects
    
    Dim db As Database
    'Dim db As DAO.Database
    Dim td As TableDef
    Dim d As Document
    Dim c As Container
    Dim i As Integer
    Dim sExportLocation As String
    
    Set db = CurrentDb()
    
    sExportLocation = "C:\Temp\" 'Do not forget the closing back slash! ie: C:\Temp\
    
    For Each td In db.TableDefs 'Tables
        If Left(td.Name, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
        End If
    Next td
    
    Set c = db.Containers("Forms")
    For Each d In c.Documents
        Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Reports")
    For Each d In c.Documents
        Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Scripts")
    For Each d In c.Documents
        Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
    Next d
    
    Set c = db.Containers("Modules")
    For Each d In c.Documents
        Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
    Next d
    
    For i = 0 To db.QueryDefs.Count - 1
        Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
    Next i
    
    Set db = Nothing
    Set c = Nothing
    
    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    
Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

This is how you import a text file that was exported using the Application.SaveAsText method...

Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"

 

[Uncle Gizmo]

More info on this Thread HERE:-

https://www.access-programmers.co.uk/forums/showthread.php?t=296459