Restore Database Objects (LoadFromText)

[lcarpay]

hi all, today it was my turn to have a corrupted db :banghead:. Luckily I found this thread:- Export All Database Objects Into Text Files But I missed a way to restore the objects again in a new empty database. So I created a table to store the saved objects.

step 1 => define the table. I named it "_ApplicationObjectList":
ID (AutoNumber)
ObjType (Long Integer)
ObjName (Short Text 255)
ObjLocation (Short Text 255)
sysDateAdded (Date/Time default:Now())

step 2 => run the adjusted procedure from ghudson (see thread 99179). I did not include the table objects!:

Public Sub ExportDatabaseObjects()
  ' https://access-programmers.co.uk/forums/showthread.php?t=99179
  On Error GoTo Err_ExportDatabaseObjects
    
  Dim db As Database
  Dim td As TableDef
  Dim d As Document
  Dim c As Container
  Dim i As Integer
  Dim sExportLocation As String
  Dim strSql As String
  
  Set db = CurrentDb()
  
  sExportLocation = "C:\YOURLOCATION\"
  
  '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
    strSql = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acForm & " as ObjType, '" & d.Name & "' as ObjName, '" & sExportLocation & "Form_" & d.Name & ".txt" & "' as ObjLocation;"
    db.Execute strSql, dbSeeChanges
    Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt"
  Next d
  
  Set c = db.Containers("Reports")
  For Each d In c.Documents
    strSql = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acReport & " as ObjType, '" & d.Name & "' as ObjName, '" & sExportLocation & "Report_" & d.Name & ".txt" & "' as ObjLocation;"
    db.Execute strSql
    Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt"
  Next d
  
  Set c = db.Containers("Scripts")
  For Each d In c.Documents
    strSql = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acMacro & " as ObjType, '" & d.Name & "' as ObjName, '" & sExportLocation & "Macro_" & d.Name & ".txt" & "' as ObjLocation;"
    db.Execute strSql
    Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt"
  Next d
  
  Set c = db.Containers("Modules")
  For Each d In c.Documents
    strSql = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acModule & " as ObjType, '" & d.Name & "' as ObjName, '" & sExportLocation & "Module_" & d.Name & ".txt" & "' as ObjLocation;"
    db.Execute strSql
    Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt"
  Next d
  
  For i = 0 To db.QueryDefs.count - 1
    strSql = "insert into _ApplicationObjectList ( ObjType, ObjName, ObjLocation ) " & _
             "select " & acQuery & " as ObjType, '" & db.QueryDefs(i).Name & "' as ObjName, '" & sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt" & "' as ObjLocation;"
    db.Execute strSql
    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

step 3 => restore the text again to a newly create empty db
import the table "_ApplicationObjectList" to your new database and run the code:

Sub RestoreDatabaseObjects()
  Dim strSql As String
  Dim rs As DAO.Recordset
  
  strSql = "select * from _ApplicationObjectList"
  Set rs = CurrentDb.OpenRecordset(strSql)
  
  If Not rs.BOF And Not rs.EOF Then
    rs.MoveFirst
    While (Not rs.EOF)
      Debug.Print rs.Fields("ObjName")
      Application.LoadFromText rs.Fields("ObjType"), rs.Fields("ObjName"), rs.Fields("ObjLocation")
      rs.MoveNext
    Wend
  End If
  rs.Close
  Set rs = Nothing
End Sub

step 4 => wrap up:
I had to set some references in the VBE environment and import the linked SQL tables again.

That's was it. All stable again.

best regard, leon