ghudson
Registered User.
- Local time
- Today, 17:07
- Joined
- Jun 8, 2002
- Messages
- 6,195
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.
This is how you import a text file that was exported using the Application.SaveAsText method...
Code:
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
Code:
Application.LoadFromText acForm, "YourFormName", "C:\Temp\Form_frmTest.txt"
