Executive Summary
Microsoft Access 97 for the Windows 95 and Windows NT operating systems provides
relational database power for your programs. Its visual design and event driven nature
make Access a powerful and easy tool to learn. Access is quick and easy to use which
makes it a popular tool with home users. Small business owners benefit greatly from
Access because they can develop their own database applications and eliminate the cost of
third party developers.
Microsoft Access 97 makes it easy to turn data into answers and includes tools that help
even first time users get up and running quickly. For example, the Database Wizard can
automatically build custom databases in minutes. The Table Analyzer Wizard quickly
transforms linear lists or spreadsheets into powerful relational databases.
Access 97 offers greatly enhanced 32-bit performance with smaller forms, more efficient
compilation, and better data manipulation technology for quicker queries and responses.
Other features further improve execution time and help you build fast business solutions.
The Performance Analyzer Wizard automatically recommends the best way to speed up your
database. Additionally, Visual Basic for Applications and OLE make it simple to build
quick solutions and integrate them with other Microsoft Office programs.
Table of Contents
WHAT IS ACCESS? 3
A BRIEF HISTORY OF ACCESS 4
ACCESS 1.X / ACCESS 2.0 4
ACCESS 95 / ACCESS 97 4
HARDWARE REQUIREMENTS 6
RAPID APPLICATION DEVELOPMENT 7
THE EVENT DRIVEN MODEL 7
VBA IN ACCESS 95 / 97 8
THE JET DATABASE ENGINE 10
WHERE IS ACCESS TYPICALLY USED? 11
ACCESS IN CORPORATE BUSINESSES 11
ACCESS IN SMALL BUSINESSES 13
ACCESS AT HOME 13
FEATURES OF ACCESS 97 15
INTEGRATING ACCESS WITH OTHER APPLICATIONS 17
MICROSOFT OFFICE 17
ACCESS AND VISUAL BASIC TOGETHER 18
CONCLUSION 20
GLOSSARY 20
ENDNOTES 23
BIBLIOGRAPHY 28
What is Access?
Microsoft Access for Windows is a relational database management system. Access uses the
graphical abilities of Windows so that you can easily view and work with your data in a
convenient manner. Access makes your data available to you quickly and easily, and
presents it in an effective and readable way. Its ability to locate information using
query by example eliminates keystrokes and consequently speeds up the development
process.
Access lets you examine your data in a variety of ways. Sometimes the information in a
record is easier to understand if the record's fields are arranged on a form or a report
in a visually pleasing way; sometimes you need to see the maximum number of data records
possible on your screen.
A Microsoft Access form is a special window that is used for data entry. You can use the
visual capabilities of Windows to create a custom form using a combination of graphics
and text. Forms can present data in a format that is easier to read and understand than
a data sheet. The form wizard is an internal tool that helps you create data entry forms
by asking the user to answer a number of predefined questions. The wizard asks about
how the data is to be displayed and then sets up the layout based on the responses.
Overall, Access is a tool that allows users to create, edit, and maintain sophisticated
databases. Users can accomplish this without programming skills. However, Access Basic
provides programmers with additional abilities to automate and extend the functionality
of their database programs.
A Brief History of Access
Access 1.x / Access 2.0
Access 1.x and 2.0 run under Windows 3.1. Access 1.0 debuted in 1993 and set the
standard for databases in Windows. At this point, Access was a little too much for home
use. The typical home PC user was not at the same level as Access was with respects to
application development and understanding of relational data modeling. Access had only
a few wizards and thus a long learning curve. Additionally, few home PCs had adequate
RAM and processor speeds to accommodate Access.
The arrival of Access 2.0 changed things quite a lot, offering more wizards and add-ins
to supplement the package.
Access 95 / Access 97
Access 95 and 97 are 32-bit applications which run under Windows 95 or Windows NT.
Access 95 is also frequently referred to as Access 7.0. Microsoft has improved these
products over previous version of Access so that they integrate better with other
applications in the Microsoft Office suite. The 32-bit versions are also more heavily
oriented towards the home user than previous versions.
Access 95 introduced Visual Basic for Applications as a means of integration and
compatibility. Users could use Access to define data structures and their relationships,
then export the generated schema to VB. This gave Access 95 an edge over competing
products such as Paradox and Delphi.
Access 97 is a product with multiple personalities. On the surface, a quick tour of
Access leads you to believe that it was created primarily for novice database
programmers. Like a friendly personal assistant, Access helps to organize and store
information by using features such as the following:
? A well organized Database window
? Wizards for constructing database objects
? A variety of built-in properties to define each object
? A simplified macro scripting language
Below the surface lies a completely different infrastructure. Access 97 has the
following additional abilities:
? Automation allows Access to print reports from within Visual Basic or to edit Access 97
table data while inside an Excel worksheet.
? The Visual Basic for Applications programming language gives you the building blocks
for creating robust applications in Access 97 and for automating complex business
processes.
? The Access relational data model and Structured Query Language (SQL) foundation allow
you to make uncomplicated representations of complex data.
? The Jet Database Engine exposes programmable Data Access Objects that provide your
program code direct access to database data and structures.
Throughout the different versions of Access its user friendly interface has not changed
much. It still makes designing a database look relatively easy, but it has become more
flexible and powerful.
Hardware Requirements
Access is a resource hungry application. However, hardware requirements for a developers
and end users are different. Be sure to note the actual as opposed to recommended
requirements.
What Hardware Does Your System Require?
According to Microsoft documentation, the official minimum requirements to run Microsoft
Access 7.0 for Windows 95 are as follows:
? 386DX processor
? Windows 95 or Windows NT 3.51 or later
? 12 megabytes of RAM on a Windows 95 machine
? 16 megabytes of RAM on a Windows NT machine
? 14 to 42 megabytes hard-disk space, depending on whether you perform a Compact,
Typical, or Custom installation
? 3 1/2-inch high-density disk drive
? VGA or higher resolution (SVGA 256-color recommended)
? Pointing device
Recommended specifications for a development machine are much higher because you will
probably run other applications along with Microsoft Access. In addition to Microsoft's
requirements, these are the recommended requirements :
? A Pentium or Pentium Pro processor - 100 MHZ or faster
? A fast ATA-2 or SCSI hard drive
? At least 20 megabytes of RAM for Windows 95, and 24 megabytes for Windows NT. Increase
this amount if you like to run multiple applications simultaneously.
? A high-resolution monitor (larger is better) and SVGA graphics
The bottom line for hardware requirements is that the more you have, the better off you
are. The increased speed and performance will make you much happier when you use Access
or any other large, powerful program.
Rapid Application Development
Visual application design tools such as Access, Delphi, Visual Basic, and Oracle Forms
allow the user to begin program development with the user interface. This approach is
radically different than traditional program development where the user interface is
typically designed last. Access allows the programmer to draw the individual components
of the program on the screen, then link code to each object on that form. The programmer
creates the interface much like he would use a paint program. Different objects and
painting tools are selected from tool bars and applied to the form with a click of the
mouse.
This process is commonly referred to as Rapid Application Development, or RAD. RAD
allows the programmer to develop applications quickly with very little turn around time
and a minimal amount of coding. What RAD eliminates is duplication of effort. GUI
design elements common to all Windows applications do not have to be recreated for each
program.
The Event Driven Model
Event driven programming is a concept which goes hand in hand with RAD tools. Access and
almost all competing RAD products fully support the event driven model. Traditional
programs have a well defined flow of control. They execute sequentially from beginning
to end. On the other hand, event driven programs do not have a logical beginning or
ending point. The program will actually do nothing - until an event occurs. Once an
event occurs, then the program will respond accordingly, depending on the type of event.
Some examples of events include an application being run, mouse clicks, mouse movements,
and keystrokes.
Unknown to the user, Windows traps events and notifies the application behind the scenes.
Access traps these notifications, called messages, and allows the programmer to design
his program around those events. For example, a double-click event on an OK button
could initiate a database query or anything else the programmer desires.
VBA in Access 95 / 97
Visual Basic for Applications is the development language for Microsoft Access 95. It
provides a consistent language for application development within the Microsoft Office
suite. The core language, its constructs, and the environment are the same within
Microsoft Access for Windows 95, Microsoft Visual Basic, Microsoft Excel, and Microsoft
Project.
The early versions of Access used a coding engine called Access Basic or EB (Embedded
Basic). It had some similarities with its other siblings like VBA, Excel and Project.
However, a major difference is that Access Basic was written in assembler language and
VBA was written entirely in C.
Microsoft was highly motivated to implement one common Basic engine for all of its
development applications. The benefits of this standardization to the developer are:
? Reduced learning curve. Microsoft is distributing Basic more widely each year, adding
it to everything from the entire Office suite to its Internet browsers and servers. As a
solution developer, you can now learn one rendition of the Basic language and one
development interface, then carry your skills and experience with Access VBA into your
work with other VBA host products.
? Code portability. One of the current developer buzzwords is reusable objects, a term
that describes self-contained servers (or something that provides services to an
application). In order for a code procedure to qualify as a reusable object, you must be
able to carry code from one host application into another to use it unmodified. VBA
provides this capability.
? Shared resources. By sharing a centralized coding and run-time environment, multiple
tools and applications on your machine share the same dynamic link libraries and type
libraries. The performance of your workstation improves when you have fewer resources
loaded to memory, and this speeds up your development efforts. Disk space consumption,
application deployment efforts, and version control issues are all favorably impacted
when multiple applications on your machine share central services.
Simple Access applications can be written using macros. Although macros are great for
quick prototyping and very basic application development, most serious Access development
is done using the VBA language. Unlike macros, VBA provides the ability to:
? Work with complex logic structures
? Utilize constants and variables
? Take advantage of functions and actions not available in macros
? Loop through and perform actions on table rows
? Perform transaction processing
? Programmatically create and work with database objects
? Implement error handling
? Create libraries of user-defined functions
? Call Windows API functions
? Perform complex DDE and OLE automation commands
The Jet Database Engine
Microsoft Access 97 ships with the Microsoft Jet database engine. This is the same
engine that ships with Visual Basic and with Microsoft Office. Microsoft Jet is a
32-bit, multithreaded database engine that is optimized for decision-support applications
and is an excellent workgroup engine.
Microsoft Jet has advanced capabilities that have typically been unavailable on desktop
databases. These include:
? Access to different data sources. Microsoft Jet provides transparent access, via
industry standard ODBC drivers, to over 170 different data formats. These formats
include dBASE, Paradox, Oracle, Microsoft SQL Server, and IBM DB2. Developers can build
applications in which users read and update data simultaneously in virtually any data
format.
? Engine-level referential integrity and data validation. Microsoft Jet has built-in
support for primary and foreign keys, database specific rules, and cascading updates and
deletes. This means that a developer is freed from having to create rules using
procedural code to implement data integrity. The engine itself consistently enforces
these rules, so they are available to all application programs.
? Advanced workgroup security features. Microsoft Jet stores user and group accounts in
a separate database, typically located on the network. Object permissions for database
objects are stored in each database. By separating account information from permission
information, Microsoft Jet makes it much easier for system administrators to manage one
set of accounts for all databases on a network.
? Updateable dynasets. As opposed to many database engines that return query results in
temporary views or snapshots, Microsoft Jet returns a dynaset that automatically
propagates any changes users make back to the original tables. This means that the
results of a query, even those based on multiple tables, can be treated as tables
themselves. Queries can even be based on other queries.
Where is Access Typically Used?
Access in Corporate Businesses
Many midsize and large companies rely heavily on Access, but none rely exclusively on
Access. Companies of any significant size usually have complex data needs, multiple
database platforms and dozens to thousands of application users. In such an environment,
no single product is sufficient to satisfy all needs. Access becomes one piece of an
often complex puzzle of application development tools.
Virtually all technology companies with more than one hundred employees have some
in-house development staff. These departments are usually called Information Systems
(IS) or Information Technology (IT). Corporations with changing technology have the
challenge of efficiently retraining their application development staff. Access wins big
in such a circumstance for two main reasons.
First, Access has a reasonable learning and implementation cycle. It is neither the
easiest nor the hardest development tool to learn. There are enough books, videos,
courses, and conferences built around Access that companies can shop competitively and
select the best staff retraining option they can find. There are also thousands of
consultants and contractors that can help the IT staff make the transition to Access
without wandering in the dark.
Secondly, Access is flexible. Access fits well into corporate development models because
it can be extended in the following ways:
? Access coexists with other applications. Companies using Excel or Word find Access easy
to add to existing desktops. Users are comfortable with the Office style user interface,
appreciate the built-in data links between each of the products, and enjoy features like
drag-and-drop. The IT staff can use Automation to add extra capabilities to the exchange
of information between these products.
? Access connects to existing data. Using ODBC technology and ISAM drivers, Access can
import or link to text file data, spreadsheet data, Xbase data, Paradox data, Web pages,
and SQL based data residing on platforms ranging from PC servers to mainframes. Companies
can continue to use data stored in non-Access formats and easily convert such data to
native Access data when required.
? Access uses Basic. Many IT programmers have been writing in some dialect of Basic for
years and find the transition to programming in Access only slightly challenging. Also,
where Visual Basic is already part of an IT department's tool set, Access fits in well
due to its many similarities to and compatibility with VB.
Access in Small Businesses
Access is best suited for small businesses. Microsoft had this market in mind when they
started creating wizards in the Office product line. Because this market is comprised of
people short on both time and money, they will not use Access if it cannot solve their
problems quickly and cost effectively.
Many small business owners and managers use Access themselves as a productivity and
decision support tool. Small businesses frequently have only a few computer literate
employees on staff, so the ability of Access to manage a few dozen simultaneous users is
quite adequate. Business owners on a tight budget find that they can learn enough about
Access to produce a simple but effective custom application with a few weeks of training
and a few more weeks of development time.
Of course, a very small businesses may not even need Access for the application
development power it provides. Even without an application and its forms, you can be
productive with Access by entering data into table datasheets, running summary queries,
exporting data to Excel for analysis, and printing reports.
Access at Home
Four years ago, if you thought using Access 1.x at home was like using a sledgehammer to
swat a fly, you were correct. At that time, home PC users lacked sophistication and most
could not grasp the relational data model. Access had only a few wizards and a long
learning curve. Few home PCs had the 16 megabytes of memory and 486 or Pentium processors
that Access demands.
The current home marketplace is quite different. The explosion of multimedia PCs has
given many home PC users more than enough power to run Access. Microsoft Office
Professional, of which Access is a part, is convenient for home users who want to use the
same software at home that they have already learned to use at work.
If you use or intend to use Access at home, you most likely fit into one of two
categories:
? you are a business user bringing Access work home
? you are a home user, who knows Access through your job, and you want your home machine
to resemble your work machine.
It is natural to reason that if Access can manage your business data, it can certainly
handle your personal data as well.
When you create a new database in Access 97 you can select a template for the Database
Wizard to meet your specific purpose. Some of these database templates, such as Book
Collection, Donations, and Household Inventory, are quite obviously designed for home PC
users.
Features of Access 97
? Database Wizard. This can help you create a database to manage home data using a
standard template. The resulting application can then be modified.
? Table Wizard. This steps you through the process of creating commonly used tables and
relationships.
? Form Wizard. This tool saves time by removing most of the tedious form layout work.
? Assistant. The Assistant character answers simple help requests and is designed to
help new users feel less intimidated by the product.
? Import Wizards. Many home users keep their records in products that produce spreadsheet
or text format files. The Import Wizards help you load such data into Access.
? Easy queries. Access 97 has a powerful SQL based query engine, but provides home users
with layers of usability features (query wizards, sortable datasheets, query filters, and
the like) on top of that engine. This enables users to easily ask everyday personal
questions like "What is the oldest bottle of wine in my collection?"
? Macros. Home users often prefer to use macro scripts rather than program in Basic.
? Add-ins. As more copies of Access enter the home market, third parties will produce
additional tools and wizards appropriate for home users.
? Export to Word. Historically, home PC users spend more time in their word processor
than their database software. Access makes copying and merging data to Word easy.
? Publish to the Web. If you maintain a home page on the Internet you can use the new
Internet data publishing features to help translate data into HTML.
? Multi-user. Access makes data available to workgroups of multiple users by providing
built-in record locking. This is available in forms and table datasheets without any
programming.
? Visual Basic for Applications. Access is highly programmable because its VBA language
provides the ability to write custom procedures and because it provides event
notifications that can be detected from code. Also, existing code from Visual Basic or
Excel VBA libraries can be easily ported to Access VBA code libraries.
? Forms. IT groups can create complex entry/edit forms which provide selective access to
records, validation of data, query-by-form capabilities, and spell checking.
? Reports. Corporate managers make many of their daily decisions from reports. Access
lets them use graphical reports and can filter the reports using queries and parameters.
Reports can also be connected to linked external data.
? SQL. Most IT programmers have been exposed to SQL while working on minicomputer or
mainframe databases. They can quickly grasp the query capabilities of Access.
? Intranet capabilities. Access applications can provide users with links to Web pages on
a corporate intranet through hyperlinks on form controls and in table fields.
? Interoperability. Features like Automation from Access to Excel and Word or the new
Publish to the Web Wizard give users flexibility when they publish and report company
data. Interoperability is covered in more depth in the following section.
Integrating Access with Other Applications
Microsoft Office
Access is an excellent tool for multifaceted solutions that involve integration with
other Microsoft applications. Access 97 communicates better than ever with its siblings
in Microsoft Office because of the following features:
? Drag-and-drop. You can drag-and-drop form data, cells from a table datasheet, and
entire table and query objects into Excel worksheets and Word documents. Conversely, you
can drag-and-drop Excel cells into Access to create a new table. You can also drop
Access objects onto the Windows desktop to create shortcuts to databases.
? Save as Rich Text Format. You can save the output of a table datasheet, a form, or a
report as a Rich Text Format (RTF) file that can be loaded into Word with the formatting
preserved.
? Mail Merge Wizard. Using this wizard you can link a Word mail merge document to data
in Access and retrieve the latest data from Access whenever you print your Word merge
document.
? Save as an Excel worksheet. You can save the output of a table datasheet, a form, or a
report as an Excel file with the formatting preserved.
? Excel AccessLinks. The AccessLinks add-in program in Excel lets you create Access
forms and reports using data in Excel and export data from Excel into Access tables.
? E-mail attachments. Using the SendObject macro action or File Send... menu selection,
you can attach an Access datasheet, form, report, or module to an e-mail message as a
Rich Text Format file, an Excel worksheet, or a text file.
? Common interface elements. The new Office 97 Assistant and Command Bar features
provide a common set of user interface construction tools.
Access and Visual Basic Together
With Access 2.0, a significant two-way migration of developers occurred. Many Access
developers realized that the investment they had made in learning Access Basic enabled
them to learn Visual Basic more easily and added another powerful product to their skill
set. From the other direction, most Visual Basic programmers adopted the Jet Database
Engine as their preferred file-server database technology and adopted Access to create
their database structures, queries, and reports.
Thus, many Access developers became Visual Basic developers, and the reverse. This trend
will only accelerate with the 97 versions of these products. The following three key
areas help illustrate this point:
? Visual Basic for Applications. Both Visual Basic 5 and Access 97 utilize the same
programming language engine. Program code developed in either environment can be easily
ported to the other. The benefits include the following:
? You can create one common code library with procedures that work in both environments.
? Developers can be trained in one language and use it in multiple products, including
Access 97, Excel 97, Project 97, PowerPoint 97, Visual Basic 5, and Word 97.
? You can quickly prototype applications destined for Visual Basic 5 in Access 97 using
the Table and Form Wizards and some simple navigation code, then preserve any VBA code
when moving it over to VB 5.
? Automation. The OLE communication wire between Access 97 and Visual Basic 5 runs in
both directions:
? You can use Visual Basic 5 to drive Access 97 as an Automation server for editing table
data or printing database reports from within a VB 5 application.
? You can create applications in VB 5 that are specifically designed to be OLE servers to
Access 97, enhancing the capabilities of Access 97 while providing the faster performance
of a compiled application.
? You can build ActiveX controls in Visual Basic or Visual C++, or buy them and use the
same control and code to extend both Access 97 and VB 5. Both products are host
containers for ActiveX controls (OCX files).
? Jet Database Engine. Visual Basic 5 makes even broader use of Jet through the same
Data Access Objects coding language as Access 97 uses. More and more developers will
create multifaceted solutions that use both Access 97 and VB 5 with the same back-end
database in Jet.
Conclusion
Microsoft uses continuous user-driven research programs to gain insight on how customers
use Microsoft Access and how it could be improved. Based on extensive research, Microsoft
has designed Access for Windows 95 around the following design goals:
? Make it easier for people to get their work done using a database
? Strengthen integration with Microsoft Office applications
? Provide greater flexibility to a broad range of computer users
? Make it easier for developers to create custom database solutions
The result is that Microsoft Access for Windows 95 is the easiest to use and most
integrated desktop database available. It includes innovative technologies that provide
all types of users with compelling reasons to make Microsoft Access a standard part of
their business computing desktops.
Glossary
ActiveX Microsoft's answer to Java. ActiveX is a stripped down implementation of OLE
designed to run over slow Internet links.
API Application Program Interface. The interface (calling conventions) by which an
application program accesses operating system and other services. An API is defined at
source code level and provides a level of abstraction between the application and the
kernel (or other privileged utilities) to ensure the portability of the code.
DDE Dynamic Data Exchange. A Microsoft Windows 3 hotlink protocol that allows
application programs to communicate using a client-server model. Whenever the server (or
"publisher") modifies part of a document which is being shared via DDE, one or more
clients ("subscribers") are informed and include the modification in the copy of the data
on which they are working.
DLL Dynamically Linked Library. A library which is linked to application programs when
they are loaded or run rather than as the final phase of compilation. This means that the
same block of library code can be shared between several tasks rather than each task
containing copies of the routines it uses.
GUI Graphical User Interface.
HTML Hyper Text Markup Language.
ISAM Indexed Sequential Access Method. File access method supporting both sequential and
indexed access.
IT Information Technology.
OCX OLE custom controls. An Object Linking and Embedding (OLE) custom control allowing
infinite extension of the Microsoft Access control set.
ODBC Open DataBase Connectivity. A standard for accessing different database systems.
There are interfaces for Visual Basic, Visual C++, SQL and the ODBC driver pack contains
drivers for the Access, Paradox, dBase, Text, Excel and Btrieve databases.
OLE Object Linking and Embedding. A distributed object system and protocol from
Microsoft.
RAD Rapid Application Development
|