September 1996, Smart Access, pp. 6-9.
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.
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.
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
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
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.
http://www.mcwtech.com.
© Smart Access September 1996. Reprinted with permission from ©
Smart Access. All rights reserved.
Back to Main Reviews
Page
|
Email Newsletter
Developer Team Blog
Facebook
Twitter
