Navigation:  2012 > Exporting and Recovering Programming Objects

by Garry Robinson     Access 2010/2007

 

In this article, I will show you how to save queries, forms, reports, macros, and modules (which I'll call programming objects from now on) to text files. I encourage this particular backup approach because it offers additional recovery opportunities and helps you in these specific situations:

▪If more than one person is developing software for the database, your systems for cooperating will not always be perfect, and someone's good hard work will be lost.

▪If an object in a database becomes irretrievably damaged

▪If the database is partially damaged, this function may stop exporting  on the damaged object which will pinpoint the problem

▪If you're adding protection to the database, such as passwords

In all these scenarios, backing up the programming objects at regular intervals may help recover the object. See also why this is important in working out the size of objects

 

 

Saving and Retrieving Objects by using Hidden Methods

 

Unless you suffer from the same compulsive Web-searching disorder that I am afflicted with, you will probably be blissfully unaware that it is possible to save all your queries, forms, reports, macros, and modules to text files. So what, you might ask? Saving objects as text files means that you have a copy of an object that is external to any database that you are developing. If an object is inadvertently changed, you can retrieve that object from the text file. Once you understand the concepts behind saving objects to text, you will find many possible uses for it, such as using the files to interchange objects between developers and recovering objects from corrupt databases.

 

To import and export programming objects, we can use two undocumented (hidden) methods of the Application object called LoadFromText and SaveToText. These methods both require you to specify the object type by using the Access constants, the name of the Access object, and the destination or retrieval location of the file. To illustrate this process, open the Immediate window (press CTRL+G) in the Visual Basic Editor and type Application.SaveAsText. From then on, Intellisense will provide you with the list of constants and arguments to complete the statement. In Figure 1, I have put together an example to show how you can export and import a form.

Figure 1: The SaveAsText and LoadFromText methods viewed in the Immediate window.

 

Exporting all Programmable Objects to Text Files

A good way to describe backing up objects to text is to work through an example from the demonstration database. This demonstration will create a file for each programmable object in the database. You will be able to run this utility even if other people are using the database. This example will coexist well with the Exportiing all tables to text example described here.

 

To experiment with this download, do the following:

1.Make a copy of your database in a test folder and then open the copy.

2.Import the following objects from the demonstration database: frmBackupAllObjects, bas_exportObjects.

3.Compile all the code by using the Debug menu in the Visual Basic Editor.

4.Open the form frmBackupAllObjects (shown in Figure 2) and click the Back Up All Objects to Text button to start the backups.

 

Figure 2: The Back Up All Objects to Text form.

5.When the exports are completed, a message box (as shown in Figure 3) will tell you where the text copies of the objects are. It will also tell you the name of a text file that you can use to recover all the objects into a blank database by using VBA.

 

Figure 3: The message that appears to tell you where the files went and how to recover them.

 

 

Now that the exports are complete, let's have a look at what has happened. All the objects are now stored in text files in a subfolder where the current database is located (shown in Figure 4). The file types used for saving the files are *.QRY for queries, *.FRM for forms, *.RPT for reports, *.MCR for macros, *.BAS for modules, and *.CLS for class modules.

Figure 4: Folder showing all the objects exported to individual files.

 

To gain an understanding of the structure of the exported objects, let us look at the text version of a query that was created by the SaveAsText method (as shown Figure 5).

Figure 5: The Product Sales query after being exported to a backup text file.

 

As you can see, not only is the SQL stored, but the column properties, field types, and other details are also stored in the file. It is this complete detail that allows Access to import the objects that are exported, which allows us then to recover a damaged or deleted object.

Okay, so now we have created all the text copies of the database; what use are they if we cannot recover them? To assist in loading all these files back into a database, the export process generated an object recovery file. This file has all the necessary VBA code to import the objects back into an empty database, which I will now describe how to do.

 

Importing all Programmable Objects into a Blank Database

Retrieving all or some of these objects back from a folder requires you to create VBA code by using the Application object's LoadFromText method once for every object in the database. Writing this sort of code manually for even a small database would be very tedious. To automate this process, the Export All Objects software automatically generates a text file called YourDatabase_rebuildBas.txt (shown in Figure 4). This file contains VBA code that will load all the objects into a blank database.

 

Figure 6: The VBA recovery file that helps import all the objects into a blank database.

To load all the objects into a new database, follow these steps:
 

1.Open a new blank database.

2.Open the Visual Basic Editor (press ALT+F11).

3.Choose File Ø Import File.

4.Find the file (its name should be YourDatabase_rebuildBas.txt) and click Open.

5.Find the module in the Project Explorer, which you can view by choosing View Ø Project Explorer.

6.Open the Immediate window.

7.Type "call RebuildDatabase" into the Immediate window.

8.Because this database started as a blank project, you need to check your VBA project references by choosing Tools Ø References. You will probably be missing references such as DAO and Microsoft Office 10.

As an alternative, you can actually use the LoadFromText method to load the individual VBA object recovery files into the database. To do this, open the Immediate window and type

 

LoadFromText acModule, "RebuildDatabase", _

  "c:\Backups\YourDatabase BackupObjects\YourDatabase_rebuildBas.txt"

 

 

 

Now I will retrace my steps a little to discuss the VBA code that makes backing up and recovering objects possible.

 

How Exporting of Objects to Text Works

The following onClick procedure for the form frmBackupAllObjects shows you how to integrate the exporting software into your database. This procedure establishes both a folder for the backup plus a name for the VBA recovery file. It then calls the exportObjectsToText_FX subroutine, which you will find in the basGR8_exportObjects module.

 

Private Sub cmdBackupToText_Click()

' Back up all queries, forms, reports, macros, and modules to text files.

Const OBJFOLDER = "BackupObjects\"

Const REBUILDOBJ = "_rebuildBas.txt"

Dim exportAllOK As Boolean, backUpFolder As String

Dim dbNameStr As String, rebuildFile As String

 

backUpFolder = GetDBPath_FX(, dbNameStr)

backUpFolder = backUpFolder & dbNameStr & " " & OBJFOLDER

' Back up all objects to text.

rebuildFile = dbNameStr & REBUILDOBJ

exportAllOK = exportObjectsToText_FX(backUpFolder, rebuildFile)

If exportAllOK Then

  MsgBox "Database objects have been exported to text files in " & backUpFolder & _

  ". These files can be recovered into a blank database using VB in the file " _

  & rebuildFile

Else

  MsgBox "Database export to " & backUpFolder & " was not successful"

End If

End Sub

Now we will look at the exportObjectsToText_FX subroutine in detail. Initially, the function creates a folder plus the instructions section of the VBA recovery file. You will be able to recognize the VBA code that creates the VBA recovery file by looking for lines that include the output channel variable io and the text file creation commands Open, Print, Close, and FreeFile. The first half of the subroutine follows:

 

Public Function exportObjectsToText_FX(folderPath As String, _

                rebuildFile As String) As Boolean

' Export all queries, forms, macros, and modules to text.

' Build a file to assist in recovery of the saved objects

' in a clean database.

' This function requires a reference to

' Microsoft DAO 3.6 or 3.51 Llibrary.

'Requires the modules basGR8_exportObjects and basGR8_Startup.

On Error GoTo err_exportObjectsToText

Dim dbs As DAO.Database, Cnt As DAO.Container, doc As DAO.Document

Dim mdl As Module, objName As String

Dim io As Integer, i As Integer, unloadOK As Integer

Dim FilePath As String

Dim fileType As String

If Len(Dir(folderPath, vbDirectory)) = 0 Then

 

  unloadOK = MsgBox("All tables will be backed up to a new directory called " & _

           folderPath, vbOKCancel, "Confirm the Creation of the Backup Directory")

  If unloadOK = vbOK Then

    MkDir folderPath

  Else

    GoTo Exit_exportObjectsToText

  End If

End If

' The location of all the text files should be in a folder that is

' backed up and kept off-site.

io = FreeFile

Open folderPath & rebuildFile For Output As io

Print #io, "public sub RebuildDatabase"

Print #io, ""

Print #io, "' Import this into a blank database and type"

Print #io, "' RebuildDatabase into the debug window"

Print #io, ""

Print #io, _

"msgbox ""This will OVERWRITE any objects with the same name. "" & _"

Print #io, _

"        ""WARNING: Press CTRL+BREAK NOW "" & _"

Print #io, _

"        ""If you already have these objects in your database "" & _"

Print #io, _

"        ""You will not be able to retrieve the current objects if you continue"""

Print #io, ""

The function must now iterate through the different collections of objects in the database by using DAO. When the loop moves to the next object, the object is saved to text and another line is written to the VBA recovery file. When it comes to exporting modules, I like to differentiate between modules and class modules by saving them to a different file type, which requires that I open the modules in design mode first. If you like, you can remove this additional code and save all class modules as .BAS files. This change will not affect the recovery process at all. Before I start this part of the subroutine, I find it useful to test whether the database has been compiled and then compile it as a final test of the quality of the database. The second half of the exportObjectsToText_FX follows.

 

If Not Application.IsCompiled Then

  ' If the application is not compiled, compile it.

  RunCommand acCmdCompileAllModules

End If

' Now test again whether the database is compiled.

' First, test whether the database is compiled.

If Application.IsCompiled Then

  Set dbs = CurrentDb()

  For i = 0 To dbs.QueryDefs.Count - 1

    objName = dbs.QueryDefs(i).Name

    FilePath = folderPath & objName & ".qry"

    If left(objName, 1) <> "~" Then

      SaveAsText acQuery, objName, FilePath

      Print #io, "LoadFromText acQuery,""" & objName & _

       """ , """ & FilePath & """"

    End If

   Next i

  Print #io, ""

  Set Cnt = dbs.Containers("Forms")

  For Each doc In Cnt.Documents

    FilePath = folderPath & doc.Name & ".frm"

    SaveAsText acForm, doc.Name, FilePath

    Print #io, "LoadFromText acForm,""" & doc.Name & _

     """ , """ & FilePath & """"

  Next doc

  Print #io, ""

  Set Cnt = dbs.Containers("Reports")

  For Each doc In Cnt.Documents

    FilePath = folderPath & doc.Name & ".rpt"

    SaveAsText acReport, doc.Name, FilePath

    Print #io, "LoadFromText acReport,""" & doc.Name & _

     """ , """ & FilePath & """"

  Next doc

  Print #io, ""

 

' Scripts are actually macros.

  Set Cnt = dbs.Containers("Scripts")

  For Each doc In Cnt.Documents

    FilePath = folderPath & doc.Name & ".mcr"

    SaveAsText acMacro, doc.Name, folderPath & doc.Name & ".mcr"

    Print #io, "LoadFromText acMacro,""" & doc.Name & _

     """ , """ & FilePath & """"

  Next doc

  Print #io, ""

  Set Cnt = dbs.Containers("Modules")

  For Each doc In Cnt.Documents

' Modules need to be opened to find if they are class or function modules.

' You can turn off open module to save all files as .BAS types.

    DoCmd.OpenModule doc.Name

    If Modules(doc.Name).Type = acClassModule Then

      fileType = ".cls"

    Else

      fileType = ".bas"

    End If

    FilePath = folderPath & doc.Name & fileType

    DoCmd.CLOSE acModule, doc.Name

    SaveAsText acModule, doc.Name, FilePath

    Print #io, "LoadFromText acModule ,""" & doc.Name & _

     """ , """ & FilePath & """"

  Next doc

  exportObjectsToText_FX = True

  Print #io, "msgbox ""End of rebuild"""

  Print #io, ""

  Print #io, "end sub"

Else

  MsgBox "Compile the database first to ensure the code is OK .", _

    vbInformation, "Choose Debug, Compile All Modules from the VBA window."

  exportObjectsToText_FX = False

End If

 

Exit_exportObjectsToText:

On Error Resume Next

  Close io

  Set doc = Nothing

  Set Cnt = Nothing

  Set dbs = Nothing

  Exit Function

err_exportObjectsToText:

  Select Case Err.Number          ' Problems with unload process.

    Case Else

        MsgBox "Error No. " & Err.Number & " -> " & Err.Description

  End Select

  exportObjectsToText_FX = False

  Resume Exit_exportObjectsToText

End Function

Now that you have seen how to import all the objects, let's have a look at how the file size of the text files will tell you the relative size of the objects in the database.

To show hidden members of objects such as the Application object, open the Object browser from the Visual Basic Editor. Right-click any object and choose Show Hidden Members from the menu.

 

Download