Save Yourself Some Work

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:

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.

Constant Value
acForm 2
acMacro 4
acModule 5
acQuery 1
acReport 3

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:

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:

Table 3. Relevant Container names.

Container name Contents
Forms Saved form
Scripts Saved macro
Modules Saved module
Reports Saved report

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:

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:

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:

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?

 

Your download file is called Steele_AA200502.accdb

 

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

This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.