Fixing Access Annoyances

Chapter 0. Access Basics

If you’re already an Access ace, you can skip this chapter and move right into the meat of the book. But if you’re still clawing your way up the learning curve, here’s a brief overview of some basic Access concepts and procedures.

The Database Window

When you start Access and create a new database (File → New), you’ll find yourself in the Database window. This is home base when working in Access. A list of the object types in your database appears on the left side of the window. You can click an object name to show objects of that type (of course, there won’t be any until you create some). The key types of objects are:

Tables

Tables are where data gets stored. A table in Access is roughly analogous to a table in Word or a worksheet in Excel—column headings (a.k.a. fields) and rows (a.k.a. records) are the main structural elements. The difference is that in a database, you must define these structures much more precisely. This is the foundation on which everything else rests. (See “Table Design 101” in Chapter 3 for more info.)

Queries

Queries which are based on Structured Query Language (SQL) typically retrieve selected data from tables. They can also be used to modify or delete existing data, add new data, and create new tables. Of course, you could view all of your data just by looking at the table, but the power of a database is that it lets you create alternate views of your data to answer specific questions. For example, your boss may say, “I need a list of all our products with sales over $10,000 per month that come from a single supplier.” You’d go nuts trying to extract this information by looking at the raw data in a table, but you can easily find it with a query. (See “Query Basics” in Chapter 4 for more information.)

Forms

As a general rule, you don’t want users entering data directly into tables; there’s too much potential for error and data damage. The safest and easiest way for users to enter data is via forms. You can use forms to provide clear, user-friendly data entry templates, and with a bit of Visual Basic (VB) code you can add an additional layer of error-checking to prevent erroneous or improperly formatted entries. Forms are also a useful way to browse a database and look up information. If you’ve ever filled out a web form, you’re already familiar with the basic concept behind Access forms: text boxes, checkboxes, and other widgets provide ways for users to input data in a structured way. Access has a Form Wizard that makes it easy to create a basic form for any table. (For more on forms, see Chapter 5.)

Reports

Reports, which are actually based on queries, let you present, summarize, and print your data in various elegant ways. The key to a good report is basing it on a query that selects all the data the report needs from the underlying tables. Access’s Report Wizard makes it easy to group, order, and summarize the data from the query. (We provide lots more information on reports in Chapter 6.)

Pages

Data Access Pages use Microsoft technology designed to let users view and edit Access data on the Web. Unfortunately, the technology doesn’t work very well. At best, the feature is useful for intranets, but we recommend you look into other solutions first. (For more information, see “Putting Data on the Web” in Chapter 3.)

Macros

Much like macros in Word and Excel, Access macros can be used to automate certain tasks. For example, if you import the same worksheet data into a database every week, you can set up a macro that does the job with the click of a button. Unlike Word or Excel macros, however, you cannot create Access macros by recording a series of keystrokes and button clicks. Instead, you must construct a macro explicitly from a list of predefined actions, using the Macro Designer.

Modules

One of the most powerful aspects of Access is that you can write Visual Basic code to do everything you can do with macros, plus a whole lot more. Modules are where you store that Visual Basic code. If you’re not a programmer, don’t worry—you can accomplish a lot in Access without writing any code. But as you become more familiar with Access, you’ll find that even a line or two of VB code can make life a lot easier.

We’ve provided helpful code samples in various chapters, along with an overview titled “How to Create an Event Procedure” that you’ll find later in this chapter.

Wizards

If there’s one secret to becoming instantly productive with Access, it’s the wizards. These step-by-step dialog boxes walk you through many key tasks. Even advanced developers use them, simply because it saves time; the Form, Report, and Mailing Label Wizards are especially useful.

Here’s a list of the main wizards. Unless otherwise noted, these wizards can be activated in the Database window by first selecting the specific type of object you want to create and then clicking the New button:

Form Wizard

This wizard creates a basic, no-frills data entry form for any table. It’s very useful. Power users will probably want to open the resulting form in Design View and tweak it (see Chapter 5), but the wizard gives you a good starting point.

Control wizards

Forms, queries, and reports are based on controls—text boxes, labels, and so on. Access provides several wizards that make it easy to customize these controls and apply the kinds of tweaks that were once the preserve of power users. Here’s where you can make a big improvement in the usability of your forms with surprisingly little effort (see “Activating the Wizards” in Chapter 5).

For example, instead of using a text box in a form where users must enter country names, you could deploy the Combo Box Wizard to create a combo box control, providing a drop-down menu that lets users pick standardized country names from a list stored in a table. This prevents spelling errors and ensures that every user enters the same version of each name. Some other controls that have useful wizards include:

Command buttons

