Microsoft Access for Windows 95 replicates desktop databases and adds nifty productivity tools for users and developers
Rick Dobson
The new version of Access from Microsoft isn't just a repackaging job with the Windows 95 look and feel. The program's replication technology advances the state of desktop databases by managing multiple remote copies of a database. After working extensively with a late beta version, we can say Access's replication is easy to apply, and it's also highly programmable. Access can even replicate database objects, such as modules and forms, which you can't do with most high-end database products.
Access 95, along with Lotus Notes and Oracle, is leading the charge for database replication. The upcoming Notes 4.0 will offer more granular, fie
ld-level replication -- as compared to Access's row-level replication. Personal Oracle 7 for Windows 95 provides a more complex set of replication rules that scale up to the parent Oracle database manager. Access doesn't yet have the equivalent for its enterprise counterpart, Microsoft SQL Server. (The sidebar "Replicating Access Databases" explains Access 95 replication in greater depth.)
Developers will benefit from other enhancements, including Access 95's ability to be an OLE Automation Server, a switch from Access Basic to Visual Basic for Applications (VBA), and some new "wizards." Among other things, the wizards build databases automatically; allow developers to view ASCII data during import; convert data tables from flat, legacy designs to relational ones; and simplify getting security settings right.
To get all these new features, though, you have to switch to Win 95. Access 95 won't run on Windows 3.1.
Wizards of Productivity
Access 95 introduces wizards th
at speed up application development. The most important ones handle database design and use of legacy tables.
A new Database Wizard offers about 20 preset designs for operations ranging from contact managing, asset tracking, and ledger keeping to maintaining lists of wine and music collections. Each design includes tables, forms, reports, and event procedures. The preset applications include sample data sets and custom forms and reports for viewing data.
Developers can use these designs to expedite the early steps in building custom applications. But for those who prefer working without design templates, Access 95 offers the Performance Wizard, which reviews database objects to suggest improvements. Access can then carry out some of the recommended actions, such as indexing a field, modifying the code in a module, or converting a macro to Visual Basic for Applications.
Two wizards dramatically improve processing legacy tables. A new Import Wizard readily converts ASCII and spreadsheet tables
to database tables. We liked being able to view data while scrolling through the screens in which you decide how to import the table fields. The wizard lets you set indexes, specify data types, and decline to import fields.
The Table Analyzer converts any flat-file table into a set of relational tables (
see the screen
). The user can now process a query that has the same name as the original table; the difference is that the new query is tied to a new, relational set of tables. In addition to improving the basic database design, the Table Analyzer creates lookup lists for quickly adding records or editing fields.
Access 95 replaces Access Basic with Visual Basic for Applications, furthering Microsoft's goal of a common scripting language that works in every Office application and in Visual Basic. Besides improving Access's interoperability, the change in language brings improvements in programming, setting start-up options, and debugging.
Form and report modules now
serve as class modules, thanks to VBA (in Access, a module is a container for one or more procedures). Developers can construct methods and properties for custom classes with these modules. Sub functions define custom methods, and new Property Let, Get, and Set statements let you set and read custom properties. The reserved word New lets you create instances of custom classes. Still, as desirable as the class modules are, many Access developers will long for the full class-construct functions that Microsoft built into Visual FoxPro.
A new Startup Dialog lets programmers control an application's start-up sequence. The same dialog also permits disabling of shortcut menus. You can impose security restrictions that prohibit users from modifying these settings.
The move to VBA also results in an easier-to-understand module window that has enhanced debugging features. Access 95 dramatically improves the readability of modules by using continuation lines and color coding to distinguish comment lines and
keywords from code. The basic Immediate Window is also better, with a new Watch pane for tracking the value of key expressions as a procedure runs. You can specify how an application responds when it reevaluates a watch expression, telling it to either show the value or stop.
Security Access
Access 95 simplifies security. A new workgroup information file -- its format is not compatible with Access 1.x and 2.0 -- offers control of new features, such as who can create replicas or set the Startup dialog controls. However, these and other improvements come at the expense of file-level backward compatibility.
In addition to the traditional user-level security in previous versions, Access 95 introduces database password security. This is very appropriate for departments where anybody in a workgroup has total access to a database but those outside the department have none. Database password security is similar to the share-level security in Windows for Workgroups.
A Securit
y Wizard, available from the Tools menu on the database menu bar, automates the process of setting basic user-level security. It reduces the chance that developers will leave routes to database objects open to users who are not supposed to access them.
Access 95 has three important security enhancements. First, it grants permission to replicate a database. Second, it lets you set a password for a database. Third, it grants access to the startup properties of a database.
Now Serving OLE
Two of Access 95's most significant improvements are new OLE Automation features and better menuing capabilities. As an OLE Automation Controller, Access 2 could readily tap the exposed functionality in other applications, such as Excel and Word. Access 95 builds on this by making itself available as an OLE Automation Server. Now, any program that can be an OLE Automation Controller, such as Visual Basic, Visual C++, Excel, and Project, can launch Access and manipulate its objects.
Dev
elopers will like the expanded custom menuing that they'll be able to incorporate into the applications they build. Adding shortcut menus is no more complicated than invoking the familiar Menu Builder. You then use the new SetMenuItem action in either macros or procedures to make custom menu items appear gray or deselected.
No-Brainer Upgrade?
Access 95's interoperability with the rest of Microsoft Office makes the upgrade decision almost automatic for organizations that will standardize on Office 95. This is even more true in companies that need database replication for mobile workers.
Companies that haven't made the move to Windows 95 -- but that have a substantial need for data replication among their mobile workers -- have a more difficult decision. Should they adopt Windows 95 in order to get the advantages of Access 95?
If you are a Windows shop that has no existing replication solution, you should adopt Windows 95 and reap the replication benefits of Access 9
5. But if you currently use Oracle or Sybase replication servers, then stick with the solution that works for you now.
PRODUCT INFORMATION
Access for Windows 95..........$339
Microsoft Corp.
Redmond, WA
(800) 426-9400
(206) 882-8080
http://www.microsoft.com
screen_link (36 Kbytes)
The new Table Analyzer Wizard converts flat-file databases to relational. Shown here are the database container and Relationships window after
the Table Analyzer has created a relational design. The original flat table, named Addresses, has been renamed Addresses_OLD, and two new relational tables have been built.
The wizard also creates a new query (not shown) that ties the two relational tables together to reproduce the records in Addresses.
Rick Dobson is president of CAB, a consultancy specializing in database development and Windows 95. You can reach him at
Rick_Dobson@msn.com
.
Access 95 Advances Database Design
