This month, Doug Steele looks at an undocumented feature within Access that allows you to save Access objects as text files.

Access allows you to save the code associated with a form or module as a text file. Is there a way to do this programmatically?

As it turns out, the answer is yes, but you need to use undocumented methods in the database. As I’ve mentioned in previous columns, there’s always a risk associated with using undocumented features, since they could be changed or removed from one version to another. If you’re willing to take that chance, though, you may find the following interesting.

Before I get into the descriptions of how to use the SaveAsText and LoadFromText methods of the Application object, it might be useful to explain how you can find more of these undocumented features. When you’re in the VB Editor, you can hit F2 to invoke the Object Browser. Once you’ve done that, right-click on the browser and select “Show Hidden Members” from the context menu that appears, as shown in Figure 1.

Figure 1

Figure 2 shows some of the methods and properties associated with the Access Application object without the hidden members being shown, while Figure 3 shows that same part of the list with hidden members being shown. As you can see, there are two new methods in Figure 3 (SaveAsText and SetUndoRecording).

Figure 2

Figure 3

Now, the problem with using undocumented features is that, um, they aren’t documented! (I know, that’s terrible.) What I’m trying to imply is that you can’t usually find instructions on how to use these features, so you have to play with them (and hopefully not break anything while doing so!).

Fortunately, the SaveAsText method (and the corresponding inverse method LoadFromText) are pretty straightforward to use. From the Object Browser, you’ll find that the declarations for the two methods are:

Sub SaveAsText(ObjectType As Long, _

  ObjectName As String, FileName As String)

Sub LoadFromText(ObjectType As Long, _

  ObjectName As String, FileName As String)

ObjectName and FileName should be obvious: the name of the object you want to save (or load), and the file to which you want to save (or from which you want to load). ObjectType uses the same ObjectType definition as elsewhere in Access (for instance, in the Close method of the DoCmd object). I’ve listed their names (and values) in Table 2.

Table 2. ObjectName values.

What needs to be done, then, is to determine the name of each of the various objects, and then use SaveAsText to save each object in turn. How you do this depends on what version of Access you’re using.

In Access 97 and earlier, you can easily determine all of the defined queries from the QueryDefs collection of the Database. However, the Forms, Reports, and Modules collections (contained in the Application object) only contain those forms, reports, or modules that are currently open, so it’s a bit more of a problem to determine all of the defined forms, reports, and modules in an application–there just isn’t a comparable collection for macros (also known as “scripts”) at all. Fortunately, there’s a Containers collection in the Database, and it contains Container objects that hold the details of, among other things, all of the saved forms, modules, reports, and macros in the database. Each Container object has a Documents collection, where the individual Document objects in the Documents collection describe instances of built-in objects of the type specified by the Container.

What does all that gobbledygook mean? To get a list of all of the forms in a database, you can use code such as this:

Dim dbCurr As DAO.Database

Dim cntCurr As DAO.Container

Dim docCurr As DAO.Document

  Set dbCurr = CurrentDb()

  Set cntCurr = dbCurr.Containers("Forms")

  For Each docCurr In cntCurr.Documents

    Debug.Print docCurr.Name

  Next docCurr

Table 3 shows the Containers of interest for this exercise. The following code will loop through each of the collections of interest and save the contents of each of the objects in a separate text file:

Dim dbCurr As DAO.Database

Dim cntCurr As DAO.Container

Dim docCurr As DAO.Document

Dim qdfCurr As DAO.Querydef

