Total Access SourceBook: FMS Inc.'s VBA Library

MS Office and VB for Applications Developer

Thomas Wagner, September 1998

As the name implies, FMS Inc.'s Total Access SourceBook (TAS) is a library of VBA source code tailored for use in developing Microsoft Access applications. Considering it's the code found in the production of some of the most successful Access add-in tools on the market, it ought to be worthy of anyone's attention.

The value of this library depends on a number of factors:

• How well do you program?
• How do you feel about using purchased code in your projects?
• How far behind are you in your current project?

I've known programmers who absolutely refuse to consider a tool such as TAS. It's simply a matter of pride. "Who needs this? I can write it better myself," was usually the reply when asked about products such as TAS. If this isn't your modus operandi, read on.

Several companies have published Visual Basic and Access programming frameworks. TAS is not one of them. It is, by no means, a framework. Rather, it's a collection of over 500 well-documented and well-tested VBA functions, presented in a very capable management application called Code Browser (see FIGURE 1).

The code is divided into 36 categories, including everything from Attached Tables, Access Reports, Access Forms, ODBC and Client/Server, Error Handling, Managing Objects, to Windows Registry, Office Objects, and Internet and Intranet Web functions.

The product contains an inordinate amount of date and time manipulation code, math and string functions, and Access (JET) environment-specific code. For example, it has approximately 63 functions handling mathematics, from general subjects such as "Is a number prime?" to trigonometry and some serious statistics. As a bonus, a lot of these functions are generic within the realm of the VBA engine -- including Visual Basic, Excel, and Word. Some may need a very small amount of modification. Have you ever needed to write code that deals with business days as opposed to regular calendar days? TAS has several nifty ways of dealing with everything from finding out the first or last business day of the month or week, to knowing the number of business days between two given dates.

Initial Tests

Just for fun, and as a way of testing the TAS Code Browser, I attempted to use certain sections of the error-handling code by selecting them and pressing [CTRL] [C] to copy them into the Clipboard (thinking I could fool the library). As soon as I attempted to execute this set of tasks, the system informed me that the specific function I wanted to copy was actually dependent on another routine in the Error Handling category. It then offered to copy the necessary additional code as well. Now that's slick! Someone spent some time thinking how people may actually get the code out of the TAS into their own environment. Not everyone feels the need to follow menus or command buttons, but, no matter, the application handled my curve ball gracefully (see FIGURES 2 and 3).

Another test I performed dealt with the library code used for manipulating Access tables. In a matter of minutes, I put together a form that was used to export a specific table to a text file, which was accomplished using one library function. Once exported, I retrieved the names of all Access Table objects in my database into an array (another single library function), searched the array for a specific table, and deleted it from the database container. The FMS code ran just as advertised, and the act of finding, importing, and testing the necessary functions took a fraction of the time it would have required for me to write the code from scratch. Of course, that's the whole idea of the product.

Aside from housing the library code, TAS' Explorer-styled interface has some additional amenities. First, it allows you to add your own functions to the system. You can define special groups or names used in the organization of your code. Even better, the interface was built with the idea of a programming team in mind. It's multi-user enabled, and, if the data file is loaded on a share point, can be utilized by a team as a central code repository.

Keeping in mind that a group of people can generate a great deal of code, you may appreciate the ability of the Code Browser to search the contents of the system by a number of parameters. The search can be concentrated on areas, such as parts of Annotations, Descriptions, or the functions themselves, as well as the Created by or Modified By sections of the code (see FIGURE 4). Note that the latter parameters are only applicable to user-created code -- not the 500-plus functions supplied by FMS.

In addition, the product shops with a Windows API Viewer that allows you to quickly find the necessary declare statements required by the Windows API (see FIGURE 5).

Lastly, FMS ships a small administrative application along with the Code Browser. This utility can be used to import procedures from previous versions of TAS, and, more importantly, it an be used to apply periodic patch files available on the FMS Web site. These files can contain bug fixes for existing routines, or new routines to be made available to registered users (see FIGURE 6).

A good amount of thought went into the design of TAS' Code Browser. It brings enough value to the table to make it a contender in the area of code repositories. However, the biggest value is the VBA functions shipped with the product. It's like having one of the people at FMS on your staff. Need to save user-selected values to the registry (like placement of forms or application color schemes)? No problem! The library contains all the registry functions you'd want, including examples on how to use them.

Closing Thoughts

After spending some time with the product, I have only one wish for the next version of TAS. Because Access 97 supports class modules, it may be nice to see how FMS can take advantage of this functionality by including some "ready-to-roll" classes in their code library.

Finally, as I mentioned at the beginning of the review, this product is not an application framework. And that's completely by design. According to Dan Haught, Vice President of FMS, the research conducted by FMS indicates that a number of users feel somewhat stifled by frameworks. "Why do I have to adapt someone else's paradigm of how an application ought to flow, just to use the code that comes with the product?" is one of the more prevailing questions. Well, programmers are nothing if not independent. With that in mind, I recommend you have a look at Total Access SourceBook.

Thomas Wagner is the Manager of Software Development & Consulting Services at NovaQuest InfoSystems located in Torrance, CA. An author, programmer, and technology consultant, Thomas specializes in Microsoft Access and Visual Basic and Internet database projects.