The wizard makes it easy to put a button on your form that can trigger various actions, such as opening another form, running a macro, or saving the current record.

List boxes

List boxes are similar to combo boxes, but instead of seeing a dropdown menu, users see a list of all the items at once.

Option groups

Option groups present a fixed choice (“Yes,” “No,” “Maybe”) as a set of mutually exclusive radio buttons.

Report Wizard

This wizard creates a basic, no-frills report based on a table or query. It’s a good starting point, but you’ll definitely need to go in afterwards and tweak the design. (For details, see Chapter 6.) Since reports are usually based on queries, designing a good report means building the right query first—many headaches in report design are due to so-so queries. Chapter 4 will put you on the path to understanding how to build the query you need.

Mailing Label Wizard

The Mailing Label Wizard has built-in knowledge of the sizes and specifications of many commercial labels and is an indispensable tool for creating mailing labels. Though there are some gotchas (see “Mailing Labels” in Chapter 6), it makes creating labels pretty easy. You can even create custom specifications for odd-sized labels. Since mailing labels are technically a kind of report, you invoke the Mailing Label Wizard from the New Report dialog (click Insert → Report from the Database window).

Chart Wizard

Building an Access chart from scratch can be painful, because there’s no integrated graphical editor; every time you make a change, you must switch views to see the results. Though it’s pretty basic, the Chart Wizard will at least get you started. For solutions to common problems, see “Charts and Graphs” in Chapter 6.

Table Wizard

This wizard gives you some sample prefab tables that you can customize. It’s worth a look, but it’s not especially useful. In most cases you’ll want to build tables by hand. (See Chapter 3 for tips on designing your tables.)

Query wizards

The Simple Query Wizard enables you to build (what else?) a very simple query. This may be useful for absolute beginners, but it won’t help much with most real-life queries. The other query wizards are more useful, but mainly for very specialized needs, such as crosstabs and duplicates queries. You’ll need to design most queries yourself (see Chapter 4).

Design View and User Views

Wizards only take you so far. If you spend much time developing Access databases, most of the time you’ll probably be working in Design View. Every type of object (table, query, report, and so on) has its own Design View. For example, in Table Design View you can define field names and determine what type of data is allowed to go into each field. In form and report Design Views, you’ll tweak the behavior and layout of your controls. Queries have two different Design Views: the Query Builder provides a graphical user interface, while the SQL View lets you write queries in the SQL programming language. You’ll find much more information about these different views in subsequent chapters.

Any view that isn’t a Design View is a user view: user views show your objects as they will appear to users who are entering or viewing data. (Neither macros nor modules have user views—they’re just code that works behind the scenes.) Table 0-1 lists the user views associated with some of the objects you’ll be working with.

Table 0-1. Main user views

Object	Most common user views
Table	Datasheet
Query	Datasheet
Form	Single, Continuous, Datasheet
Report	Print Preview, Layout Preview

Here’s an overview of the main user views:

Datasheet View

When you look directly at the data in a table, Access presents it in Datasheet View, a column and row layout that looks a lot like an Excel worksheet. The columns are the fields, and each row is a record. By default, you get the same view when you look at the results of a query, since a query is basically a recipe for creating a “virtual” (temporary) table (see “Query Basics” in Chapter 4). A form can also be presented in Datasheet View—this is mostly useful for certain kinds of subforms (i.e., forms nested inside other forms). See Chapter 5 for more on subforms.

Single Form View

When you want your form to present one record at a time, use Single Form View; it’s the default.

Continuous Forms View

When you want users to view a full page of records at the same time (this is sort of like looking at search engine results on the Web; you wouldn’t want them presented one hit at a time), use either this view or Datasheet View. Continuous Forms View works best when each record is relatively small, so that many records can fit on one page. To turn it on in Design View, set Default View on the Format tab in the form’s properties sheet to “Continuous Forms.”

Print Preview and Layout Preview

To see a report as it will appear in print, use Print Preview. For long reports, this may take some time to view. You can view the layout of a sample of records by using Layout Preview instead.

Setting Properties

In many places throughout this book, we’ll tell you to look at, or set, this or that property of an object. Nearly all Access objects (tables, fields, queries, joins, forms, reports, sections, controls, and pages) have properties that define the way they look and work. For example, a form’s Caption property determines the text that appears in the titlebar of that form’s window.

In general, you will enter and change property settings in the object’s properties sheet. However, the properties sheet isn’t always found in the same way, and sometimes it’s hard to find, period. Here are the steps for setting a property:

1. Open a table, query, form, or report in Design View.

