The Top 20 New Developer Features in Access 97

Conventional wisdom has it that Microsoft always takes three tries to get software right. And the pundits are certainly right in the case of many products, including Windows itself. But Access is a different animal-with their primary desktop database, Microsoft appears to work in cycles of two. Although, the first release of Access lacked polish and functionality, version 2 was a watershed tool. It is still considered by millions of developers to be the best possible database application platform for a variety of uses. So Microsoft got it right in two, not three releases. (Of course, this does not count version 1.1, but that was an inline release, so it doesn't count.)

Then came Access 95. As any developer who toyed with the first 32-bit product can tell you, it appeared to be a step backwards. Sluggish performance and language-induced stability problems plagued the 95 release, and its adoption was further hampered by slow migration to Microsoft's 32-bit operating systems. All in all, Access 95 was neither the software we expected, or the success Microsoft anticipated.

If we are looking for a pattern here, it appears that the fourth attempt, Access 97 should be the 32-bit equivalent of Access 2-what Access 95 should have been. But does this newest version measure up? From preliminary tests, I'm quite optimistic. The key problems of performance and stability have received a large amount of attention from the Access team, and Access 97 delivers major advancements in these areas. Beyond these key areas, Access 97 is chock full of new features. To see what's new, strap yourself in for a lightning tour of the top 20 features for developers in Access 97.

Microsoft has stated repeatedly that performance is the top goal for Access 97. Indeed, the code name of this product is "Mach 5", a reference to the super fast vehicle of Speed Racer fame. So if performance was the number one goal, Does Access 97 deliver? In a nutshell, yes. Just about every area in Access has undergone either radical internal restructuring or at least some tuning. Access itself now loads very quickly-faster in fact than any of the other main Office applications. Form load time has been greatly improved by delay-loading various components such as VBA and Jet. Dozens of other tweaks have been made to form storage and retrieval to make them pop up faster. And the addition of lightweight forms allows you to develop basic forms that load very quick indeed: they don't need to load a module or VBA itself.

When doing performance testing, Microsoft used a real-world testbed machine consisting of a 486/33 with 16MB of RAM. Using what many consider to be such a lowly machine may seem strange, but it allowed the Access team to look at many worst-case performance scenarios. In Access 97, all forms load 20% to 55% faster than they did in Access 95, and some form load tests have shown 10% to 30% increases in speed over Access 2.0. Memory usage has also been reduced-Access 97 with the NorthWind database open consumes 30% less memory than Access 95.

Application load time has not been ignored either. The DAO library and UTILITY.MDA database are not loaded until Access actually needs them. Likewise, the load of the workgroup information file (typically SYSTEM.MDA) is deferred until it is actually needed. Additionally, work has been done on tuning the swap process for Office components and VBA.

Finally, additional enhancements have been made in version 3.5 of Microsoft Jet. Indexed columns work better in multi-user environments because the number of lock conflicts has been reduced. Optimizations are in place for large queries, temporary queries, and queries using the inequality operator-all of these types now run faster. Sequential reads have also been improved because Jet has increased its pre-allocated buffer to 64K.

A word of caution: those expecting to return to Access 2 levels of performance for code compilation, form loading and application loading should probably stay with Access 2. I doubt any 32-bit incarnation of Access will ever match Access 2 performance in all areas.

If you have ever created a client/server application using Access, you have no doubt enjoyed the advanced features that Microsoft Jet gives you when hitting server data. Features like heterogeneous access, updateable joins, cached table definitions, implicit parameters, nested queries and the like. But you probably ended up wanting a faster, low-level interface to the server, and were no doubt jealous of VB4's Remote Data Objects implementation.

The good news is that version 3.5 of DAO, which ships with Access 97, introduces a new interface called ODBCDirect. This new set of extensions gives you an almost direct route to the server, using a very thin programming layer over the ODBC drivers. ODBCDirect allows you to set up objects that deal with server data using the same familiar DAO model you have been using since Access 2.0. Using ODBCDirect, you can enjoy real speed improvements, as well as access to server specific functionality like stored procedures and server-side cursors. And did I mention asynchronous queries? You can now send a query off to your server and monitor its completion status. If you use Access for client/server applications, this feature alone is reason enough to upgrade.

If you are an inquisitive developer, you may have tried to play with the wizard libraries that shipped with Access 95. If you did, you quickly realized that there was something different about these databases. The design surfaces for forms, reports and modules are disabled on all objects! Is this some new form of security? There doesn't seem to be anyway to view module code, and more importantly, the size of the library databases is smaller than the source-code versions you can download from the Microsoft Web site. So what's going on? In a nutshell, the wizard libraries use an exciting bit of functionality that exists in Access 95, but is not exposed to developers. Known as "Make Wizard" technology, this feature allowed Microsoft to create "compiled-only" versions of their library databases. The databases contained not one line of source code, only the compiled state.

The advantages to compiled databases are numerous. First, since no source code is included in the database, code security concerns are a thing of the past. Secondly, the lack of source code results in a much smaller database size. And finally, since all design surfaces are automatically disabled, you (or your application's users) cannot make changes that would cause the database to become decompiled. If you develop Access applications using VBA and distribute them to users, the Make MDE functionality is a major advantage.

Version 4 of Visual Basic introduced Class Modules. Suddenly, a whole new world of programming was opened up-the ability to define and store your own objects, complete with properties and methods. But since the Access implementation of VBA is always a feature-step behind Visual Basic, Access 95 lacked true class modules. In their place, you could create pseudo class modules by putting code behind a form or report. Non-private functions in that code became "methods" of the form, and through the use of Property Let and Property Get procedures, you could define your own custom properties for forms. Problem was, you had to base everything on a form; hardly the best vehicle for abstract object modeling.

In Access 97 this changes. You can now create true class modules and enjoy all the benefits of this powerful new way of defining objects. By creating a class module, you define an object and encapsulate its internal composition, and what it can do, by writing VBA code. The scope of possibilities with class modules is truly wide, and you can expect to see entire articles devoted to them in future issues.

The tabbed divider, also known as the tab control, is becoming ubiquitous in Windows development. Unfortunately, Access developers have long had to work with a myriad of problems when using tab controls. You either had to rely on OCX controls that didn't support containership, or implement your own native tabs using a slew of lines, rectangles and other design elements. Heck, even the last version of Paradox for Windows had a working tab control!

Access 97 addresses this problem by finally building in a native tab control. The biggest advantage here is the "native" part. This is a real Access control, and it doesn't rely on OCX/ActiveX components. It is fully integrated into both the form, and the property sheet. Under the covers, the new native tab control is just an internal wrapper to the Win95/WinNT 4.0 tab object, but the end result is impressive. When compared to OCX components, the new tab control is faster, smaller, and more reliable. Plus, you no longer have to distribute additional components just to support tabs.

While Access 95 introduced various editor enhancements like color-code syntax and variable watches, you ain't seen nothing until you write code in Access 97. There are so many editor enhancements that in can take weeks to familiarize yourself with all of them. In this brief space, I'll try to cover the hottest.

Auto List Members: Displays a drop-down list at the cursor position when you are about to type in a built-in word. This list contains relevant objects, methods and properties according the word you just typed. For example, type in "DBEngine" and as soon as you type a dot, a list of all the possible members of DBEngine appears. Select the one you want and you're on your way-no more searching through help files for the information you use hundreds of times a day.

Auto Quick Info: Displays a tooltip below the current line of code as you are typing. This tooltip contains the syntax definition for the procedure or method name you are typing in. Yet another way to reduce the number of trips to the help file.

Keyboard Step Into: One of the least discoverable of the new editor features, but is certainly one of the best. With the module editor open, position the cursor on a procedure declaration. Press [F8] and the procedure starts executing! While on its surface this may seem trivial, consider how many times you have gone to the debug window and typed in a procedure name to test it.

Schizophrenic Menus: Are alive and kicking in this version. Just to keep you on your toes, Microsoft has (yet again) re-arranged the module editor menu trees and hot keys. On the plus side, there is now a single menu command to compile all modules and save the compiled state at the same time. On the down side, the hot keys for compiling modules has changed yet again. This completely aggravates me-just when I get used to a version of Access (selecting Compile All from the menu a few hundred times a day can do this to you), they go and change it. Access 97 carries on the noble tradition of moving the Compile command and changing its hot key with every new version.

Although debugging is probably one of your least favorite development task, Access 97 adds many new features to make it easier. A number of new features have been added to both the editor and the Debug Window (known as the Immediate Window in Access 2.0).

Auto Data Tips: Create a tooltip showing the value of a variable or expression when you hover over the source code with your mouse.

The Debug Window: Now has three panes. The Immediate pane works like it did in previous versions-you can type in code and expressions, or see the results of Debug.Print statements. The new Locals Pane shows a expandable TreeView of all variables, at both the local and module levels. The first time you see this, you'll appreciate the fact that you can now instantly see the state of not only your variables, but Access, VBA and Jet objects. Finally, the Watch pane shows the contents of any Watch expressions you have set.

Step Out: Allows you to "back up" when you have inadvertently stepped into a procedure in debug mode. For example, assume procedure A calls procedure B, which in turn calls procedure C. If you step into procedure B, and realize that's not what you wanted, you can step out. This causes the rest of procedures B and C to be executed, and you are returned to break mode in procedure A.

Quick Watches: Allow you to select an expression in your VBA code and instantly add a Watch to that expression.

Finally, there are a slew of new keyboard commands to make things like indenting and outdenting code, calling up the Auto Data Tips, and other common operations much more accessible. The Object Browser has also been beefed up to make it easier to find object, property and procedure definitions.

Although Access 95 and Jet 3.0 introduced the concept of Replication to Access developers, the technology was limited by the fact that there was no built-in support for Partial replicas. This has been remedied in Access 97/Jet 3.5. You can now define replicas that contain only a subset of data by identifying a filter to use when the replica is synchronized. The advantages of partial replication is obvious: your databases can be smaller, and the amount of time the synchronization process takes is diminished. This last factor is especially important if you are synchronizing over a slow network, or over phone lines with a modem. If you hesitated using replication in Access 95, this feature alone should cause you to re-think the benefits of this remarkable technology.

CommandBars are one of the most visible new features in Access 97. CommandBars, implemented as an Office component, integrate toolbars and menus into one object hierarchy. The best part of this is that you can now programmatically create, modify and manipulate toolbars and menus in Access. Gone are the days of having to rely on menu macros, and having only limited control over Toolbars. CommandBars also give you new object types from which to choose when creating your toolbars. For example, you can create drop down lists directly on a toolbar, or modify the icon property of buttons. Finally, you can now call your own functions from toolbars and menus.

If you have ever tried to work with multiple developers on an Access application, you know the frustration of Access' inability to handle changes efficiently and safely. Although it does not ship in the retail box of Access, or in the Office Professional Suite, integration with Microsoft's Visual SourceSafe version control server is included in the Microsoft Office Developers Edition (ODE). With SourceSafe integration, you now have fairly extensive version control capabilities with check-in/check-out, rudimentary comparison operations, and archival backups.

Unfortunately, SourceSafe itself is not included in the ODE, so you'll have to shell out some additional cash to solve your version control problems. Additionally, the Access/SourceSafe integration model lumps all table data, relationships, command bars and options into one object. You can either check all of this out, or none of it. Finally, the current version of SourceSafe has an internal limit of approximately 67MB for any single object-an obvious problem with large databases, but one the Microsoft promises to address with a future release.

Since the very first version of Access, you have always been able to tune the Jet engine by modifying settings. And each version of Jet has exposed new settings, giving you a great deal of flexibility over how the engine works. The problem is that you had to shut down and re-start Access to get changes to these settings to take effect.

With DAO version 3.5, the new SetOption method of the DBEngine object allows you to make changes to various Jet settings on the fly. Your application can now increase resources for certain database-intensive operations, and then reset the values for normal data-entry behavior. Unfortunately, there is no corresponding GetOption method, so you can't query the value of a specific setting. This makes it difficult to reset options to their original values.

Ok, so this one really isn't a new "feature". But it is a very important architectural change that makes it much easier to maintain and Access database in an optimal form. In Access 95, if you rename a database, or compact it into a new name, all the module code in the database is marked for decompilation. This means that the next time you open that database, VBA needs to recompile the code, which introduces severe performance penalties. This is because VBA in Access 95 uses the database name for its project name. Every bit of code is tied to this name, and if the name changes, VBA assumes that the project is no longer valid, and needs to be rebuilt. So it marks every module for decompile.

In Access 97, when you first create a database, its name is used to create the VBA project name. But from then on, the project name is completely distinct from the database name: you can compact and rename at will without causing the project to decompile.

Sometimes usability enhancements are so obvious that they take a while to get implemented. Take the example of compacting and repairing databases. Prior to Access 97, you had to close your database, select Compact or Repair from the appropriate menu, use the Open File dialog to point to the database, and then select a new name to compact into. Let's do a little math here. The above sequence of events is about 10-20 mouse moves and clicks (more if your databases have a non-MDB file extension). Multiply this by the number of times you repair or compact your databases during a normal development week.

This is altogether too many operations for something you do on a regular basis. Access 97 introduces what I think is the biggest usability enhancement of all: while your database is open, select Repair or Compact from the Tools menu, and Access automatically closes the database, compacts or repairs it, and then re-opens the database. What a time saver!

Access 95 defined a new referencing model that caused some headaches. If you wanted to call code from another database, you needed to manually add a reference to that database. And the path and name of the referenced database was hardcoded in your database. This meant that you had to manually add a reference to each database you wanted to call code in. Although you could use some rather nasty hacks to add references programmatically, there really was no robust way to solve this problem.

Access 97 handles this by giving you a new References collection and object. You can now programmatically detect all references in a database, and see properties that define where the referenced object is, and whether or not the reference is valid. Also, you can programmatically create and delete references with the new AddFromFile, AddFromGUID, and Remove methods. The ability to programmatically work with references should make the distribution and installation of your Access applications much easier.

Although Access 95 surfaced a module object, its properties and methods were very limited. With Access 97, the object model for modules has been beefed up. New methods support loading text from a file or string into a module, adding and deleting lines, finding lines and replacing existing code. New properties allow you to detect and set the number of lines, the number of procedures and interact with starting and ending lines in a procedure or module. These new features are a real boon for developers who need to programmatically create and modify code.

The importance of VBA's compiled state can not be overstated, especially in databases with a lot of code. Access 95 suffered acute performance problems when a database lost its compiled state. This problem was exacerbated by the fact that you could not programmatically detect whether or not a database was compiled. In real terms, this meant that you could not detect the potential performance bottleneck and alert users to the problem.

In Access 97, this sad state of affairs is addressed with the new IsCompiled property of the Application object. When the value of this property is True, all the module code in the database is saved in a fully compiled state. When the value is False, you should make note of the fact and take measures to compile.

One of the optimization techniques often recommended for forms is to take RecordSource and RowSource SQL strings out of the form and store them as real queries. SQL strings in these properties represent a performance slowdown: when the SQL string is encountered, Jet must create an execution plan, compile it as a query, and then execute it. On the other hand, if the SQL string is saved as a real query, the creation of the execution plan and the compilation happen only once-when the query is saved. This means that when the form encounters a saved query in the RecordSource or RowSource property, much of the work has already been done, making the form load or run faster.

In Access 97, RecordSource and RowSource queries are automatically saved as hidden system queries. This means that you no longer have to worry about SQL strings being a performance problem. And because you can now delete all the saved queries you previously created to enjoy the performance benefit, parts of your application will run faster because of the smaller QueryDefs collection in your database.

Database conversion was one of Access 95's weakest areas. Many large databases simply could not be converted in one pass, leaving you to import objects in chunks. What's worse, when out Access 2 objects finally made it into an Access 95 database, the database itself was in the worst possible shape. Creation and deletion of temporary objects caused the database to grow in size beyond even worst case estimates. And Access 95 neglected to compile your code for optimum performance. In my humble view, this was the single biggest pitfalls of the product-developers eager to try the new version converted their databases, only to find that performance was, to put it charitably, lackluster. The end result was a terrible first impression for all.

Obviously, Microsoft viewed this conversion fiasco in the same light. As a result, Access 97 is much smarter about moving your Access 2 and Access 95 objects into its realm. First, you can actually convert extremely large and complex databases with one operation. Second, Access 97 automatically compiles all your module code and saves a compiled state. Then, as its last smart move, it compacts the database, making the end result a much better picture of how Access 97 treats your database.

Remember the dark old days when you had to use DoMenuItem? Remember creating fake macros just so you could manually count through the choices (was that starting with 0 or 1?) to build the argument list? With the addition of the RunCommand action, every action that was available through DoMenuItem is now available with just one argument. Each action is defined as a constant, and all constants are available through both the help file and through the Auto Quick Tips mentioned earlier. This feature should save developers a lot of time and frustration when calling Access actions.

What modern software would be complete without the requisite Internet/Intranet features? Given Microsoft's recent transformation into a net-driven company, it is hardly surprising that Access 97 would have a slew of new features in this area. I'll defer coverage of these areas to other articles, since the new features are too numerous to mention here. Suffice to say, Microsoft has all the marketing bullet points covered here: you can import or link (attach in Access 2 parlance) to HTML documents, and export objects to HTML. You can use the 'Publish to the Web Wizard" to send datasheets, forms and reports to static or dynamic HTML for easy integration of your Access objects into a web site, and import or link to FTP and HTTP servers. Additionally, the new Hyperlink object and data type allow you to insert hyperlink addresses into your tables using either Universal Naming Convention (UNC) or Uniform Resource Locator (URL) entries.

With competitive pressure mounting from an increasing number of fronts, Microsoft has once again proven that it will not sit by and let its products languish. While many developers expected Access 97 to be release centered on stability and performance, the fact is that this newest release not only fixes problems associated with the 95 release, but adds a long list of new features for both users and developers. Stay tuned for details on this exciting new release.