Using the Access 2003 Database Window

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Using the Access 2003 Database Window

Mike Gunderloy and Dana Jones        

 

I AM ONLY KEEPING THIS ARTICLE FOR THOSE OF YOU WHO HAVE NEVER SEEN THE ORIGINAL AND REALLY GOOD DATABASE WINDOW THAT CAME IN ACCESS 2 TO ACCESS 2003. AFTER THAT CAME THE RIBBON AND LHS OBJECT VIEWER.  Garry

The Database Window is the first thing you see when you load an Access database. In this article, Mike Gunderloy and Dana Jones dig into the Database Window interface to introduce you to the many features in Access 2000/2003 and help you work smarter and more efficiently.

ad5468x60

Perhaps it’s been a long while since you really looked at the Access Database Window. Or perhaps you haven’t had the opportunity to look at the Database Window in Access 2000/2003, the turn-of-the-millennium version of Access that Microsoft has just released. In either case, this interface is worth a first (or second) look. The Access developers have managed to pack an awful lot of functionality into those few pixels, including many shortcuts to make your work easier.

In Figure 1, you can see the new look of the Database Window for an Access database (the Northwind sample database from Access 2000, in fact). You can see that there are a lot of changes from the Access 97 look, including the Object bar, a toolbar in the Database Window itself, plus object groups and wizards right in the list of objects. We’ll talk about all of these later in this article. But first, take a look at Figure 2: This is also the Database Window!

19910_MG1 Figure 1

19910_MG2 Figure 2

What appears in Figure 2 shows the Database Window from the NorthwindCS sample Access project that ships with Access 2000. What’s an Access project and how does it differ from an Access database? You’ll recall from past versions that Access has always delivered two programs in one box. Access itself presents the user interface for the database, while the Microsoft Jet Engine provides the underlying database. In Access 2000, Microsoft has taken advantage of this separation to include the SQL Server database engine. An Access 2000 database still uses Access atop Jet to manipulate file-server data, just as all previous versions have done. But an Access 2000 project uses Access atop SQL Server to manipulate client/server data, giving you seamless access to this data while maintaining most of the old familiar rapid application design tools that Access developers have come to know and love.

Microsoft has also included a stripped-down version of SQL Server 7.0 directly in the Office Premier box. Known as the Microsoft Database Engine, or MSDE, you can install this version by running the Setupsql.exe program from the \Sql\X86\Setup folder on the Office Premier CD-ROM. Although this version doesn’t include SQL Enterprise Manager, you can use Access to develop databases to be deployed to production servers later. You can perform some administrative operations (for example, backing up and restoring MSDE databases) from the Tools | Database Utilities menu in Access when you have a project loaded. (See James Biedleman’s article "One Big Step for Access, One Huge Leap Toward SQL Server" in the February 1999 issue of Smart Access for an introduction to Access and SQL Server.)

Database objects

Where Access 95 and Access 97 used a tabbed dialog interface for the Database Window, Access 2000 has introduced the new Object bar. This is an area down the left-hand side of the window that shows the different types of objects (as well as the new object groups, which we’ll discuss later). As you can see from Figure 1 and Figure 2, the Object bar has the advantage of being easily modified to fit different circumstances, unlike the old tabbed interface.

Looking at the types of objects listed in the Object bar, you’ll see some old favorites and some new items. These are all of the objects that you can work with directly using the built-in design tools in Access:

• Tables: Present in both databases and projects, these are the base tables that store all of the data in your database. In projects, tables (as well as views, database diagrams, and stored procedures) are stored in the SQL Server instead of within Jet.

• Queries: Present only in databases, these are client-side queries, stored in Jet. An Access 2000 project has no local query processor, so it has to store all of its queries on the server as either views or stored procedures.

• Forms: Present in both databases and projects, forms represent the user interface of your Access application.

• Reports: Present in both databases and projects, reports are designed to display or print non-editable information to the user.

• Pages: Present in both databases and projects, pages are editable HTML pages bound to data and designed to present information from your database in a Web browser. Figure 3 shows a sample page from the Northwind database. Pages are actually separate files stored outside of Access, so what appears in the Object window are links to the pages.

• Macros: Present in both databases and projects, macros are the "point-and-click" programming interface for Access applications.

• Modules: Present in both databases and projects, modules store Visual Basic for Applications (VBA) code for Access applications. Access 2000 integrates the standard Visual Basic Editor for the first time, enabling all module editing to be done in a separate window.

• Views: Present only in projects, views are the equivalent of queries for SQL Server–stored SQL statements that return sets of records.

• Database Diagrams: Present only in projects, database diagrams are flexible schema diagrams. While an Access database can hold only a single Relationships view, projects can have multiple database diagrams, each focusing on one part of the schema.

• Stored Procedures: Present only in projects, stored procedures are pieces of Transact-SQL code that can be executed. They might or might not return a set of records.