2. Open the properties sheet for the object (for some of these objects, you can just press F4):

   Table

   Click View → Properties.

   Query

   Click the background of the upper pane of the query window, then click View → Properties.

   Table field

   Click on the field; its properties sheet will appear in the bottom half of the window.

   Query field

   Click the field in the query grid and click View → Properties.

   Query join

   Double-click the join line between field lists.

   Form or report

   Double-click the form or report selector box at the top-left intersection of the rulers.

   Control or section

   Double-click the control or section bar.

   Data Access Page

   Open the page in Design View, select View → Properties, and then select Edit → Select Page.

3. If the properties sheet organizes properties on tabs, click the tab that contains the property you want, or click the All tab to see all the properties in a single, scrollable list (see Figure 0-1).

   

   Figure 0-1. The properties sheet for a text box control.

4. Click the properties field you want to change and type in a setting. If there’s a drop-down arrow, click it to select a property setting from the list. If a Build (…) button appears, click it to display an appropriate builder for the property.

Bound Versus Unbound Objects

You hear Access programmers make this distinction all the time, because it’s fundamental to the way forms and controls work. An object is bound when it’s linked directly to a data source (a table, say) and unbound when it isn’t. The simplest example is a text box control on a form. Let’s say you have a customers data entry form, with a text box where the user can type in the company name. The text box’s Control Source property (on the Data tab of its properties sheet) is set to the companyName field in the customers table. This ensures that when data is typed into the text box and saved, it goes directly into that field on that table; the text box is bound to that field.

On the other hand, you might have a data display form with two text boxes in which users type start and end dates, so the form will select and display a set of orders matching that date range. (Essentially, the text boxes allow the user to enter query parameters.) The Control Source properties of the two text boxes are blank, because the dates that get entered into them won’t be stored permanently in the database; they are simply used to limit the range of the query. These text boxes are unbound.

There’s actually a third case, too. Sometimes a text box has its Control Source property set to an expression—for instance, the total value of a customer’s orders. This is called a calculated field, and it is neither bound nor unbound. Because the value of this field is calculated, you can’t edit it or save it into the database. It’s for display only.

In addition to controls, a form itself can be bound or unbound. A form is bound when its Record Source property (on the Data tab) is set to a table or query. If its Record Source is left blank, it is unbound. Generally, bound forms are used to display bound and calculated controls, and unbound forms are used to display unbound controls.

Multi-User Databases and Split Design

A single-user database lives on a single PC and is used by only one person at a time. This might be a personal contacts database, or an orders database that’s used by several people who share the same computer. Obviously, sharing a single PC can be inconvenient, and that’s why you can also use Access to design and manage multi-user databases.

A multi-user database can be accessed over a network by multiple users, from multiple PCs, at the same time. Access databases are often deployed this way, but it’s important to do it correctly. Deploying Access across a network increases the risk of database corruption (see “That Darn Corruption” in Chapter 1), but if you do it right, the risk is manageable and the gain is substantial.

To create a multi-user database, it’s standard to adopt a split design. Splitting involves separating your database into two different Access database (MDB) files: a backend that contains only your data (i.e., your tables) and a frontend that contains all of your user interface paraphernalia (queries, forms, reports, and so on). This is easier to do than it sounds, because Access has a Database Splitter tool that makes it easy to split an MDB file into a backend and a frontend: it moves your tables into the backend and creates links to them from the frontend.

Once you’ve split your database, it’s important to deploy it correctly. Because all users will share a single copy of the backend, it must be put in a network-accessible location—ideally, on a server. That way, all users can access the same data at all times. On the other hand, every user gets her own copy of the frontend, installed on her own PC.

It’s a bad idea to attempt to use a split design over a wide area network (WAN) or the Internet; the reliability of the network connection is simply not good enough to prevent database corruption. One option is replication, which enables each user to have his own copy of the database, which is then synchronized periodically with the master copy. (See the sidebar “What Is Replication?” in Chapter 2 for more information.) However, we recommend against replication, because it’s very difficult to implement correctly. An option we do recommend is to web-enable your database. (See “Putting Data on the Web” in Chapter 3 for a full discussion.)

How to Create an Event Procedure

We’ve already mentioned using the properties sheet to define the way controls look or work. That’s just the beginning. Access has thousands of places where one or two lines of code added to an “event procedure” can fix a problem or dramatically improve an application. Throughout this book, we’ll say things like: “Add this line of code to the Before Update event,” or “Use the Click event of the command button to make this happen.” Here’s how it works.

At certain points in the program flow (when the user enters data, for instance, or runs a query), Access checks to see whether a developer has added any custom code. For example, every time a report is opened, Access sees if any code has been added to that report’s Open event. If code has been added, Access runs it before it opens the report. This is powerful!

