A Couple of Gems From FMS: Total Access Agent and Total Access CodeTools

September 1996, Smart Access, Paul Litwin

Total Access Agent is a useful utility for the Access or VB developer looking to ensure that database backups, compacts, and other critical operations are carried out on a regular basis. Total Access CodeTools is a set of 10 tools that take some of the tedium out of Access Basic coding. Both products are highly recommended.

FMS recently introduced two new utilities for the Access market: Total Access (TA) Agent and Total Access CodeTools. These two products are just the newest of a long line of Access tools from FMS, many of which have been favorably reviewed in these pages. TA Agent, which allows you to schedule Access databases (any version) for unattended backup, compacting, and other operations, can be used by both Access and VB developers. TA CodeTools is an Access 2.0 (only) add-in that provides 10 tools to assist in Access Basic coding. In this article, I'll review each of these products, beginning with TA Agent.

Total Access Agent

When you buy TA Agent, you get both 16-bit and 32-bit versions of the program. If you're using Windows 3.x, install the 16-bit version; otherwise, install the 32-bit version irrespective of the version of Access you're using. The 16-bit supports Access 1.x and 2.x databases; the 32-bit program supports Access 1.x, 2.x, and 7.0 databases.

TA Agent actually consists of two VB programs: Manager and Monitor. Manager is the program you use to schedule events, while Monitor executes the events you've scheduled.

When you first start TA Agent Manager, the Configuration Options dialog box appears:

Figure 1: You can use the Configuration Options dialog box to set various options for TA Agent, including the location of Access executable and .INI files.

You use this dialog to tell TA Agent where to find your Access executable and .INI files and, if your databases are secured, the user name and password under which TA Agent is to log in. (These settings are stored in a secured table in the TAAGNT16 or TAAGNT32 databases.) You can also use this dialog box to specify the default location of the archive files and the e-mail settings to use when sending alerts.

Once the configuration options are in place, you're ready to schedule events using the Scheduled Events screen:

Figure 2: Use this screen to manage event schedules. You can create events that occur on an hourly, daily, weekly, or monthly basis.

Initially, four (disabled) events appear in the events list box, but you're free to add as many new events as you'd like. For each event, you must choose a schedule (hourly, daily, weekly, or monthly), and starting and ending times appropriate for the chose schedule. Next, you're ready to configure the event.

Details, details

Pressing the Configure button takes you to the Event Group Details screen:

Figure 3: Each event can have one or more actions.

where you specify one or more actions to occur for the event. TA Agent ships with four canned actions:

• Compact/repair database.
• Archive database.
• Database statistics.
• Archive table data.

The archive database and archive table data actions will optionally compress the database using a PKZip-compatible algorithm. In addition, you can specify that TA Agent save one to 99 different versions of the archive database. The advantage of using the archive table data action is that, unlike the archive database action, it can be scheduled to archive the data in one or more tables in the database while users have the database open. This is a great feature!

In addition to these canned actions, you can select "Custom Command" to execute a custom program or batch file, or "Execute Named Macro" to open Access and run a macro. You might use the Custom Command option to run a program that downloads data from a mainframe. You could use the Execute Named Macro option to run a lengthy report after hours.

With your schedule set, the only thing left to do is run the schedule. This is where the TA Agent Monitor program comes in -- place it in your startup group and it will run quietly in the background, making sure your schedule of events is kept. Periodically you can review the event log or database statistics using TA Agent Manager. The excellent user manual also tells you how to create Access reports that draw data from the log or statistic tables that TA Agent keeps for you.

OLE automation capabilities

While the ability to use the TA Agent Monitor program to maintain and execute your schedule is a great feature, the fact that TA Agent packages and logs several useful actions is a plus in and of itself. In creating TA Agent, FMS realized that you might want to use this latter capability without using the Monitor program. Thus, FMS includes an OLE automation interface to the TA Agent engine. This allows you, for example, to call the nicely packaged "archive table data" routine from your own code before executing a potentially destructive delete query. The OLE Automation interface to TA Agent rounds out the product and makes it clear that FMS really understands the Access/VB developer.

Total Access CodeTools

Unlike TA Agent, TA CodeTools works only with Access 2.0. After using TA CodeTools' setup program to install the add-in, you select File|Add-ins|Total Access CodeTools to display the add-ins floating toolbar:

Figure 4: Each toolbar button runs a different tool. Some tools, like the procedure builder, generate code; others, like the code cleanup tool, work on existing code.

The TA CodeTools toolbar contains 10 buttons, which run the following tools:

• Procedure builder
• Open all modules tool
• SQL builder
• Long text builder
• Message box builder
• Select case builder
• Code cleanup tool
• Code delivery tool
• List-filling function builder
• Constants generator

Generating Code

Six of the 10 tools that make up TA CodeTools generate pieces of code that can take some of the tedium out of Access Basic programming. Probably the most useful of the six is the procedure builder:

Figure 5: Let the TA CodeTools Procedure Builder add a standard header and error handler to all your procedures.

The builder includes a default template with a serviceable header and error handler, but if you're like most developers (me included), you'll likely want to customize the template to suit your coding style. You can use special characters to indicate to TA CodeTools where to substitute things like the procedure name, the module name, and the current date. Once you've created your template, click on the Proc Builder toolbar button, enter the name of the procedure, whether it's a sub or a function and, if the latter, its return data type, and click on "Paste to Module" to create the shell for your new procedure. For example, here's the shell it created for a function called CreateFooBar using my custom template:

