FMS, Inc. is a group out of Vienna, Virginia who really understands the needs of Access developers. There's a good chance that you're already familiar with its flagship product - the award winning Total Access Analyzer (which was reviewed in the February 1995 issue). Once again, they've hit the target with Total Access Code Tools, a Swiss Army knife for serious developers using Access 2.0.
Code Tools is designed to assist in the creation, development, maintenance, and delivery of Module objects. It loads as an add-in and when run, a toolbar comes up allowing you to launch the various Code Tools builders and utilities. There are the three tools that make this product a must have for any Access developer:
Code Tools includes the following tools not covered here, but nonetheless can save you loads of time:
You start CodeTools by selecting it from the File|Add-ins menu. The first time you run Code Tools you're asked to fill out registration information. This is a one time detour. Subsequent launchings of Code Tools open the Code Tools toolbar (figure 1).
The tool bar may seem large, especially in 640x480 resolution. The shrink button can be used to remove the right four buttons from display. FMS has done a fine job of making the Code Tools toolbar as small as possible. To further accommodate your workspace preferences, you can display this toolbar vertically or horizontally (figure 1).
As an alternative, you can open this form on-demand by calling its entry-point function TACTools_Start() from an AutoKeys macro. While this doesn't change the size of the toolbar, it may make it more convenient to open and close as needed.
You can use the Procedure Builder (figure 2) in one of two ways: to create your subprocedure or function, or to generate the body structure and place it into an existing procedure.
After your procedure is generated, you can copy the code to the clipboard and paste it into your module. Recognizing that you may want to use this code in an event procedure, FMS thoughtfully provided a Copy Body to Clipboard option, which copies only the code between the procedure Declaration and End procedure lines. This is useful for adding the code to existing global procedures, although you can accomplish the same task using the Code Cleanup Utility.
The Options button extends the Procedure Builder significantly, by giving you the opportunity to customize the procedure header and error handling text structure. Special characters are used to identify user name, date/time, along with procedure and module name information. A few screen shots can be speak more loudly than a written description. Figure 3 shows how you might customize the Procedure Comment (Header) text.
Figure 4 shows how one might customize the Error Handling options. Since all the text boxes in these options are scrollable, you can write a comprehensive error logging process and have it automatically become part of your procedure. FMS has provided sample code in a text file, which you ca use to log errors to a text file for review at a later date.
The example in figure 4 shows a label for Exit, which I added before snapping the screen shot. The template does not include this label, and defaults to a Resume Next in the actual error handler. There are other numerous possibilities. I talked with an FMS developer, and he was excited about the possibility of benchmarking procedure execution speeds, recording procedure calls, and a host of other actions that could be triggered at the start and end of a procedure which aren't directly related to error handling. The point is, the Error Handling section of the Procedure Builder always writes to the top and bottom of a procedure, making developer intervention as easy as running the Procedure Builder or Code Clean-up on a test version of your application.
The Procedure Builder is exceptionally functional, and justifies much of the cost of Total Access Code Tools. There are additional option settings for establishing Procedure Naming Conventions and Global Procedure Identifiers.
The Code Cleanup Utility is a superset of the Procedure Builder and more. There are three ways you might use Code Cleanup:
After your application is written and tested, you can run Code Cleanup, but first you must perform two key steps:
Once you've accomplished this, you should run Code Cleanup with just the Option Explicit setting on (figure 5). This ensures the Option Explicit is added to all modules, including the private ones belonging to each form and report. If any Option Explicit statements are added, it's possible your code won't compile; therefore, you should (read that as "must") repeat steps 1 and 2 above.
The Error Handling and Procedure Comments Options work as with the Procedure Builder. Once again, you can modify the header text to fit your programming style and conventions. You can also have Code Cleanup apply indentations, module comments, and variable naming conventions to your code. The naming conventions can be edited to whatever you like' however, since they default to the L/R Conventions, you'll probably not make many changes to the defaults. When you press OK, Code Cleanup takes over applying your specifications to all modules in your database.
Once Cleanup is completed, you'll be left with model code suitable for documentation (no doubt using Total Access Analyzer) as in this example:
Function DivByZero (Number as Variant, Denom As Variant, Retval as Variant) As Variant Dim Result As Variant Result = RetValue If Not IsNull (Numer) And Not IsNull(Denom) Then If IsNumeric(Numer) And IsNumeric(Denom) Then If Denom <> 0 Then Result = Numer / Denom End If End If End If DivByZero = Result End Function
After running Code Cleanup, this code will look like the following:
Function DivByZero (varNumber as Variant, varDenom As Variant, varRetval as Variant) As Variant ' Comments : ' Parameters : varNumer ' varDenom ' varRetval ' Returns : Variant ' Created By : Doug Silver - PDS Consulting ' : 05/20/96 ' Modified : ' ----------------------------------------- On Error GoTo DivByZero_Err: Dim varResult As Variant VarResult = RetValue If Not IsNull(varNumer) And Not IsNull(varDenom) Then If IsNumeric(varNumer) And IsNumeric(varDenom Then If var Denom <> Then varResult = varNumer / varDenom End If End If End If DivByZero = varResult DivByZero_Exit: Exit Function DivByZero_Err: MsgBox "The following error occurred: " & Error$ Resume DivByZero_Exit End Function
All that's left is to move the line DivByZero = varResult so that it follows the Exit label, and then to fill out comments as appropriate. Code Cleanup enters the error handling that I specified, module comments, code indentations, and even prefixes variables appropriately. This example is trivial; you should see Code Cleanup do its thing on poorly written code!
This is the most ambitious and stunning element of the Code Tools, and alone is worth the cost of the entire package. Code Delivery operates under these assumptions:
Code Delivery can remove white space, and indent and outdent code and comments. Code Delivery can also substitute alpha-numeric names for all constants and variables in your code, making it less readable. Finally, Code Delivery can add line numbers in all your procedures, making it easier to track down the offending line when an error is encountered (if you have an error logging process, modify it to record the line number).
This code has been through both Code Cleanup and my own additions of comments and whitespace:
Function DivByZero (varNumer As Variant, varDenom As Variant, varReturn As Variant) As Variant ' Comments : perform division to protect from Divide BY Zero error. ' If non-numeric or Null values or if the Denominator is Zero then return the value specified by incoming parameter varReturn. ' Parameters : varNumer - Numerator ' varDenom - Denominator ' varReturn - Return value to supply on ' failure or error ' Returns : Variant ' Created : 05/22/96-Doug Silver-PDS Consulting ' Modified : ' --------------------------------------------- On Error GoTo DivByZero_Err: Dim varResult As Variant 'Initialize the return value varResult = varReturn 'Test for numeric, non-null values and non-zero denominator If Not IsNull(varNumer) And Not IsNull (varDenom) Then If IsNumeric (varNumer) And IsNumeric (varDenom) Then If varDenom <> 0 Then varResult = varNumer / varDenom End If End If End If DivByZero_Exit: DivByZero = varResult Exit Function DivByZero_Err: MsgBox "The following error occurred: " & Error$ Resume DivByZero_Exit End Function
After running Code Delivery, it looks like this:
Function DivByZero (V2 As Variant, V1 As Variant, V4 As Variant) As Variant 100 On Error GoTo DivByZero_Err: 110 Dim V3 As Variant 120 V3 = V4 130 If Not IsNull(V2) And Not IsNull(V1) Then 140 If IsNumeric(V2) And IsNumeric(V1) Then 150 If V1 <> 0 Then 160 V3 = V2 / V1 170 End If 180 End If 190 End If DivByZero_Err: 220 MsgBox "The following error occurred: " & Error$ 230 Resume DivByZero_Exit End Function
Notice a significant difference in size and readability. The Divide by Zero function is rather trivial. I ran Code Delivery against a freshly compacted 2.3MB database. This database uses attached tables, so the size is due primarily to all the other database objects. After running Code Delivery with line numbering set to yes, I again compacted my database. The result is a database of just more than 2MB: a reduction in size of more than 200K! This suggests that more than 10 percent of my database size is due to white space, the length of variable names, and comment lines. I have no doubt that performance will be enhanced after Code Delivery.
The Code Tools Manual is similar to other recent FMS product manuals in that it's written in a clear, concise, and informative manner. You can probably operate this product without reading the manual, but you'd be doing yourself a disservice. The manual comes with a discussion of an enhanced error handler for logging errors to a text file. The code for this is in a text file you can load to a module. Then, all you have to do is make a couple of modifications to the error-handler section of the Procedure Builder or Code Cleanup, and you'll have error logging application wide. A read-me file comes on the product disk and contains corrections and clarifications for some of the topics in the manual.
Total Access Code Tools delivers the tools necessary to produce high quality Access Applications with a maximum of consistence and minimum of tedious labor. Using them makes the less experienced developer produce more professional code, and helps the more experienced developer be more productive. With the addition of a little code, the really creative developer can use Code Cleanup on a pre-delivery version to benchmark procedure call frequency and execution time, and determine what processes should be reviewed for optimization.
There are a few items I'd like to see done differently, but to be honest, it's a matter of taste rather than of importance. One particular option I'd like is an entry-point function to each of the individual builders and utilities. This way, I could bypass the toolbar and call these functions directly from and AutoKeys macro.
I've written builders that do many of the same actions performed by Total Access Code Tools, and I always felt they gave me an edge over other application developers. Without having to take the same route I did, Total Access Code Tools helps to raise programmer productivity and improve the overall quality of your code.
Doug Silver is the presidnet of PDS Consulting, Inc,. a Minneapolis Minnesota, custom application development company specializing in Access, Visual Basic, and Office. Doug is a trainer for Application Developers Training Company and leads the Access Developer User Groups in the Twin Cities.
Supports Office/Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and Visual Basic 6.0!
Also available for
"Total Visual CodeTools is by far my favorite third-party product."
Alison Balter, Author, Conference Speaker, Instructor
Best Visual Basic Add-In