To cite a very simple example, you can automatically maximize a report window (see “Report Preview Is Too Small” in Chapter 6 for the full story) by dropping a single line of code in the report’s Open event, such as:

	DoCmd.Maximize

Here’s how:

1. Open the report in Design View, and then open its properties sheet (View → Properties).

2. On the Event tab (see Figure 0-2), click in the On Open field, and then click the Build (…) button that appears. If you get a Choose Builder dialog, select "Code Builder” and then click OK. The Visual Basic Editor will open and create the appropriate empty event procedure for you. The empty procedure will look something like this:

   	Private Sub Report_Open(Cancel As Integer)
   
   	End Sub
   

   

   Figure 0-2. The Event tab shows the global report events—click in a particular field, then click the Build (…) button on the right to create an event procedure.

3. The procedure starts executing at the top and continues line-by-line until it gets to the bottom. Simply add your code on the blank line in between the top and bottom lines, so it looks like this:

   	Private Sub Report_Open(Cancel As Integer)
   	   DoCmd.Maximize
   	End Sub
   

   Indentation and blank lines have no effect on what your code does, but they do help to make code more readable.

That’s all there is to it. Of course, the more you learn Visual Basic, the better you’ll be able to take advantage of these event hooks. See the Appendix for a list of all Access events organized by category, and see “Tame the Visual Basic Editor” in Chapter 7 for help on using the VB Editor.

How to Add Code to Your Application

Adding a few lines of code to an event procedure on a properties sheet is easy. But suppose you want to add a lot of code?

There are numerous annoyances in this book where we say something like, “Fortunately you won’t have to write this code yourself, because so-and-so has already written it. And here it is.” But in order to take advantage of that free code, you must know how to add it to your application, and then how to run it.

The first part—adding the code—is easy, and that’s what we’ll cover here. The second part—running it—depends on the code. In some cases, you can run it immediately simply by typing the name of the new procedure in Visual Basic’s Immediate window and pressing Enter; in others, you’ll need to add at least a line or two of additional code yourself—often in an event procedure.

For now, let’s talk about adding code to your database. For example, in Chapter 7 (see “Use Excel Functions”) we show you how to call functions that are available only in Excel. Suppose you decide that the xlCeiling function is just what you need for your query. Here’s what you’d do:

1. Create a new model. In the Database window, select Insert → Module. The Visual Basic Editor opens and puts the cursor into the new module.

2. Add the code. Copy the code in question (such as one of the samples we list on the Fixing Access Annoyances page at http://www.oreilly.com/catalog/accessannoy/index.html) and paste it into the new module. If you prefer, you can even type it in, but if you do this you are more likely to introduce typos.

3. Save the module. Ctrl-S saves the new module and lets you give it a name; something like bas Whatever is good. (“bas” is the standard prefix for code modules—it stands for “basic,” as in Visual Basic; Whatever is the memorable name you supply.)

That’s all there is to it. Now you can use the xlCeiling function in expressions, just like any other Visual Basic function. For example, suppose your query showed grade averages, and you wanted to round them using the Excel CEILING function with a “significance” of 5. In the properties sheet of the Field line, instead of avgGrade, you could now write xlCeiling([avgGrade], 5).

DAO Versus ADO

OK, you’re ready to write some VB code. Prior to Access 2000, there was a single code library for working with data via recordsets, bookmarks, and so forth: the Data Access Objects (DAO) library. With Access 2000, however, Microsoft introduced a new code library: ActiveX Data Objects (ADO). Whereas DAO was designed specifically for Access’s Jet database engine—and may actually offer some performance advantages with data in MDB files—ADO is intended to work with a wider variety of data sources, including any Object Linking and Embedding (OLE) DB sources.

Does this mean that you can now forget about DAO and just work with ADO? Unfortunately, no. For example, the default recordsets that Access uses for bound forms are DAO objects. If you want to work with them, you must use DAO. In general, when you’re working strictly inside MDB files (as opposed to using Access as a frontend to another Database Management System [DBMS], such as SQL Server), you may want to use DAO exclusively to avoid confusion. If you need to use a mix of DAO and ADO code, remember to be explicit about which library you are using. When you declare objects such as recordsets or connections, you must be sure to specify which type you mean, like this:

	Dim rst1 As DAO.Recordset
	Dim rst2 As ADODB.Recordset

Whichever library or libraries you use, make sure your database has references to them. In the VB Editor, click Tools → References and make sure Microsoft DAO Object Library and/or Microsoft ActiveX Data Objects Library are checked—use the latest versions. If you plan to use DAO or ADO only, uncheck the reference to the library you won’t use to avoid confusion. Note that new databases created in Access 2003 include references to both libraries, while those created in Access 2002 and 2000 include only an ADO reference.