In order to view and work with data access pages either on the Internet or within a company’s intranet, you must have both Microsoft Internet Explorer 5 and a Microsoft Office 2000 license.

19910_MG3 Figure 3

Object shortcuts

Access 2000 now offers a quick way to create objects either by opening a design window or by launching one of the object-creation wizards. To access these object shortcuts, simply click on the object type that you want to create in the Object bar. In the Object window to the right of this bar, you’ll see the new object shortcuts that you can click to launch from the database window. Table 1 lists the object shortcuts that are available in the Database window by default.

Table 1. Available object shortcuts.

Object type

Available shortcuts

Table

Create table in Design view, create table by using wizard, create table by entering data

Query

Create query in Design view, create query by using wizard

View

Create view in designer

Database diagram

Create database diagram in designer

Stored procedure

Create stored procedure in designer

Form

Create form in Design view, create form by using wizard

Report

Create report in Design view, create report by using wizard

Page

Create data access page in Design view, create data access page by using wizard, edit Web page that already exists

Macro

(none)

Module

(none)

The shortcut for editing existing Web pages is particularly interesting. Double-clicking on this shortcut launches the Web page Locate dialog box. You can open either data access pages you’ve already created or other Web pages. If you open a Web page that isn’t a data access page, you can import data to the page and so convert it into a data access page.

In an Access project, those objects that are stored in SQL Server instead of within Access don’t have wizard shortcuts. For example, in an Access project, you couldn’t create a table using the Table wizard. You can still create a new table using Design view in an Access project. Macros and modules don’t have shortcuts.

If you’ve worked with past versions of Access, you know that the default lists of wizards are stored in the Windows Registry and can thus be modified and extended by third-party developers. For example, you could write your own form wizard and have it appear in the list box of choices available when you choose the New Object: Form toolbar button. Unfortunately, the Access developers didn’t make the list of object shortcuts in the Database Window similarly extensible. The choices listed in Table 1 are hard-coded into the Access executable, and that’s the end of it (for this version, at least).

Places to click, things to do

Access 2000 offers more places to click in the Database Window than ever before. With the new Database Window design, for instance, comes a new object group format. Older versions of Access used tabs for each object type. This now-clunky approach has been replaced with the sleeker object button look. Now, all you have to do is click on the object buttons at the left of the Database Window, and all objects of that type are displayed in the Object window, along with the object shortcuts you just read about. You can expand or collapse the Objects button window by clicking on the "Objects" button, as shown in Figure 4.

19910_MG4 Figure 4

 

Below the Object buttons in the Object toolbar is the Groups section. Clicking on the Groups header button allows you to fully or partially collapse the Object group, or completely open the Groups section and close the Objects section as shown in Figure 5 (we’ll return to groups in a moment). For now, we’ll just mention that we think it would be nice to add our own buttons to the button bar, along with "Objects" and "Groups"–but, alas, the Access designers didn’t allow for this possibility. Maybe in the next version.

19910_MG5 Figure 5  
 
19910_MG6 Figure 6

In addition to the Object bar, the Database Window has acquired a second, more conventional toolbar (see Figure 6). Buttons on this bar are enabled and disabled automatically when you click on a button in the Object bar. Icons and button names also change as you click through the Database Window. For instance, if you click on the Forms button in the Object toolbar, the first three buttons on the standard toolbar are "Open," "Design," and "New." If you click on the Reports button in the Object toolbar, the Open button becomes a Preview button, and the icon for the New button changes appropriately.

The fourth button in the toolbar, the Delete button, is disabled if you click on one of the option shortcuts but is enabled for any other object in the Object window.

The next four buttons on the conventional toolbar allow you to choose the view options for the Database Window. You can choose Large Icons, Small Icons, List, or Details (List is the default setting). These settings should be familiar to you from your interaction with Windows Explorer, which has the same options. They are database specific–if you change from List to Large Icons, it applies only to that database, and not to any databases that already exist or that are subsequently created.

Want to customize the Database Window toolbar? Too bad. No matter how much it might look like a standard Access commandbar, it isn’t. This toolbar is just buttons embedded in the Database Window. You can’t remove a button or add a new one. Once again, maybe next time.

Grouping objects

Also new to the Database Window in Access 2000 are groups. Grouping functionality allows the developer to create custom receptacles for related objects. By default, there’s a Favorites group already created. The Favorites group can be used to store links to those forms, tables, and other objects that you (or your users) access most frequently.

To create a group, simply right-click anywhere under the "Groups" header and select New Group from the popup menu. You’ll then be prompted for a name for the group. To add objects to a group, click on the appropriate object type in the Object bar, then right-click on the object you want to add. From the resulting menu, click Add to Group and select the new group. Alternatively, you can simply drag an object from the Database Window and drop it on the group header in the Object bar to add that object to that group. You can add any object to a group, and you can add the same object to an infinite number of groups.

