Exporting and Recovering Programming Objects | Real World Microsoft Access Database Protection and Security

In this section, 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 you're adding protection to the database, such as passwords and user -level security.

In all these scenarios, backing up the programming objects at regular intervals may help recover the object. Now I will describe how to save VBA code by using a menu.

Exporting a VBA Module to a Text File

Your VBA modules and class modules are the only objects in the database for which you can use a menu to save as text and recover from text.

Open the Database window (of any database with code).
Select Modules.
Select an individual module.
Choose File ˜ Export.
Save the file as a text (*.txt).
Select your folder and file name .
Select the Autostart check box and click Export.

Your VBA module will now be saved in a text file. In Access 2000 and later, you can also export modules from within the Visual Basic Editor. First, you need to select the object that you want to export in the Project Explorer, then you choose File ˜ Export, and the steps are similar to the preceding procedure. If you use the Visual Basic Editor to save the code, you can use more specific file types like *.BAS and *.CLS rather than *.TXT.

Importing the VBA Text File Back into the Database

In Access 97, the only way to import the VBA text file back into the database is to open the text file in a text editor like Notepad, select all, copy to the paste buffer, open a new module in Access, and paste the code into the new module. You can use this simple method in all versions of Access.

In Access 2000 and later, however, you can also import the VBA text file directly into the database by following these steps:

Open a new database.
Open the Visual Basic Editor (press ALT+F11).
Choose File ˜ Import File.
Find the file and click Open.
You will now find the module in the Project Explorer. If it is not visible, choose View ˜ Project Explorer.

The next section of this chapter will show you how to use the same formats that Microsoft Visual SourceSafe uses to save and recover all the programming objects in your database.

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 ^[1] 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 5-10, I have put together an example to show how you can export and import a form.

Figure 5-10: 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 exporting of all tables to text example described earlier in this chapter.

To experiment with this download, do the following:

Make a copy of Northwind.mdb and then open the copy.
Import the following objects from the demonstration database: frmBackupAllObjects , basGR8_exportObjects , and basGR8_Startup .
Compile all the code by using the Debug menu in the Visual Basic Editor.
Open the form frmBackupAllObjects (shown in Figure 5-11) and click the Back Up All Objects to Text button to start the backups .

Figure 5-11: The Back Up All Objects to Text form.
When the exports are completed, a message box (as shown in Figure 5-12) 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 5-12: The message that appears to tell you where the files went and how to recover them.

Tip	Before exporting a database or shipping it to clients , for that matter, it is wise to compile all modules in the database. To do this, open the Visual Basic Editor and choose Debug ˜ Compile Project for Access 2000 and later and Debug ˜ Compile All Objects for Access 97.

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 5-13). 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 5-13: 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-14).

Figure 5-14: 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 such as Northwind would be very tedious . To automate this process, the Export All Objects software automatically generates a text file called Northwind_rebuildBas.txt (shown in Figure 5-15). This file contains VBA code that will load all the objects into a blank database.

Figure 5-15: 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:

Open a new blank database.
Open the Visual Basic Editor (press ALT+F11).
Choose File ˜ Import File.
Find the file (its name should be Northwind_rebuildBas.txt) and click Open.
Find the module in the Project Explorer, which you can view by choosing View ˜ Project Explorer.
Open the Immediate window.
Type "call RebuildDatabase" into the Immediate window.
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\Northwind BackupObjects\Northwind_rebuildBas.txt"

Caution

The LoadFromText method will copy over the existing objects without warning. If you are using this method, you probably should open a new blank database and then compare the object with your existing database before importing.

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.

^[1] 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.