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 BUSINESSES11
ACCESS IN SMALL BUSINESSES 13
ACCESS AT HOME 13
FEATURES OF ACCESS 97 15
INTEGRATING ACCESS WITH OTHER APPLICATIONS17
MICROSOFT OFFICE 17
ACCESS AND VISUAL BASIC TOGETHER 18
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.
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
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.
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.
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
RTF Rich Text Format. An interchange format from Microsoft for exchange of documents between Word and ot
Source: Essay UK - http://www.essay.uk.com/coursework/microsoft-access-an-overview.php