David M. Kroenke and David J. Auer
Database Concepts (3
rd
Edition)
APPENDIX D
Getting Started with Microsoft Access 2007
Prepared by
David J. Auer
Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation.
Additional material to accompany:
X
OBJECTIVES
• Understand the Microsoft Access 2007 files formats • Understand the Microsoft Access 2007 user interface • Learn how to create databases in Access 2007 • Learn how to create tables in Access 2007
• Understand Microsoft Access 2007 security options • Learn how to create forms in Access 2007
• Learn how to create reports in Access 2007 • Learn how to create relationships in Access 2007 • Learn how to submit SQL commands in Access 2007
• Learn how to use Access 2003 user-level security in Access 2007
• Learn how to use Access 2007 in Web database processing applications
X
WHY SH
OULD I LEARN TO USE MICROSOFT ACCESS 2007?
icrosoft has introduced Microsoft Office 2007. All of the Office products have been modified with new features and a new user interface. The changes in the user interface are intended to make the Office products easier to use, while the new features extended the functionality of the Office components.
M
Database Concepts (3rd Edition) was written before the introduction of Office 2007, and it is based on MS Access 2003. Since you may choose to or be required to use Access 2007, you should be aware of how it has changed from Access 2003.
X
HOW HAS THE MICROSOFT OFFICE 2007 CHANGED?
For our purposes, the two biggest changes in MS Office 2007 are (1) new file formats and (2) a new user interface.
X
HE MICROSOFT OFFICE 2007 FILE FORMATS
CHANGED?
For MS Access 2007, we can use the file formats shown in Figure D-1 on the next page.
HOW HAVE T
Each MS Office 2007 application has one or more new file formats associated with it1.
1
The older *.mdb file format can be used (and older files with this extension can be opened in Access 2007), but these files will not have the new Access 2007 features— the new features are found in the *.accdb file format.
The *.mdw file format associated with the Workgroup Information Files used to secure Access databases as described in Chapter 6 has not changed. This file format is identical to the format used by previous version of Access, but is only supported for databases saved in the older *.mdb format.
Figure D-1 – MS Access 2007 File Extensions
File Extension Purpose
*.mdb The older Access 2000-2003 file format, still
available for compatibility.
*.adp The older Access projects file format, used
when Access is used as a frontend application to and SQL Server database. Still used in Access 2007 for the same purpose.
*.accdb The new Access 2007 database file format.
*.accdt The new Access 2007 database template file
format.
*.accde The new Access 2007 extension for “execute
only” databases. Visual Basic for Applications (VBA) source code is removed, but the VBA logic can still be executed by the user. Replaces the previously used *.mde file extension.
*.accdr The new Access 2007 extension for locking a
database in “runtime mode,” where it can be used but not modified.
*.mdw The older Access Workgroup Information File
file format, still used in Access 2007 for the same purpose.
The new *.aacdb file format supports several new Access 2007 features. However, none of these new features has any impact on our use of Access in this book. We can do everything we need to do in either the *.mdb or *.accdb file format─with the notable exception of Access 2003 user-level security discussed in Section 6 of The Access Workbench which only works with the *.mdb file format.
X
HOW DOES THE MICROSOFT OFFICE 2007 USER INTERFACE
WORK?
The biggest change in MS Office 2007 is the new user interface, where the familiar menu and toolbar system has been replaced by a set of tabbed ribbons. This can best be explained by seeing it, so we’ll open Access 2007 and take a look. For an easy comparison with Access 2003, we’ll use the same Wallingford Motor CRM example that is used in all sections of The Access Workbench (TAW) in the text with references to the book’s section and page.
Creating the Access Database WMCRM.accdb (Reference TAW Section 1, page 21)
1. Start Microsoft Access 2007. The Microsoft Access 2007 window appears as shown in Figure D-2.
Figure D-2 – The Microsoft Access 2007 Window
Click Blank Database to create a new database
Click a Template icon to create a new database based on a database template
Click More… to select a previously created database
2. Click the New Database icon in the New Blank Database section. The Blank Database pane appears as shown in Figure D-3 on the next page. 3. Type in the database name WMCRM.aacdb in the File Name text box,
and then click the Create button.
NOTE: The database will be created in your My Documents folder. If you want to create the database in a different folder, use the Open button shown in Figure D-3 to browse to the correct location.create an Access project as the user interface to an SQL Se database, name
rver your database with an .adp extension (i.e,
Figure D-3 – The Microsoft Access 2007 Window
WMCRM.adp).
The Blank Database pane
Type in the new database name here, and then click the Create button
4.
ll
click the Close Document button shown in Figure D-4 on the next page.
The new database appears as shown in Figure D-4 on the next page. The Microsoft Access window itself is now named WMCRM: Database (Access 2007) – Microsoft Access to include the database name. Note that since this is a new database, Access 2007 has assumed that you wi want to immediately create a new table. Therefore, a new table named Table1 is displayed in Datasheet view in the Document window. We do not want this table open at this time, so
The Create button
Figure D-4 – The WMCRM: Database (Access 2007) Window
5. The Access 2007 window with the new database appears as shown in Figure D-5. Most of the new features of the Office 2007 can be seen in this window.
Figure D-5 – The Access 2007 Window The Document Window
using the tabbed documents interface Database name WMCRM: Database (Access 2007)
s
THE MICROSOFT OFFICE BUTTON
The Microsoft Office Button essentially replaces the previous menu bar File command. Clicking the Microsoft Office Button now displays the File Menu, as
hown in Figure D-6.
The File Menu contains the familiar New, Open, Save, Save As, Print and Close commands. Moving the mouse point over a command that has options will display those options, as shown in Figure D-7 on the next page. There, the options for the Manage command are displayed.
The File Menu also contains Access Options and Exit Access buttons. Clicking the Access Options displays the Access Options dialog box, as shown in Figure D-8 on the next page. This dialog box is the entry point for controlling many important settings for Access defaults and for individual databases. For example, in the Popular | Creating databases settings shown in Figure D-7 we can set the default database file format for Access 2007 and the default database folder for newly created database files. For another example, referencing the section “Does Not Work with MS Access SQL” on page 103 and the footnote on that page, we can specify the use of SQL 92 instead of the default Access 2007 ANSI-89 SQL by changing the Object Designers |Query design settings SQL Server Compatible Syntax (ANSI 92) check boxes.
Figure D-7 – Command Options in the File Menu
THE QUICK ACCESS TOOLBAR
As shown in Figure D-5, the Quick Access Toolbar is located at the top left corner of the Access window next to the Microsoft Office Button. By default, the Quick Access Toolbar contains three buttons - Save, Undo and Redo. However, the Quick Access Toolbar can be customized.
There are two ways to customize the toolbar. First, we can use the Access Options dialog box discussed in the previous section. In this case, we would use the Customize settings page, which is specifically used for detailed customization of the Quick Action Toolbar.
Alternatively, we can use the drop-down arrow just to the right of the Quick Action Toolbar to display the Customize Quick Access Toolbar Drop-Down List as shown in Figure D-9. By selecting or de-selecting items on this list we can easily modify the icons on the Quick Access Toolbar. Figure D-10 on the next page shows the Quick Access Toolbar with two additional icons added to it–one for Quick Print and one for Print Preview.
If we need more detailed control over the icons on the Quick Access Toolbar, selecting More Commands… on the drop-down list will take us to the Customize settings page of the Access Options dialog box.
Figure D-10 – The Modified Customize Quick Access Toolbar
THE NAVIGATION PANE
The Navigation Pane shown in Figure D-5 is the new interface for database objects, and it replaces the database window shown in Figure AW-1-4 on page 24 in the text. However, the Access 2007 Navigation Pane provides much more control of which objects are displayed. In Figure D-5, only tables and their related views are selected, and there are no such objects shown because this is a new database and we haven’t created any tables or views yet!
The additional icons on the modified Quick Access Toolbar are now checked in the drop-down list The modified Quick Access Toolbar with two additional icons
To see how objects are selected for display in the Navigation Pane, we’ll set it to show the same set of objects shown in the database window in Figure AW-4-4.
Selecting Objects in the Access 2007 Navigation Pane
Figure D-11 – The Navigation Pane Drop-Down List
The
Navigation Pane Drop-Down Arrow
The All Tables Drop-Down List
2. Click the Object Type in the Navigation Pane Drop-Down List, and then click the Navigation Pane drop-down arrow again. The Navigation Pane Drop-Down List now appears as shown in Figure D-12 on the next page. 3. As shown in Figure D-13 on the next page, the Navigation Pane for All
Access Objects is currently empty—we have not created any tables or other objects in the WMCRM database.
Figure D-12 – The All Access Objects Drop-Down List
Figure D-13 – The Empty Navigation Pane Use this button
THE COMMAND TABS AND RIBBONS
The tabbed Office Fluent Ribbon, or just Ribbon, shown in Figure D-5 is the new Access 2007 command interface, and it replaces menus and toolbars. The menu commands of the older Office interface have been replaced by command tabs (and the Microsoft Office Button discussed earlier), and the toolbar tools have been moved onto the associated ribbons as groups of associated commands. Each Office 2007
application has a Home tab, and a set of additional tabs specific to each application. The Access 2007 command tabs are the Home, Create, External Data and Database Tools tabs shown with their related ribbons in Figure D-14. In each ribbon, the currently available commands are shown in color, and the unavailable commands shown in grey. Figure D-14 – The Access 2007 Command Tabs and Ribbons
A – The Home Command Tab and Ribbon
B – The Create Command Tab and Ribbon
C – The External Data Command Tab and Ribbon
THE CONTEXTUAL COMMAND TABS AND RIBBONS
In addition to the basic command tabs and ribbons, additional contextual command tabs and their associated ribbons will be displayed as needed depending on which object we are working with. Since this is easiest to understand by seeing the contextual command tabs and ribbon in action, we’ll continue creating the WMCRM database by creating the CUSTOMER table. The column characteristics for the CUSTOMER table are shown in Figure AW-1-6 on page 25 in the text.
Creating the CUSTOMER Table (Reference TAW Section 1, Page 26)
1. Click the Create command tab to display the Create Ribbon, and then click the Table Design button as shown in Figure D-15,
Figure D-15 – The Table Design Button
2. The Table1 tabbed document window is displayed as shown in Figure D-16.
Click the Table Design button to create a new table in Design View
Figure D-16 – The Navigation Pane Drop-Down List The group of command tabs displayed is named as a set of tabs called Table Tools Table Tools adds only one new command tab—the Design tab with its Ribbon
3. In Figure D-16, we see that a new tabbed document named Table1 is now displayed in the Access 2007 Documents window. At the same time, an additional contextual command tab named Design is being displayed, and the entire set of contextual command tabs is being referred to as Table Tools.
4. If we compare the Table1 window in Figure D-16 to the Table 1 : Table window in Figure AW-1-9 on page 27 in the text, we will discover that they are basically identical. This means that we build a table in Access 2007 just as described in the steps 3 through 8 on page 27. Following those steps, we end up with the table shown in Figure D-17, where the CustomerID field row has been selected after entering the other fields. Figure D-17 – The Table1 Table With Field Data
The Save button The Primary Key button The complete fields—the CustomerID field row has been selected
Setting the CUSTOMER Table Primary Key (Reference TAW Section 1, Page 28)
1. As shown in Figure D-17, we’ve already selected the CustomerID row. To set this field as the primary key, click the Primary Key button on the Design Ribbon.
Naming, Saving, and Closing the CUSTOMER Table (Reference TAW Section 1, Page 29)
3. To close the CUSTOMER table, click the Close button in the upper right corner of the Documents window. The CUSTOMER tabbed document window is closed, and the Table Tools / Design contextual control tab disappears. The CUSTOMER table object remains in the Table objects section of All Access Objects in the Navigation Pane.
Figure D-18 – The CUSTOMER Table and Object
X
WHERE HAVE ALL THE WIZARDS GONE?
The CUSTOMER table object The CUSTOMER table The Document window Close button
The Access Wizards found in previous versions of Access are still available, but they have been augmented by single button actions. Let’s see how this works when we insert data into the CUSTOMER table. The data we need is in Figure AW-1-19 on page 33 in the text. First, we’ll use the Datasheet view to enter data.
Inserting Data into Tables – The Datasheet View (Reference TAW Section 1, Page 32)
1. Click the Home command tab, and then double-click the CUSTOMER table object in the Navigation Pane. The CUSTOMER tabbed document window opens in Datasheet view as shown in Figure D-19 on the next page. Compare this figure to Figure AW-1-20 on page 33 in the text.
Figure D-19 – The CUSTOMER Table in Datasheet View
2. Follow steps 2 through 6 on pages 33-35 in the text to add data to the table. Figure D-20 below uses a minimized Navigation Pane to help display more data.
The group of command tabs displayed is again named as a set of tabs called Table Tools Table Tools adds only one new command tab—the Datasheet tab with its Ribbon
The CUSTOMER tabbed document window in Datasheet view
Figure D-20 – The Completed CUSTOMER Datasheet
The
Navigation Pane has been minimized-click here to restore it
We can delete data rows exactly the same way as described in the text on pages 36-37. Reentering the deleted data results in a new CustomerID number, just as in Access 2003. At this point, we can close the CUSTOMER table.
Now, let’s try creating a form.
Inserting Data into Tables – Using a Form (Reference TAW Section 1, Page 38)
1. Click the Create command tab, expand the Navigation Pane if necessary, and then click the CUSTOMER table object in the Navigation Pane to select it. The colored buttons in the Create Ribbon show all the possible actions at this point, and this is shown in Figure D-21.
Figure D-21 – The Create Ribbon and the Selected CUSTOMER Object
2. Click the Form button. A completely designed form appears, as shown in Figure D-22.
The Form button
Figure D-22 – The CUSTOMER Form
We now have a draft form and are in form Layout view. We can make modifications to the form using the tools provided or by switching to form Design view. We can then save the form and use it.
However, the Form Wizard is still available and we will want to use it. As shown in Figure D-23, it is found on the More Forms drop-down list.
Figure D-23 – The Forms Wizard
Reports work the same way. Clicking the Report button will create an editable report in report Layout view. The Report Wizard works the same way it did in previous versions of Access.
To use the Form Wizard, use the More Forms drop-down list To use the Report Wizard, use the Report Wizard button
In general, we have more control over the basic layout of our forms and reports when using the Access Wizards. Therefore, we recommend using the Form Wizard and Report Wizard as discussed in the text. The steps in the text in The Access Workbench will work as shown. There are some minor changes in the content of the Wizard dialog boxes─for example the default style is named Office instead of Standard─but these do not effect the actions necessary to create the forms and reports.
Closing the WMCRM Database (Reference TAW Section 1, Page 46)
1. To close the WMCRM database, click the Microsoft Office Button and then click Close Database.
X
HOW HAVE MICROSOFT OFFICE SECURITY OPTIONS
CHANGED FOR ACCESS 2007?
Therefore, we will normally enable the features that the Access 2007 security warning is warning us about when we open an existing database. Note that the security options we are discussing at this point are not the database administrator (DBA) security features discussed in Chapter 6 of the text. We will discuss those features later in this Appendix.
Opening the WMCRM Database
1. To open the WMCRM database, click the WMCRM.accdb database in the Open Recent Database pane as shown in Figure D-24.
NOTE: If the database you want isn’t listed in Open Recent Database, click More… and browse to the database.Figure D-24 – Opening an Existing Database
The Open Recent Database Pane
Use More… to browse to other databases not listed
Click WMCRM.accdb to open the database
2. A Security Warning bar appears with the database. Click the Security Warning Options… button to display Microsoft Office Security Options dialog box as shown in Figure D-25 on the next page
NOTE: The Security Warning bar and the Microsoft Office Security Options dialog box replace the Access 2003 Security Warning dialog box discussed in TAW Section 2 Page 75 and shown in Figure AW-2-2 in the text.Figure D-25 – The Microsoft Office Security Options Dialog Box The Security Warning bar The Security Warning Options button The Microsoft Office Security Options dialog box
X
HOW ARE RELATIONSHIPS CREATED AND MAINTAINED?
We’ll take a look at relationships in Access 2007 by creating the relationship between the CUSTOMER and CONTACT tables discussed in Section 2 of The Access
Workbench.
1. Click the Database Tools command tab to display the Database Tools Ribbon shown in Figure D-26.
Figure D-27 – The Relationship Tools Contextual Command Tabs
3. Click the Show Table button to display the Shown Table dialog box as shown in Figure D-28. The Relationship Tools contextual command tabs The Show Table button
Figure D-28 – The Show Table Dialog Box
The Show Table dialog box
4. We are now back to familiar territory, for this is the same dialog box shown in Figure AW-2-11 on page 82 in the text. Steps 2 though 8 on pages 81-83 in the text work exactly the same in Access 2007. Compare Figure D-29 on the next page to Figure AW-2-14 on page 84 in the text. The only difference is that in Access 2007 the relationship diagram appears in a tabbed Relationships document window (Figure D-29) instead of in a separate Relationships window (Figure AW-2-14).
5. Click the Save button on the Quick Access Toolbar to save the changes to the relationship.
Figure D-29 – The Completed Relationship The Relationship Report button The completed relationship in the Relationships document window
Figure D-30 – The Relationship Report The Print Preview command tab and associated ribbon The Close Print Preview button The completed relationship report in the Relationships for WMCRM document window
7. The Relationships for WMCRM document window is selected. Click the Save button on the Quick Access Toolbar to save the Relationships for WMCRM report.
X
HOW DO I CREATE ACCESS 2007 QUERIES
The Access 2007 Query By Example (QBE) and SQL queries work essentially the same as they did in Access 2003. As we have seen with previous Access 2007 tools, the main difference is in using the new user interface to get to the tools. We will illustrate this by:
•
Creating the QBEQuery-AW-3-04 QBE query in Section 3 of The Access•
the SQL statement also shownccess QBE Query with Multiple Tables (Reference TAW Sectio
1.
bed document window appears Workbench, and
Creating the SALESPERSON table using in Section 3 of The Access Workbench.
Creating and Running an A n 3, Pages 155-157)
Click the Create command tab, and then click the Query Design button. As shown in Figure D-31, The Query1 tab
along with open Show Table dialog box.
Figure D-31 – The Query Tools and the Query 1 Document Window
2.
e t d of in a separate Query1 : Select Query window (Figure AW-3-15).
Again, we are now back to familiar territory, and we can now follow steps 3 though 11 on pages 155-156 in the text. Compare Figure D-32 on the next page to Figure AW-3-15 on page 156 in the text. The only differenc is that in Access 2007 the query appears in a tabbed Query1 documen window (Figure D-32) instea
Figure D-32 – The Completed Two Table Query
The Run button
3. Click the Run button on the Design Ribbon to run the query.
4. Click the Save button on the Quick Access Toolbar to save the query. Name the query as QBEQuery-AW-3-04.
5. Click the Close Document button to close the QBEQuery-AW-3-01 document window.
Creating the SALESPERSON Table Using Access SQL (Reference TAW Section 3, Pages 160-161)
1. Click the Create command tab, and then click the Query Design button. The Query1 tabbed document window appears along with the open Show Table dialog box.
2. Click the Show Table dialog box Close button to close the dialog box. 3. Click the SQL View button (shown in Figure D-31) to switch the query to
SQL View.
4. Click the Data Definition button (shown in Figure D-31) to switch the query to a data definition query.
Figure D-33 – The CREATE TABLE SALESPERSON SQL Statement
6. Click the Run button on the Design Ribbon to run the query.
NOTE: If you didn’t select the “Enable this content” security option when you opened the database, this query will not run. An error message will appear in the status bar stating “The action or event has been blocked byDisabled Mode.”
7. Click the Save button on the Quick Access Toolbar to save the query. Name the query as Create-Table-SALESPERSON.
8. Click the Close Document button to close the Create-Table-Salesperson document window.
The rest of the steps following the creation of the SALESPERSON table in Section 3 of The Access Workbench on pages 161 through 173 still have to be completed to
integrate the SALESPERSON table into the WMCRM database. We have already seen the way the new Access 2007 interface works for these actions, so the steps will work as written with only minor modifications to compensate for the new Access 2007 interface.
X
HOW DO I SECURE A DATABASE IN ACCESS 2007?
Access 2007 introduces an entirely new security model that is beyond the scope of this discussion. For more information, use the Access 2007 Help system and read the
Secure an Access 2007 Database topic.
However, Access 2007 still supports user-level security as described in Section 6 of The Access Workbench for databases in the older *.mdb file format. The security settings are still stored in a workgroup information file (*.mdw). All the actions discussed in Section 6 are supported for *.mdb files.
Figure D-34 on the next page shows the WMCRM.mdb in Access 2007. The Database Tools command tab has been selected. Note the appearance of the Users and
Permissions options in the Administer group.
Figure D-34 – The CUSTOMER Table in Datasheet View The User-level Security Wizard The Users and Permissions button
Figure D-35 – The Security Wizard Start Page
X
HOW DO USE AN ACCESS 2007 DATABASE AS AN ODBC
SOURCE?
Web database processing using Access 2007 is the same as described in Section 7 of The Access Workbench. When Access 2007 is installed, the ODBC driver list is updated to include the new *.accdb file format, and all the actions discussed in Section 6 are supported for *.accdb. Figure D-36 shows the new ODBC driver for an ODBC system DSN.
Figure D-36 – Selecting the Microsoft 2007 *.accdb Driver The Access
X
REVIEW QUESTIONS
D.1 What are the two biggest changes in Microsoft Office 2007? D.2 How have Access 2007 file formats changed?
D.3 Based on Figure D-5, what are the main components of Access 2007 user interface? D.4 Describe the function of the Microsoft Office Button.
D.5 Describe the function of the Access 2007 File Menu. D.6 Describe the function of the Access Options dialog box. D.7 Describe the function of the Quick Access Toolbar.
D.8 Describe the function of the Quick Access Toolbar Drop-Down list. D.9 Describe the function of the Access 2007 Navigation Pane.
D.10 Describe how to control the display of objects in the Access 2007 Navigation Pane. D.11 Describe the function of the Access 2007 command tabs.
D.12 Describe the function of the Access 2007 Office Fluent Ribbon. D.13 How many basic command tabs are used in Access 2007?
D.14 Describe the function of the Access 2007 Home command tab and the Home Ribbon. D.15 Describe the function of the Access 2007 Create command tab and the Home Ribbon. D.16 Describe the function of the Access 2007 External Data command tab and the Home
Ribbon.
D.17 Describe the function of the Access 2007 Database command tab and the Home Ribbon.
D.18 What is a contextual command tab? When do contextual command tabs appear in the
Access 2007 interface?
D.23 How are relationships between tables created in Access 2007? D.24 What is a Relationships Report and how do you create one? D.25 How are QBE queries created in Access 2007?
D.26 How are SQL commands created in Access 2007?
D.27 How may some SQL commands be affected if the Microsoft Office Security Options for Access 2007 are enabled?
D.28 When can user-level security be implemented in Access 2007? D.29 How is user-level security implemented in Access 2007?
