When was the last time you gave thought to the naming conventions you use? Do all of your methods contain regular error handling code? What about "flower box" comment blocks in method headers? If you've never been good about coding standards, or if your good intentions are sometimes sacrificed when you're working in "crunch mode", then you owe it to yourself to check out a great set of coding tools from FMS called Total Visual Code Tools 2002.
If memory serves me, it was VBITS San Francisco - 2001 where I heard a VB old-timer talk about how he and his fellow trail-blazers unwittingly set coding standards for future generations of developers. Their personal preference for naming conventions and code formatting became the de facto standard for self-taught newbies who read their books and magazine articles. Now, I don't mean to imply that there's anything wrong with their example, but by the same token, there's nothing wrong with determining and applying your own standards. Really, that's what Total Visual Code Tools 2002 is all about -- taking control of your code, applying your standards and simplifying the process is the crux of this FMS tool.
In fact, I used to be one of those self-taught newbies. I clearly remember studying the code generated by Access wizards and emulating it in the methods that I wrote. For example, the Access 97 wizard generated this code for running Excel from a button click in Access:
Private Sub cmdRunExcel_Click()
On Error GoTo Err_cmdRunExcel_Click
Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
Notice that the On Error instruction is the first line of code and is not indented, while the variable declaration follows and is indented. To this day, that is my style. This example also demonstrates the wizard's naming convention in that the Excel generic object is prefixed with the letter "o". Not so strangely, my typical error handler looks pretty much like what you see above, too. "Not that there's anything wrong with that," as Jerry Seinfeld once said, but maybe it's time for a change.
What if you had the power to revisit all your existing code, reformatting it and standardizing naming conventions, comments and error handling? Would you also like a set of easy to implement procedure templates for writing the shell for tediously similar code? How about string manipulation tools for parsing long strings, such as SQL Statements, into a more readable format for embedding in modules? All this and more is available in Total Visual Code Tools 2002.
If you have the developer version of Access 2000 or 2002, then you have probably seen the Microsoft equivalents, such as the String Editor and Code Commenter dialog boxs. In fact, even the standard version of Access has always included the Add Procedure dialog box, available from the Insert menu. These tools are a bonus, but they are primitive compared to what FMS delivers. Consider, for example, the difference between creating a custom property using FMS New Property Builder versus the Microsoft Add Procedure dialog. In Figure 1 it's clear that you have much more control over the output when using Total Visual Code Tools. Since space is limited, I won't post the generated code, but suffice it to say that while the Microsoft dialog produced four lines of code, the FMS tool generated 37 lines, with comments and error handling. Enough said.
Once again, space is limited, so I can't illustrate all the differences, but you would see a similar outcome if you compared the Microsoft String Editor with the FMS Text/SQL Builder or the various code commenting tools. You get an idea of the comprehensive nature of this utility when you look at the Standards option dialog, as shown in Figure 2. Just about everything you could imagine is configurable: comment location, indent and the text itself.
As can be seen in Figure 2, you can also set standards for error handling code and naming conventions. For example, you may prefer to prefix strings with an "s" instead of the common "str". Once you settle on standards, they will be used when you run the tools for inserting new code templates and when running cleanup against existing code. Your standards can then be saved off to a file and shared with team members also using FMS Total Visual Code Tools, so that all developer generated code will have a similar look and feel.
While it was great of the Microsoft Access development team to include the native tools, it's clear that they left the door open for companies like FMS to assist the developer community with some cool utilities. In addition to what's described above, Total Visual Code Tools 2002 includes utilities to insert code for message boxes, select case statements and the complete code to return an ADO or DAO recordset. Is it rocket science? No, but it sure is convenient. For example, check out this code generated by the ADO Recordset Builder:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * " & _
cnn.Open "DSN=TM;APP=Microsoft Office XP;" & _
rst.Open strSQL, cnn
Do While Not rst.EOF
Set rst = Nothing
Set cnn = Nothing
As I said before, all that is nice, but I'd have to say that the pihce de risistance would have to be the Code Delivery utility. The idea is simple, and for the most part, two-fold. First, by adding line numbers you enable line identification in error messages. When users report an error, you can more easily locate the exact entry point of the error. Of course, you can add line numbers by hand, but if you choose to do that without some sort of automation tool, it's a pretty strong indication that you've been coding too long and you need to get a life. This feature alone could pay for Total Visual Code Tools 2002 by speeding up and simplifying the debugging of errors in production code.
The other reason for using the Code Delivery utility is to obfuscate your code. There are some instances where you can't simply lock your database by converting it into an MDE. For example, I once created an Access Add-In for distribution and tried to lock its code. Unfortunately, after compiling it as an MDE, some of the functionality was disabled. I ended up delivering the Add-In with the code completely exposed. Though the Code Delivery tool doesn't lock your modules, it does convert readable code into script that is difficult to follow (See Figure 3).
You'll notice from Figure 3 that the Code Delivery tool removes comments and debug code and scrambles variable names. Blank lines are now gone, as are all indents. It also removes all Stop statements and debug code. For those of you who want to leave some comments in your code, FMS included a way to prefix comments with an escape character that will allow comments to remain. Powerful and flexible, like the rest of Total Visual Code Tools 2002.
Code Tools to the Rescue
If you haven't already figured it out, I'm a fan of this product. As a rule, I like third party tools that provide a tangible return on investment, and Total Visual Code Tools 2002 fits the bill. At the single license price of $199, this tool will easily pay for itself in the first year of use. Not to mention, that for Access/Visual Basic developers it's a double pay-off. The version that works with both Access and Visual Basic sells for $299 for a single user. Same great tools in either development environment. Who could ask for more?
You can check out this great product and more at the FMS web site, http://www.fmsinc.com
This article originally appeared in the Smart Access Newsletter, Pinnacle Publishing, Inc. (c) All rights reserved.
See All Articles by Columnist Danny Lesandrini