Private Function CreateFooBar () As Integer
    ' In:
    ' Out:
    ' History:
    ' Created: 06/11/96 6:41:41 PM
    ' Modified:

    On Error GoTo CreateFooBarErr
    Dim strMsg As String

CreateFooBarDone:
    Exit Function

CreateFooBarErr:
    Select Case Err
    Case Else
        StrMsg = "Error" & Err & ": " & Error$
    End Select
    MsgBox strMsg, MB_OK + MB_ICONSTOP, "Procedure CreateFooBar"
    Resume CreateFooBarDone
End Function

Pretty impressive! Because Access automatically creates the Sub and End Sub statements for event procedures, the procedure builder includes a button labeled "Copy body To Clipboard" (see Figure 5), which you can use to copy every thing but the Access-generated part of the procedure into your code.

The SQL builder creates a readable string variable from the SQL of a saved query. For example, it will create output like this for Category query from NWIND.MDB:

strSQL = "SELECT DISTINCTROW Categories."
StrSQL = strSQL & "[Category Name], Products."
StrSQL = strSQL & "[Product Name], Categories." 
StrSQL = strSQL & "Description, Categories."
StrSQL = strSQL & "Picture, Products.[Product ID]," 
StrSQL = strSQL & " Products.[Quantity Per Unit], " 
StrSQL = strSQL & "Products [Unit Price],"
StrSQL = strSQL & "Products.Discontinued FROM"
StrSQL = strSQL & "Categories INNE JOIN Products" 
StrSQL = strSQL & "ON Categories.[Category ID] =" 
StrSQL = strSQL & "Products.[Category ID] WHERE" 
StrSQL = strSQL & "((Products.Discontinued=No))" 
StrSQL = strSQL & " ORDER BY Categories."
StrSQL = strSQL & "[Category Name], Products."
StrSQL = strSQL & "[Product Name]; "

The other builders perform similar wonders, generating long string variables, MsgBox statements, Select…Case statements, and list-filling functions.

Code cleanup and delivery

The TA CodeTools Code Cleanup tool opens all your modules and applies several transformations to clean up your code and make it more consistent. You can use all or some of these cleanup options:

• Add Option Explicit to every module.
• Add a standard header to every procedure.
• Add a standard header to every module.
• Add standard error handling code to every procedure.
• Apply consistent indentation to code.
• Apply standard naming conventions to code.

The procedure header and error handler options are similar to the ones used by the procedure builder. In this case, they're applied to existing procedures. I was surprised, however, that I had to re-create my custom header and error handler in both places-there was no way to tell TA CodeTools to use the procedure builder's template here. On the other hand, I was happy to see that the default naming convention tags followed the Reddick naming conventions (see the February 1996 issue of Smart Access ), but these settings like most others are completely customizable:

Figure 6: The TA CodeTools Code Cleanup tool's naming conventions are completely customizable.

Easily the oddest tool in the batch, the Code Delivery tool is the antithesis of the Code Cleanup tool - it takes perfectly readable code and obfuscates it. Why? To make your code run faster and to make it harder to rip off by eliminating such niceties as comments, debug code, indentation, and descriptive variable names. The result of this exercise in code deception looks something like this (applied to the IsLoaded function from NWIND.MDB):

Function IsLoaded (V68)
100 Dim V82 As String
110 Dim V93
120 IsLoaded = False
130 For V93 = 0 To Forms.Count - 1
140 If Forms(V93).FormName = V68 Then
150 IsLoaded = True
160 Exit Function
170 End If
180 Next
End Function

Of course, this obfuscated code sample is more readable than some code I've inherited, but at least it's a start!

Because the code cleanup and delivery tools are potentially dangerous and can easily break working code, backup your database before proceeding. This is especially important if you use the delivery tool, because there's no easy way to clean up the mess it creates.

Other Tools

Performing global search and replace operations in your code is a hassle in Access 2. While Access will automatically search all global modules, it only looks in form and report modules that are already loaded. Fortunately, you can use TA CodeTools's Open All Modules tool to open every module for you.

The last tool, the TA CodeTools Constants Generator, scours your code for hard-coded object references, replacing them with object constant. For example, the constants generator takes code like this:

Set rst = db.OpenRecordset("contblClients")

And replaces it with this:

Const contblClients = "tblClients"
Set rst = db.OpenRecorset(contblClients)

Recommendations

TA Agent is an excellent utility for the Access or VB developer looking to ensure that database backups, compacts, and other critical operations are carried out on a regular basis. While you could probably write a VB or Access application that duplicates some or all of TA Agent's functionality, why bother when FMS has already done the wok for you.

TA CodeTools is another strong developer product, although currently available only for Access 2.0. While many of the tools in TA CodeTools are unremarkable in themselves - in fact prior Smart Access Developer's Disks have featured free utilities that perform many of the same functions - when you bundle them together in a single package, you have a handy set of tools. In addition two of the tools are indeed remarkable-you won't find utilities that clean up and obfuscate your Access Basic code like this anywhere else. It's hard to imagine many Access 2.0 developers who couldn't benefit from this compendium of coding tools.

TA Agent and TA CodeTools are proof that FMS understands the Access developer. They represent two in a long line of great products from an outstanding Access tools vendor. I look forward to their next product!

Paul Litwin, the editor of Smart Access, co-authored two Access 95 books: Microsoft Access 95 Developer's Handbook (Sybex) and Microsoft Access 95 How-To (Waite Group Press). Fax 206-281-1933.

© Smart Access September 1996. Reprinted with permission from © Smart Access. All rights reserved.

Additional Awards, Reviews, and Customer Testimonials for Total Visual Agent