Dim strFolderToUse As String

  Set dbCurr = CurrentDb()

  strFolderToUse = dbCurr.Name

  strFolderToUse = Left$(strFolderToUse, _

    Len(strFolderToUse) - Len(Dir$(strFolderToUse)))

  Set cntCurr = dbCurr.Containers("Forms")

  For Each docCurr In cntCurr.Documents

   Application.SaveAsText acForm, docCurr.Name, _

      strFolderToUse & "Form_" & docCurr.Name & ".txt"

  Next docCurr

  Set cntCurr = dbCurr.Containers("Reports")

  For Each docCurr In cntCurr.Documents

   Application.SaveAsText acReport, docCurr.Name, _

    strFolderToUse & "Report_" & docCurr.Name & ".txt"

  Next docCurr

  Set cntCurr = dbCurr.Containers("Scripts")

  For Each docCurr In cntCurr.Documents

   Application.SaveAsText acMacro, docCurr.Name, _

    strFolderToUse & "Script_" & docCurr.Name & ".txt"

  Next docCurr

  Set cntCurr = dbCurr.Containers("Modules")

  For Each docCurr In cntCurr.Documents

   Application.SaveAsText acModule, docCurr.Name, _

    strFolderToUse & "Module_" & docCurr.Name & ".txt"

  Next docCurr

  For Each qdfCurr In dbCurr.QueryDefs

   Application.SaveAsText acQuery, qdfCurr.Name, _

     strFolderToUse & "Query_" & qdfCurr.Name & ".txt"

  Next qdfCurr

Table 3. Relevant Container names.

With Access 2000 and newer, it’s a little bit easier. A new object was added to the Application object model with Access 2000, CurrentProject, and that object contains collections named AllForms, AllMacros, AllModules, and AllReports. Using these new collections, for instance, you can get all of the forms in your application as:

Dim dbsCurr As CurrentProject

Dim objAccess As AccessObject

  Set dbsCurr = Application.CurrentProject

  For Each objAccess In dbsCurr.AllModules

    Debug.Print objAccess.Name

  Next objAccess

There’s also a CurrentData object in the Application object that contains an AllQueries collection. Armed with these new objects and collection, the earlier Containers-based code can be rewritten as:

Sub ListObjects()

Dim dbsCurr As Object

Dim objAccess As AccessObject

Dim strFolderToUse As String

Set dbsCurr = Application.CurrentProject

strFolderToUse = dbsCurr.Path

For Each objAccess In dbsCurr.AllForms

 Application.SaveAsText acForm, objAccess.Name, _

   strFolderToUse & "\Form_" & objAccess.Name & ".txt"

Next objAccess

For Each objAccess In dbsCurr.AllReports

 Application.SaveAsText acReport, objAccess.Name, _

  strFolderToUse & _

  "\Report_" & objAccess.Name & ".txt"

Next objAccess

For Each objAccess In dbsCurr.AllMacros

 Application.SaveAsText acMacro, objAccess.Name, _

   strFolderToUse & _

   "\Script_" & objAccess.Name & ".txt"

Next objAccess

For Each objAccess In dbsCurr.AllModules

 Application.SaveAsText acModule, objAccess.Name, _

   strFolderToUse & _

   "\Module_" & objAccess.Name & ".txt"

Next objAccess

Set dbsCurr = Nothing

Set dbsCurr = Application.CurrentData

For Each objAccess In dbsCurr.AllQueries

 Application.SaveAsText acQuery, objAccess.Name, _

   strFolderToUse & _

   "\Query_" & objAccess.Name & ".txt"

Next objAccess

End Sub

Now, the preceding examples only dump the details of the current database out to file. It’s not that hard, though, to rewrite the code so that it operates on an external database instead. If you’re interested, check out the downloadable databases that accompany this column (it was a bit too much code to try and fit in my monthly space allotment!).

For the sake of completeness, I’ll touch briefly on using the reciprocal LoadFromText routine. If you know that the text file created by exporting a specific form is at a location specified in variable strFileLocation, you can import it into the current database using code like this:

Application.LoadFromText acForm, _

  "MyNewFormName", _

  strFileLocation

In the accompanying downloadable databases, I show how you can loop through all of the saved files and import them into a new database.

Yes, this is a rather esoteric capability, without a whole lot of real need. You could use it to make global changes to your database, or to do Where Used analysis, but there are, of course, other tools for that. I did have one poster in the newsgroups who used this functionality to be able to create macros dynamically (knowing what the text file needed to look like, he’d write out a text file and use LoadFromText to import it into the database). However, as I told the poster at the time, if you’re going to go to that trouble, why not use VBA code instead of macros?

The other part of this Access Answers column appears here
Give Me a Call