The Extras Add-in for Backup and Field Listing

A look at the Extras Add-in which combines the functionality of backup and field listing.

Access Archon #155

Introduction

In previous articles, I described a set of objects you could import into any Access database to do database backup (Access Archon #151) or list tables and queries and their fields (Access Archon #150).  After importing the objects into a database, you also have to set the appropriate reference(s) before doing backup or field listing.  Having to do the import and reference setting for every database where you want to use these features is a nuisance; it would be much more convenient to just have backup and field listing available in all your Access databases, say from a menu command.

An Access add-in will do just that, encapsulating a set of database objects and code into a single package that is available to all Access databases.  In this article, I describe the Extras add-in (Extras.mda), which combines the functionality of backup and field listing; this add-in runs in any Access version from 2002 (XP) to 2007.  The add-in includes the database backup code from my earlier Backup database (modified as needed to run from an add-in), with a new setup form for specifying the backup folder.  It also includes a set of objects and code (based on those in my ListTablesAndFields database) that let you print out lists of tables or queries, and their fields, excluding those with user-specified prefixes—very handy for when you need to know which fields are in which tables during database development, or for documenting the database structure.

This add-in (and a special Access 2007-only version of it that supports the new Office 2007 Ribbon) is featured in my upcoming book, Access™ 2007 VBA Bible For Data-Centric Microsoft® Applications (Wiley).  (I have been busy finishing up this book for the last month or so, which is why there has been a gap in Access Watch issues.)  For full information on the structure and function of Access add-ins, see Chapter 14 of this book.

To use the backup and field listing features, all you need to do is copy the add-in (Extras.mda) to your Addins folder (usually C:Documents and SettingsUser NameApplication DataMicrosoftAddIns).  The next time you open any Access database, you should see a set of new menu add-ins on the Add-ins menu, as shown in Figure A.

Figure A.  The Add-ins menu with new commands from the Extras add-in

Selecting the Extras Options item opens the dialog shown in Figure B:

Figure B.  The Extras Options dialog

In the top section of the dialog, you have three options for setting the folder where database backups will be stored:  The same folder as the database, a Backups folder under the database folder (this is the default setting), and a custom path, selected from a FolderPicker dialog opened from a command button.  The FolderPicker dialog is one of the options for a FileDialog object; this object was introduced in Office XP, which is why the add-in library database is in Access 2002/2003 format.

The bottom section of the dialog has two datasheet subforms, with lists of prefixes for tables (or queries) that should be excluded from the listings of table and query fields.  The datasheets are already filled in with some standard prefixes to exclude; you can edit the lists as desired, to exclude any objects you don’t need to see in the lists.

You don’t need to select anything on this dialog; if you don’t make any selections, the default backup path (Backups folder under the database folder) will be used, and the default exclusion prefixes.

The backup path you selected is used when you back up the database; when you select the Back up Database item, you will get the InputBox shown in Figure C, where you can edit the proposed save name of the database copy as desired:

Figure C.  An InputBox with a proposed database copy save name

The Back up Database Back End selection works similarly, except that it backs up the back end database (if there is one).  The List Query Fields and List Table Fields selections respectively create a table filled with the names of select queries and their fields, or tables and their fields, excluding those with prefixes listed in the exclusion tables.  Figure D shows the list of tables and their fields, with a message box asking if you wish to print the report now:

Figure D.  The table of table and field names

The report based on this table gives the description as well as the numeric data type for each field, and lists the fields alphabetically for each table (or query), as shown in Figure E.

Figure E.  The Table and Field Names report

You can print the report as a handy reference; however, in some cases the table itself (zstblTableAndFieldNames or zstblQueryAndFieldNames) may be more useful, for example, if you need to copy field names from one table to another.

For a discussion of the add-in’s code, see Chapter 14 of my upcoming book.

References

The code in the add-in library database has the following references; they have already been set, so you don’t need to set any extra references in your databases:

• Microsoft DAO 3.6 Object Library
  
• Microsoft Scripting Runtime
  
• Microsoft Office 11.0 Object Library

Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch155.zip, which is the last entry in the table of Access Archon columns for Access Watch.