CHAPTER 1
AN INTRODUCTION TO ACCESS 2010
Introduced in the early 1990s, Microsoft Access is a fully functional RDBMS (Relational Database Management System) that is one of the most powerful, and sought-after programs in the Microsoft Office suite of applications. Part of Microsoft Office 2010 Professional, Access provides both beginning and professional database developers alike a cost-effective way to leverage key database functionality with an easy-to-use graphical interface.
As of this writing, Microsoft allows you to download a free 60-day trial of Microsoft Office 2010 from this site: http://www20.buyoffice.microsoft.com. Keep in mind that this is a really large download at almost 700 megabytes, so you’ll want to ensure you download it with a fast Internet connection such as with cable modem or DSL.
The purpose of this chapter is to present Microsoft Access, its requirements and limitations, decide when to choose Access over Excel, learn how to work with older Access versions and what’s new. Though this book assumes you have some familiarity with Access, you need not be an Access expert. If you are new to Access, or databases in general, I strongly suggest reading Chapter 2, “Access Essentials,” where I’ll teach you the beginning database skills required to design and build a database for data entry, storage, and retrieval. These database essentials will be critical to proceed through the remainder of book, where I’ll teach you how to unlock the power of Access through VBA programming!
WHAT IS MICROSOFT ACCESS?
Microsoft Access is a database, and in its rawest digital form, a database is simply a collection of data stored in a file for future retrieval and analysis. Modern databases such as Access have mechanisms for storing large amounts of data in a structured form, and allow for many users to access the same database and even the same data at the same time. Databases such as Access have many other features that include graphical interfaces for end users, reporting and query tools, security management controls, native programming support (e.g., VBA), and application interfaces to connect to other programs such as web and application servers. Microsoft Access does all of this and much more!
Because of its low cost and user-friendly interface, Microsoft Access has become quite popular for teaching beginning database skills, and is just as popular in many a company’s back office operations for building quick and relatively simple software applications, which store information for future analysis, automate repetitive tasks, and provide intuitive graphical interfaces for data management.
ACCESS VS. EXCEL
Whether in school, at the office, or at home, you likely have encountered a spreadsheet tool called Excel, Microsoft Office’s other data capture and analysis tool. Though both applications look quite different, they both provide similar functionalities to solve similar problems with each having its own distinct set of capabilities and advantages. Knowing when to use one over the other can be critical to the success of your solution. Let’s take a look at what Access and Excel have in common, as seen in the bulleted list.
• Available in Microsoft Office Professional 2010
• Stores information in fields by data type
• Ability to create interactive graphical user interfaces to capture data entered by end users
• Work with data using different views
• Analyze data using filters, queries, and reports
• Import and export data from other applications and sources
• Use web pages to access data
• Ability to uniquely identify data through a primary key
• Security features such as encryption, read-only access, and password management
Access 2010 is sold by itself, or as part of the Professional suite of Microsoft Office 2010.
We see from the aforementioned list that Access and Excel can both be used to capture, store, manage, and analyze data from end users and other sources. The key difference in which application to use comes down to answering some important questions.
• Does the solution require multiuser access?
• Do you require multiple tables to store your data?
• Do you have a lot of data, especially non-numeric data to store?
Answering yes to any of the previous questions means Access is most likely the best solution. Access is a fully functional relational database that allows the creation and management of multiple tables storing large quantities of data with multiuser access. On the other hand, Excel may be a better solution if your requirements call for non-relational data (a single table), your data is numeric oriented, and/or you anticipate the need for heavy and frequent statistical computations. If you do select to use Excel for data management today, and later realize your data and/or user-access requirements have changed, don’t worry; Access has you covered with its easy-to-use data import features specifically designed for moving data from Excel to Access.
MICROSOFT ACCESS 2010 LIMITATIONS
Microsoft Access is a fully functioning RDBMS with many rich and power features, and will satisfy most small to intermediate database requirements. To aid you in your evaluation of Access as a database solution, Tables 1.1 through 1.5 describe the boundaries of Access 2010.
TABLE 1.1 ACCESS 2010 GENERAL LIMITATIONS
TABLE 1.2 ACCESS 2010 TABLE LIMITATIONS
TABLE 1.3 ACCESS 2010 QUERY LIMITATIONS
TABLE 1.4 ACCESS 2010 FORM AND REPORT LIMITATIONS
TABLE 1.5 ACCESS 2010 MACRO LIMITATIONS
SYSTEM REQUIREMENTS
Whether you will be installing Access 2010 by itself, or as part of the Microsoft 2010 Professional Office suite, you’ll need to be aware that system requirements have increased significantly since Access version 2003. System requirements for installing Access 2010 by itself are described in Table 1.6.
System requirements increase slightly if you plan on installing the entire Microsoft Office Professional 2010 suite, as detailed in Table 1.7.
TABLE 1.6 MICROSOFT ACCESS 2010 SYSTEM REQUIREMENTS
TABLE 1.7 MICROSOFT OFFICE PROFESSIONAL 2010 SYSTEM REQUIREMENTS
WORKING WITH OLDER DATABASE FORMATS
Support for backward compatibility continues with Access 2010, allowing you to easily open and edit older Access databases in the legacy MDB file format (i.e., Access versions 2000–2003). Access 2007 and 2010 use the new 2010 ACCDB database file format so there is no need to convert an Access 2007 database to Access 2010.
You can convert legacy Access databases in the MDB format to Access 2010 using the Save & Publish feature from the File menu. Note—you are not required to convert legacy MDB databases to edit and save them, but you will not be able to utilize Access 2010’s new features such as multivalued fields and data macros until you have converted to the ACCDB file format, which has been customized to implement these and many more new features.
If your users will continue to use older versions of Access, you may want to keep your legacy database in the MDB format, as legacy Access users will be unable to open an Access 2010 database in the ACCDB format. Figure 1.1 demonstrates the available Save & Publish database options in Access 2010.
FIGURE 1.1 Converting an earlier version of Access to the ACCDB format.
After you have successfully converted a legacy Access database to the new Access 2007/2010 ACCDB file format, Access will confirm the upgrade as seen in Figure 1.2.
FIGURE 1.2 Confirming a successful database upgrade.
WHAT’S NEW IN ACCESS 2010
It’s an exciting time to contemplate upgrading your Access database and/or VBA programming skills. As an author, college instructor, and information technology professional, I’ve been working with Microsoft Access databases for quite some time, and I have to say Access 2010 has matured with a number of enhancements that even the most seasoned database professional will appreciate, all in a convenient package.
At first glance, the most noticeable change to Microsoft Access is the user interface, which Microsoft really turned on its head beginning with Access 2007. It’s a really dramatic change that you might find unnerving at first, but after working in it for a while, I’m sure you’ll find it quite useful and intuitive, just like I did.
In addition to the enhanced graphical experience, Microsoft has done a great job of pouring lots of innovative features into Access 2010 that should give you, an Access developer, the feeling of driving a more robust database than its predecessors. Application developers, instructors, students, and database hobbyists and enthusiasts will find Access 2010 not only supports most of their requirements, but does so in a professional, insightful, and elegant manner.
Without further ado, let’s now take a trek together through a sampling of Access 2010’s new and enhanced features, including:
• User interface Changes
• New Database File Extensions
• New Templates
• Improved Object Design Tools
• New Data Types and Controls
• Enhanced Data Formatting and Presentation
• Improved Help
User Interface Changes
Microsoft has done a great job ensuring the user experience is similar across its most common Office applications (Word, Excel, PowerPoint, Outlook, and of course Access), using a new and common graphical interface. After launching Access (reference Figure 1.3), you can open an existing database, create a new (also known as “blank”) database, or use one of Microsoft’s many templates to get started.
FIGURE 1.3 Microsoft Access 2010.
Figure 1.4 depicts a newly created, and empty, database. One of the most visibly different interface changes beginning with Access 2007 and carried into Access 2010, is the new graphical dashboard at the top of the window called the Ribbon.
The Ribbon moves previously embedded commands from menus to a rich new tabular design. For example, you need only to click on the Create tab to view options for building forms and reports, or click on the Home tab to sort, filter, and find records.
Microsoft’s intention of the Ribbon is to allow Office users to focus on what they want to do through an easily seen common set of operations. Of course, individual experiences may vary depending on how long it takes you to acclimate to the sleek new design, but as I’ve found and I’m sure you will as well, the Ribbon does what it intends to do, which is to get rid of the clunky floating windows, multiple menus, and toolbars of old.
New to Access 2010 is the Backstage view where you can find commands that apply to the entire database such as database properties and permissions. The Backstage view is accessed by clicking the File tab; looking at Figure 1.5, you can see that Backstage view also allows you to open, close, and save databases.
The File menu and the Backstage view replace the Office button used in Office 2007 applications. Also worth noting is the new Navigation pane that lists all of your database objects and the Tabbed documents window that allows you to visually organize your tables, queries, forms, reports, and macros. Both the Navigation pane and Tabbed documents window are shown in Figure 1.6.
Note that if the Navigation pane is collapsed you will need to expand it or resize it with your mouse.
FIGURE 1.5 The new Backstage view.
FIGURE 1.6 The Navigation pane and Tabbed documents window.
New Database File Extensions
Beginning with Access 2007, new database file extensions were created to hold many new enhancements and services. Microsoft Access 2010 uses and improves upon these new file extensions, and by default creates new databases in the Access 2007 file format.
Access databases older than Access version 2007 cannot open the 2007/2010 database file extensions.
The next bulleted list summarizes the new Access database file formats.
• ACCDB—The new Access file format, which replaces the MDB file extension.
• ACCDE—Access execute-only file, which replaces the MDE file extension. ACCDE files have all VBA code removed allowing a user to execute the code, but not modify it. To create an ACCDE file in Access 2010, click the File tab, then click Save & Publish, and then under Save Database As, click Make ACCDE.
• ACCDT—Access database template extension.
• ACCDR—A new file extension that enables a database to open in runtime mode. Created by simply changing the database’s file extension from ACCDB to ACCDR, the new file extension allows for a locked-down version of your database.
• LACCDB—The locking file created when you open an Access 2010 database.
• MDW—A workgroup information file for secured databases.
New Templates
A definitive head start for Access application developers is Microsoft’s set of Access database templates, shown in Figure 1.7, that include professionally developed out-of-the-box tables, forms, queries, and reports. Each template is a complete application that will give you a head start with database development by leveraging them as is, or modifying them to suit your needs. Templates are leveraged by clicking the Sample templates icon (refer to Figure 1.3) when creating a new database.
FIGURE 1.7 Out-of-the-box professionally developed templates.
Remember to select the Sample templates icon (refer to Figure 1.3) in the Available Templates area to see the full list of sample Access 2010 templates.
Featured Access 2010 database templates include:
• Assets Web Database
• Charitable Contributions Web Database
• Contacts Web Database
• Events
• Faculty
• Issues Web Database
• Marketing Projects
• Northwind
• Projects Web Database
• Sales Pipeline
• Students
• Tasks
Improved Object Design Tools
The Create tab in the Ribbon allows you to easily build new tables, forms, reports, queries and other Access objects. Moreover, by simply selecting a database object such as a table or query, and clicking either the Report or Form icon on the Create tab, Access creates a ready-to-use and fully functional new Report or Form based on your selected object, all in a single click!
Introduced in Access 2007, the Datasheet view allows you to easily enter new columns as seen in Figure 1.8, as well as update and insert data directly into the table. You can use the Fields tab in the Ribbon to manage the table properties including size, data type, expressions, and default values to name only a few.
FIGURE 1.8 Working in the Datasheet view.
Sorting and filtering capabilities are accessible via menus that become available when right-clicking a column’s value, as shown in Figure 1.9.
FIGURE 1.9 Filtering rows in the Datasheet view.
Enhanced in Access 2010, the new Layout view allows you to make changes to a live form or report through various formatting tools and/or by dragging and dropping fields from the Field List window as seen in Figure 1.10.
FIGURE 1.10 Developing forms and reports real-time in Layout view
I really like the improved Layout view as it gives you a WYSIWYG (What You See Is What You Get) feeling during report and form development that’s not available during Design view.
New Data Types and Controls
If you’re upgrading from a legacy (MDB) Access database, pay special note to the enhanced data types and controls: The multivalued and attachments field types, and the interactive date picker control. These updated data types and controls are a longed-for set of additions to Access databases, which I’m sure both you and your Access users will appreciate.
Let’s first take a look at the enhanced multivalued field. Multivalued fields provide the flexibility to select and store multiple values in a single field. Look at Figure 1.11 where I’ve added a multivalued field Location_Available to a table called Majors, which allows both the selection and storage of multiple values in a single field.
Typically multivalued field functionality isn’t possible without creating complex many-to-many table relationships. The multivalued field doesn’t actually store multiple values for a single field, but rather stores the multiple values separately in system tables, and brings them back as necessary for graphical display. Multivalued fields are created by selecting the Lookup Wizard as the data type.
FIGURE 1.11 Using multivalued fields to add and store multiple values to a single field.
Also updated for Access 2010 is the Attachment data type that allows you to store both textual and binary files such as digital pictures, documents, or movies to a row (record) in your database table. For example, perhaps you want to add a field to your table that will store a digital photo for each record (i.e., a record of students, teachers, employees, cars, homes, books, etc.). Access 2010 also allows you to store multiple attachments to a record, which Access compresses to minimize disk space requirements.
The interactive calendar icon, called the date picker control, has been enhanced in Access 2010 to add the interface calendar to fields and controls that use the Date/Time data type. As seen in Figure 1.12, the date picker allows you to interactively traverse through a calendar to look up and select date values.
FIGURE 1.12 Using the date picker to look up and select calendar date values.
The date picker can be turned off in Design view by selecting Never for the Show Date Picker property.
New to Access 2010 is the Calculated data type, which allows you to create a field based on expressions from other fields in the same table. For example, the next line of code demonstrates an expression that uses two existing fields to display an employee’s first and last name separated by a space.
=[EmployeeFirstName] & “ ” & [EmployeeLastName]
Like Multivalued fields, Calculated fields save you time! Specifically, Calculated fields allow you to create a field type based on an Access expression that shows the expression’s value when the data is displayed. Let’s take a look at another example, say you have a table called Students that stores grade information, and want to see a “Yes/No” value for each student who is passing/not passing based on a 2.0 GPA or higher standard. To accomplish this, I’ll create a Calculated field in my Students table called Passing, and assign an expression to it as seen in Figure 1.13.
FIGURE 1.13 Create a Calculated field using the Expression Builder.
Once the expression has been created, and the table saved, I can view the Calculated field’s values in Datasheet view, as demonstrated in Figure 1.14.
FIGURE 1.14 Viewing a Calculated field’s value with Datasheet view.
Enhanced Data Formatting and Presentation
Some of the friendlier and visually stirring enhancements with Access 2010 deal with how it lets you format, sort, filter, and present your data. For example, Access 2010 provides common sorting and filtering options for your data through easily accessed menu commands. Simply right-click a field in Datasheet view that you want to filter by, and Access will provide the filtering and sorting options based on the field type selected. Filtering options are included for text, numeric, and date information as revealed in Figures 1.5, 1.6, and 1.7, respectively.
FIGURE 1.15 Sorting and filtering options for text fields.
FIGURE 1.16 Sorting and filtering options for Numeric fields.
FIGURE 1.17 Sorting and filtering options for Date fields
Access 2010 has some really cool data presentation options, including one that allows you to change the default alternating color in the Datasheet view to any color. You can easily do this by clicking the Alternating Row Color icon in the Text Formatting area of the Home tab, as seen in Figure 1.18.
FIGURE 1.18 Changing the default alternating row color.
Another exciting new data presentation feature in Access 2010 is the ability to perform conditional formatting in your forms and reports. For example, let’s say you would like a visual cue to alert you of products having a quantity of less than 15 in stock, as demonstrated in Figure 1.19.
FIGURE 1.19 Using conditional formatting.
The obvious benefit of conditional formatting is that it provides a visual cue to data patterns and/or interactions that otherwise may not be so obvious. You can add a conditional formatting expression to controls on a form or report in Layout view by clicking the Conditional Formatting button in the Control Formatting area of the Format tab, as seen in Figure 1.20.
FIGURE 1.20 Applying conditional formatting during Layout view.
Improved Help
Another improvement that began with Access 2007, and carried over into Access 2010 is the merging of end-user help with the VBA developers help, into a single Help viewer, as shown in Figure 1.21. You can filter your help searches to Access Help, Developer Reference, or All Access by simply clicking the down arrow in the Search icon. This is certainly a welcomed change that eliminates switching back and forth between multiple help windows to research a question.
FIGURE 1.21 Access 2010 Help view.
SUMMARY
• A database is a collection of data stored in a file for future retrieval and analysis.
• Modern database programs often have features that include graphical interfaces, reporting and query tools, security management controls, native programming support (e.g., VBA), and application interfaces to connect to other programs.
• Access 2010 allows you to convert older versions of Access databases to the new file format (ACCDB) and save new Access 2010 databases to an older Access format (MDB).
• The Access 2010 database system comes with Microsoft Office 2010 Professional, or can be purchased alone.
• System requirements for Access 2010 and Microsoft Office 2010 Professional have increased in CPU, memory, and disk necessities.
• Part of the new Office Access 2010 user interface improvements, the Ribbon moves previously embedded commands from menus to a rich new tabular design.
• A number of free database templates come with Access 2010, which include professionally developed out-of-the-box tables, forms, queries, and reports.
• Sorting and filtering has been greatly enhanced with the new Datasheet view.
• The date picker, available in Datasheet view, allows you to interactively traverse through a calendar to look up and select date values.
• The Memo data type now includes support for rich text.
• Multivalued fields allow you to store multiple values for a single field.
• The new Attachment data type allows you to store both textual and binary files.
• Access 2010 continues using new file extensions created in Access 2007 including: ACCDB, ACCDE, ACCDT, ACCDR, LACCDB, and MDW.
• End-user help and VBA development support have been merged into one Help viewer.