You can also rename or delete a group, or an object within a group, by right-clicking on it and selecting the appropriate option. The only exception is the default Favorites group–you can neither delete nor rename it. Groups follow the same naming rules as other Access objects (up to 64 characters and any combination of letters, numbers, and spaces).

It’s important to note that moving an object into a group doesn’t alter or change the original location of that object; it just creates a link to it. For example, in the Northwind sample database, you can add the Customers table into the Favorites group if that’s a table you use often. If you then click on the Tables object, you’ll see that the Customers table is still listed in the Object window. Similarly, if you delete the Customers table from the Favorites group, it only deletes the link to the table, not the table itself.

Grouping is especially useful for large, complicated databases or projects that contain many related tables, or forms and subforms. If you use a database to keep track of all employee information (stock allocations, addresses, tax deductions, benefits, and so forth), for example, you could create a "New Hire" group that contains all of the tables, queries, and other objects that a user would work with anytime a new employee is hired. This could help eliminate clerical oversight in processing new employees and save time by having all needed tools in one location.

A limitation of the Groups feature is that it has only one level of grouping. This means you can’t create subgroups (for example, a Benefits subgroup of an Employee Info group).

Shortcuts and menus

If you press a key while the Database Window has the focus, you’ll jump to the next object that starts with that letter (for example, in the Tables list of the Northwind sample database, typing "S" repeatedly will cycle between the Shippers and the Suppliers tables). Access 97 worked this way as well. What’s new, though, is that this typing extends to selecting objects that start with the same first letter. For example, in Access 97, if you had a table called "Hires" and typed "SH," you’d select the Hires table because it started with the last letter that you typed. In Access 2000, the Shippers table would be selected, because it starts with "SH." If you really want to select Hires in this scenario, you need to pause long enough after typing the S that Access assumes you’re not continuing the same search.

The shortcut menus in the Database Window have been changed as well. Save As and Export are now two separate items instead of one, eliminating the dialog box that let you choose between the two options. A new Send To option lets you send a database object as an attachment to a mail message. You can also add an object to a group from the shortcut menu.

The shortcut menu for the Database Window itself (available by right-clicking in the blank space on the Object bar or toolbar) has been extended as well. In addition to the group-management shortcuts we’ve already discussed, it has a new jump to the Visual Basic Editor.

Window options

Although most of the Database Window’s behavior is set for you, there are a few options you can change. Choosing the View tab of the Tools, Options dialog box allows you to change the options shown in Table 2.

Table 2. Database Window options.

Option

Effect

Show new object shortcuts

Displays the shortcuts for object creation in the Database Window. This option is on by default. If you turn it off, the Database Window shows only actual objects.

Show hidden objects

Displays hidden objects in the Database Window. This option is off by default. If you turn it on, hidden objects are displayed. Hidden objects can be created by checking the Hidden attribute box on any object’s property sheet.

Show system objects

Displays system objects in the Database Window. This option is off by default. If you turn it on, system objects are displayed. System objects are tables and queries that are used internally by the database engine (Jet or MSDE) to keep the database working and should generally be left alone.

Click options in Database Window

By default, this is set to "Double-click open." In this mode, you select objects by clicking on them, and open objects by double-clicking on them. For a more Web-like experience, select "Single-click open." In this mode, you can select objects by hovering over them and open them by single-clicking on them.

The most interesting of these options is the single-click option. Selecting this mode turns the objects in the Database Window into hyperlinks. If you move the mouse over an object, it turns the default hyperlink color, and clicking on any object opens it in regular (not design) view immediately. If you hover over an object for a short while, the highlight "catches up" and it becomes the selected object. You can still double-click an object if you like, but it opens on the first click and the second click is ignored. Even when you’re in this mode, though, you still need to click an object type on the Object bar to show objects of that type. It would have been more consistent to show the objects when you hovered over the object type. If you allow users to work directly with the Database Window, you might want to check or set this option programmatically. It’s stored in the Windows Registry in the value HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Access\Settings\Database Explorer Click Behavior. Set the data in this value to 0 for the single-click option or to 1 for the double-click option.

All dressed up

Ever run into an old friend when they’re dressed up to go out? That’s what the Database Window in Access 2000 might remind you of. It’s definitely dressier than it was in previous versions of the database. Better yet, the dressiness isn’t just flash. The Access designers worked hard on making the Database Window a useful design center for developers. While there are changes that we’re looking for in the next version, we think that Access’s designers have succeeded better than ever before.

 

Other Pages The You Might Be Interested In

Access Application Development Documentation
Remote Updates of a Back-End Database
Extracting Your Access Configuration
Access 2002 Data Projects for Developers
Breadcrumbs, or How I Learned to Love the Switchboard