Exploring the Access Database Window
In this chapter
- The Database window
- A quick introduction to Access objects
- Organizing objects
In Chapter 2, "Take a Quick Tour of Access," you learned about some of the Access user interfaces. So far, you've learned about the edges of the screenthe task panes, toolbars, and menus. Now it's time to tackle the core of Access itself: the Database window. As you'll learn in this chapter, the Database window is the place where you can find everything you'll need in Accessdata, forms, reports, and other objects. You'll learn how to navigate in this window and see how to start customizing objects for your own use.
What's in the Database Window?
The easiest way to learn what's in the Database window is to open an Access database and explore. Fortunately, Access comes with several sample databases you can use for just this purpose. You don't have to worry about breaking anything because you can always reinstall the samples if a problem occurs. Now that you know how to launch Access (refer to Chapter 2 if you need a refresher), here's how to open the sample database called Northwind Traders:
Select Help, Sample Databases, Northwind Sample Database.
If you didn't install the samples when you installed Access, you'll be prompted to insert your Office CD-ROM the first time you try to open one of the sample databases.
Depending on how your computer is configured, you might need to respond to two separate warnings after selecting the first database to load. First, you may see a warning that states "Security Warning: Unsafe expressions are not blocked." This is Access's way of informing you that there's a potential bug in the Microsoft Jet database engine, which is used by Access. To be safe from this bug, you need to install Service Pack 7 (or a later version, when one comes out) for Jet 4.0. The easiest way to do this is to visit the Windows Update Web site at http://windowsupdate.microsoft.com, where Jet 4.0 SP7 will be one of your choices to download and install.
Installing SP7 will keep this warning from appearing in the future. In the mean time, it's safe to click the Yes button to open the Northwind sample database, because it comes from a trusted source. But beware of blindly clicking Yes for every database, especially if you're not sure where it came from or who created it.
After you deal with the first security warning (if it appears), you'll get a second security warning. This one will tell you "This file may not be safe if it contains code that was intended to harm your computer." The issue here is that Access databases can contain code, and if a nasty person wrote that code, it could be designed to destroy or damage the files on your computer. Because the Northwind file is from a trusted source, it's safe to click Open here. But once again, be cautious: A database that was just emailed to you or downloaded from a Web site could contain malicious code. It's up to you to decide whether to trust the sender.
When the Northwind database loads, you are presented with the splash screen shown in Figure 3.1. If you've seen this screen often enough, you can check the check box to make it go away. For now, just click the OK button and it will vanish. A splash screen is really just a programming trick that displays information about the application that's loading. It keeps you from getting bored while Access loads the database.
After you dismiss the splash screen, your Access window contains two things, as shown in Figure 3.2. In the front is the Main Switchboard, which is actually an Access form designed to let you perform various functions in this sample database. You'll learn a bit about creating your own switchboard forms in Chapter 15, "Automating Your Database." For now, click the Close button (the X in the upper-right corner of the Main Switchboard) to make it go away. Behind the Main Switchboard is the Database window for this database.
Figure 3.1 The Northwind database splash screen.
Figure 3.2 The Northwind database Main Switchboard and Database window.
Close the Main Switchboard form, and you're left with only the Database window. But there's a lot going on in this window! Here's a rundown of the parts of the Database window (Figure 3.3 shows where these items are located):
Shortcut buttonsGive you one-click access to common operations. The exact shortcut buttons available change depending on the type of object selected in the database.
Delete buttonDeletes the object highlighted in the object list. Fortunately, it asks you to confirm before it actually deletes anything.
View buttonsLet you choose between large icon, small icon, list, and detail view in the object list.
Objects buttonOpens the list of object types in the object bar at the left side of the window.
ShortcutsSelect the type of object to display in the object list. For example, in Figure 3.3, the Tables shortcut is selected, so the objects showing in the object list are all tables.
Groups buttonOpens the list of groups in the object bar at the left side of the window. You'll learn more about groups later in this chapter.
Design shortcutsGives you easy access to ways to create new objects.
Object listShows all objects of the selected type. For example, with the Tables shortcut selected, you can see that the Northwind sample database contains a table named Categories, a table named Customers, and so on. Each of these is called a table object.
Because a database stores all your data in one place, you need to be especially cautious with database files. Access stores all your data in a single file with the extension .mdb. You therefore should make a backup copy of this file on a regular basis by copying it to a disk, a CD-ROM, or another computer. Access 2003 provides a new feature for backing up the current database. Choose Back Up Database from the File menu. Then, tell Access where to save the backup if other than the current folder, and click Save. By default, Access will add the backup date to the backup file's name.
Take a moment and click a few of the shortcuts in the Northwind sample database. You'll see that dozens of objects of various types appear in this database. But you'll recall that when you make a new database, you're prompted to save only a single file. What's going on here?
The answer is that Access acts like its own little directory of files. When you look at an Access database in Windows Explorer, you can't see into it; it's just one big file. But when you open that database in Access itself, Access knows how to extract all the little files and display them by name in the Database window. Compared to some older databases, this is immensely convenient. To give someone an Access database, you just have to copy the one file, instead of rounding up dozens of little files (and probably missing some).
Before digging into the actual objectsthe tables, queries, forms, reports, and so onyou might want to change slightly the way things look in the Database window. If you increase the size of the window vertically (click the cursor on the bottom border of the window and drag down), it will eventually be tall enough to show all the shortcuts simultaneously. Click the Details view button to get more information on each object in the object list (although you'll see fewer objects at one time). Figure 3.4 shows the resized and restyled Database window.
Figure 3.3 The Database window in the Northwind database.
Any Access database can contain seven types of objects (although it's possible that any of these types contain no objects):
Next, let's take a quick look at each of these objects. Don't worry, you'll learn about each one in more detail later in the book. For now, just concentrate on getting an overview of what the database contains.
Figure 3.4 Another view of the Database window in the Northwind database.
Tables are where the data in a database is actually stored. The Northwind sample database has tables named Categories, Customers, Employees, and so on.
The Northwind sample database uses natural naming. That is, objects have names that identify what they contain or do. Some database designers advocate a more formal, program-like style for naming objects. You can view one such naming convention at http://msdn.microsoft.com/archive/en-us/dnaraccgen/html/msdn_20naming.asp. For your own databases, feel free to name objects in any way you feel comfortable with.
To see the data in a table, just double-click the table in the Database window. For example, Figure 3.5 shows the data in the Orders table (you can see it identified in the caption area of the window displaying the data). Click the Close button at the upper-right of the table window (not the main Access window!) to exit the table when you're done inspecting it.
If you right-click a table item in the Database window, you'll get a shortcut menu of things you can do with the table. This shortcut menu varies depending on which type of object you've clicked, but some choices are the same. Right-click and select Properties to open the Properties dialog box for the table. Here you can type a description that will appear in the Database window when you're displaying objects in detail view.
You'll learn more about tables starting in Chapter 5, "Building Your First Tables."
Figure 3.5 A table opened from the Database window.
Click the Queries object type (also known as a shortcut) button to see a list of queries in the Database window. Of course, you know that in plain English, queries are questions. But in the world of Access, it's easier to think of queries as the answers to questions.
For example, the Northwind sample database contains information on a fictional food importing company. The tables contain data on the customers of the company, the products the company sells, the employees who sell the products, the orders that customers have placed, and so on. You might think of the tables as a gargantuan filing cabinet filled with sales receipts, employee time cards, and so on.
Faced with this mass of data, you might want to ask some questions, such as, "What were the total sales of all our beverage products in 1997?" If you had the sales slips and a calculator, you could work this out. But in Access, you can use a query to find the answer quickly. Locate the query named Category Sales for 1997 in the Database window and double-click it. You'll see the answers in the form of a datasheet (similar to a table), as shown in Figure 3.6. Close the query window by clicking its Close button when you're done inspecting its contents.
Even though queries look like tables when you open them, there's a difference. You enter data into the database through tables, and then retrieve it (possibly rearranged and summarized) through queries. You'll learn more about queries starting in Chapter 7, "Retrieving Data with Queries."
Figure 3.6 A query opened from the Database window.
Click the Forms shortcut to see a list of forms in the Database window. Real life is full of forms: tax forms, medical insurance forms, rebate forms, and so on. Access forms share some of this fill-in-the-blank aspect, but they also offer additional flexibility that you'll never find in a paper form.
Just like tables and queries, you can open a form by double-clicking it in the Database window. For example, double-click the Employees form in the Database window to open it, as shown in Figure 3.7.
This particular form displays some information about Scott Bishop, a sales representative for Northwind Traders. You can click the Company Info and Personal Info tabs to see two different sets of information.
But this form holds more than just information on Scott Bishop. The set of controls at the bottom of the form is called the navigation bar. These controls let you select different sets of information to display. Click the right-facing arrow on the navigation bar (just to the right of the box containing the numeral 1), and the form shows Andrew Fuller's information. Keep clicking here and you can see every employee in the database. Close the form by clicking its Close button when you're done. In this particular case, the form is drawing its information from the Employees table, which you saw when you clicked the Tables shortcut. But forms need not display any data at all; they can also be a home for Windows controls such as push buttons and list boxes. For example, the Splash Screen (which you saw in Figure 3.1) and the Main Switchboard (which you saw in Figure 3.2) in the Northwind sample database are both forms.
Figure 3.7 A form opened from the Database window.
You'll learn more about forms in Chapter 8, "Creating and Using Data Entry Forms."
Click the Reports shortcut to see a list of reports in the database. Reports provide a different way to display the data contained in tables. Reports are designed to be attractive when they're actually printed, although you can also view them onscreen. For example, locate and double-click the Summary of Sales by Quarter report. The report opens onscreen, as shown in Figure 3.8. If you move your cursor over the report, the cursor turns into a magnifying glass; click the report to see it with larger type.
If you look at the information in this report, you'll see that it consists of summary sales and orders numbers. Once again, Access has compiled this information by aggregating the raw data contained in the tables in the database.
If you have a printer, you can click the Print button on the Print Preview toolbar to get a paper copy of the report. You'll find that the onscreen rendition is almost indistinguishable from the printed version. Use the Close button on the preview window to close the report when you're done investigating it.
You'll learn more about reports in Chapter 9, "Printing Information with Reports."
Figure 3.8 A report opened from the Database window.
Click the Pages shortcut to see a list of pages in the database. Pages are very similar to forms, but there's a twist: They can be displayed in a Web browser as well as in Access itself.
Right-click the Employees page in the Database window and select Web Page Preview. This launches your default Web browser (most likely Internet Explorer) and loads the Employees page. As you can see in Figure 3.9, the page is very similar to the Employees form you saw in Figure 3.7.
Select File, Close in the Web browser to close the page.
If you have a Web site, you can post Access pages to the Internet. Just ask the Office Assistant "How do I publish pages?" for more information. Select Show the Office Assistant from the Help menu to display the Assistant.
Access is the first database to let you so easily move your data to a Web site. You'll learn more about pages in Chapter 10, "Take Your Data to the Web with Pages."
Figure 3.9 A page open in Internet Explorer.
Click the Macros shortcut to see all the macros in the database. Macros are lists of commands that Access can save and execute. For example, a macro might open up a particular form or prompt the user for some information. You'll learn just a bit about macros in Chapter 15. Until then, you won't need to use this object type.
Click the Modules shortcut to see a list of the modules in the database. Modules are objects that actually contain programming code, written in a language called Visual Basic for Applications (VBA). Modules are designed to give advanced users extra power in customizing a database and its contents. We won't be using modules in this book, although you might occasionally see a database that contains a module.