Introducing Microsoft Access for Windows 95
Michael Risse
September 12, 1995
Whether you use Microsoft Access alone or with any of the Microsoft "visual tools," such as the Visual Basic 4.0 programming system, the Visual FoxPro 3.0 database management system, or the Visual C++ 4.0 development system, the Microsoft Access for Windows 95 relational database management system is designed to speed development of your database solutions.
Microsoft Access 95 is 32-bit, multithreaded, adheres to the Windows 95 interface conventions, and takes advantage of many new Windows 95 features. This means that running Microsoft Access 95 requires either the Windows 95 or Windows NT Workstation 3.51 operating system.
This article will focus on five areas that are important to Microsoft Access and visual tool developers.
Microsoft Jet 3.0 database engine
Microsoft Jet 3.0, the database engine at the heart of Microsoft Access 95, will also ship in Visual Basic 4.0, Microsoft Excel 95, and Microsoft Visual C++ 4.0. Because Microsoft Access has a complete set of database creation, design, and optimization tools for Jet, developers can use Microsoft Access to build databases for the other tools that ship Jet. Developers can also use Microsoft Access for ad-hoc report, form, and query creation against databases used in Visual Basic, Visual C++, and Microsoft Excel applications.
The Jet database engine currently provides referential integrity, inner and outer joins, high-performance queries, and an object interface, known as data access objects (DAOs), to all Jet functionality.
Microsoft Access 95 adds DAO methods and properties for new Jet features and enhances the existing set—for example, GetRows (which retrieves rows into an array) and additional security methods. Jet 3.0 is also speedier—thanks to the new 32-bit infrastructure for Jet and open database connectivity (ODBC) data—and has better support for multiuser scenarios. For example, Microsoft Access no longer places all inserts at the end of the database, so there are fewer "lock outs" for users inserting data.
For building Jet databases, Microsoft Access currently provides graphical table design and relationship windows. Microsoft Access 95 adds the following wizards to help design, construct, and optimize Jet databases:
• An Import/Export Wizard that lets you choose data types, indexes, and which columns to import.
• A Table Analyzer Wizard to decipher flat-file data and create a set of related tables with the original data but in a Jet-optimized format.
• A Performance Analyzer Wizard to recommend index, data type, and relationship changes for faster performance.
• A Database Wizard with 20 prebuilt databases that include tables, queries, reports, and forms.
No matter which tool you are using—Microsoft Access 95, Visual C++, Visual Basic, or Microsoft Excel—these wizards for Jet are designed to make your database creation and data importation much easier.
Data replication in Microsoft Access
Replication support in Microsoft Access 95 is "multimaster," which means that data changes can occur at multiple sites. This makes Microsoft Access 95 replication an appropriate tool for load balancing a network database; providing information to occasionally connected users such as "road warriors" who dial in from the field; and distributing application updates. To keep databases in sync with a minimum of network traffic, Microsoft Access replicates changes on a row-by-row basis.
Setting up replication in Microsoft Access 95 is straightforward: create a replicable database (the "design master") and then create copies ("replicas") of the design master. After changes have been made to any of the databases, synchronize the data changes (made in the replicas and design master) or design changes (made to the design master only) between the replicas and design master.
A Conflict Wizard in Microsoft Access handles conflicting changes, and advanced replication schemas can be created with the Replication Manager in the Microsoft Access Developer Toolkit for Windows 95.
Although replication functionality is provided by Jet, only Microsoft Access and Visual Basic provide the DAO interfaces for replication. Therefore, developers using Visual C++ or Microsoft Excel will need one of these products to obtain replication functionality. Furthermore, only Microsoft Access supports Windows 95 Briefcase replication, where a Jet database is made replicable by dragging and dropping it in the Briefcase.
Microsoft Access gains Visual Basic for Applications
Like Microsoft Excel and Microsoft Project, Microsoft Access 95 now has Visual Basic for Applications. This gives developers the ability to share code and expertise across multiple products.
Visual Basic for Applications provides an integrated development environment (IDE) with many new features:
• The editor has a line continuation character, color-coded syntax, multipane code viewing, and optional line-by-line syntax checking.
• Debugging tools include breakpoints, watch variables, an "immediate" window, and a calls window to trace open procedures.
• The object browser enables developers to view the object models of OLE Automation servers and OLE Controls. New WITH...END WITH and FOR EACH...NEXT constructs and advanced parameter support make object handling and property setting easier.
Visual Basic for Applications is a superset of Access Basic, so all Access Basic keywords, constructs, and functions are supported in Visual Basic for Applications. (Note that even though all Access Basic syntax is supported in Visual Basic for Applications, there are some minor changes you may need to make when moving code to 32-bit Windows.)
The addition of Visual Basic for Applications is beneficial not only for Microsoft Access developers but also for those with expertise in Visual Basic for applications from other products, and for developers using Visual Basic who want to share code with Microsoft Access.
Enhanced OLE support for developers
Developers using any of the Microsoft visual tools that support Windows 95 will be able to use 32-bit OLE Controls and OLE Automation to assemble custom solutions quickly from off-the-shelf applications and components.
Microsoft Access 2.0 was an OLE Automation controller—that is, it could control OLE Automation servers with commands such as:
Word6.Insert 'Hello' 'control MS Word
Excel5.Recalc 'control MS Excel
Now, Microsoft Access 95 exposes its functionality to other OLE Automation controllers so that Microsoft Excel, Microsoft Project, Visual Basic, Visual FoxPro, and other OLE Automation controllers can automate Microsoft Access with commands such as:
Access7.CreateTable 'New Issues'
This means that now DAOs and all of Microsoft Access are accessible to programmers from outside the Microsoft Access development environment. In the past DAOs were accessible only from Access Basic (that is, inside Microsoft Access). Benefits of this new OLE support include the ability to create and print Microsoft Access reports from Visual Basic and Microsoft Excel.
Microsoft Access 2.0 was also an early supporter of OLE Controls, the software component model that is best described as an "enhanced" Visual Basic control (VBX). OLE Controls help developers build solutions with prepackaged, ready-to-use components. Examples of OLE Controls include calendar (included in Microsoft Access 95), multimedia, telephony, graphing, communications, and online controls.
Support for OLE Controls enables developers to build (in Visual C++ or Visual Basic) or acquire a library of software components that extend the visual tools line with new functionality.
For developers using Microsoft Access
Although developers can use Microsoft Access to bring database-specific functionality to other Microsoft development tools and applications, many developers create powerful database solutions using only Microsoft Access. This section covers a few new features designed to support these developers. These features include enhancements to the forms design environment, and tools for managing and distributing database solutions.
With forms design, the key is to enable developers to let users "live in forms," since most users prefer the form interface to the less intuitive data table. Therefore, Microsoft Access developers can now create multiple instances of the same form, change control types on the fly (without losing code or properties), and get automatic mapping of table data types to form controls (for example, a Boolean field becomes an option button).
Forms are also more intuitive with Filter By Form, where every form becomes a query that a user can fill in to search for like data, controls tips, multiselect list boxes, and custom right-mouse-click menu options.
Migrating to Microsoft Access 95
Finally, a few thoughts about migrating to Microsoft Access 95. It is possible to run existing databases "as is" in Microsoft Access 95 so they can be shared with Microsoft Access 2.0 users. However, changes cannot be made to the version 2.0 file format in Microsoft Access 95.
Another option is to convert Microsoft Access 2.0 databases to the Microsoft Access 95 file format to take advantage of the new features, new Jet engine, and so on. In this case, Microsoft Access 2.0 users cannot access or attach to data stored in the Microsoft Access 95 file format.
A compromise between leaving the database as is and converting to the new format is the Application Splitter Wizard, which splits databases into two pieces: the data tables, which reside on a network, and the application components (forms, reports, and so on), which reside locally. That way, both Microsoft Access 95 and Microsoft Access 2.0 users can access the data while running different versions of Microsoft Access on their desktop PCs.
Pricing and availability
Microsoft Access 95 (which is version 7.0, to be consistent with the other Microsoft Office 95 applications) is scheduled for availability by the end of November. Major European language versions will be released within 45 days of the U.S. version. Microsoft Access 95 will be sold both as part of the Microsoft Office, Professional Edition and as a stand-alone database product.
The estimated retail price of stand-alone Microsoft Access 95 will be US$339. There will also be an introductory upgrade price of US$109 through December 31, 1995. After that an upgrade will cost US$129.
Microsoft Office 95, Professional Edition will have an estimated retail price of US$599. It will be available at the same time as Windows 95 (it includes a coupon for Microsoft Access 95). Until December 31, 1995, a version upgrade will cost US$309 and a competitive upgrade US$349. After that the upgrade will cost US$399.
In the U.S. and Canada, contact your local reseller. Outside North America, contact a reseller or your local Microsoft subsidiary. More information on the products can also be found on The Microsoft Network at MSN: Go Accessdev, or on the World Wide Web at http://www.microsoft.com/accessdev.
Michael Risse is the lead product manager for Microsoft Access and has shipped three versions of Visual Basic and one of Microsoft Office. He is trying to reacquaint his fly rod with the rivers of Montana.
Microsoft Access Developer Toolkit for Windows 95
Microsoft is also providing tools for distributing and managing Microsoft Access solutions in the Microsoft Access Developer Toolkit (ADT) for Windows 95, one of the hundreds of Microsoft Access support tools that includes books, add-on tools, publications, and conferences. The ADT provides a royalty-free run-time license for distributing solutions to non-Microsoft Access users, an improved Setup Wizard, two-volume Visual Basic for Applications reference, additional OLE Controls, and the Replication Manager for advanced replication schemas and support for replication drop boxes.
The Access Developer Toolkit for Windows 95 is scheduled for availability in November, with an estimated retail price of US$499. Registered ADT 2.0 owners in North America qualify for a US$100 rebate. (A coupon will be in the box.)