Fixing Access Annoyances | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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:

Open a table, query, form, or report in Design View.
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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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:

Open the report in Design View, and then open its properties sheet (View → Properties).
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.
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:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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:

Create a new model. In the Database window, select Insert → Module. The Visual Basic Editor opens and puts the cursor into the new module.
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.
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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It's fitting to start this book with a chapter on "General" Access annoyances, since no other military rank (Private? Major?) suggests how aggravating—or impressive—Access can be. Truly, Access is the supreme commander of all annoyances. And yet, when it works, it rules.

General annoyances aren't just annoyances we couldn't fit in anywhere else. They're global annoyances, such as the ridiculously bad default settings that affect everything you do in Access, mysterious #Name? errors that can crop up almost anywhere (in queries, forms, reports, and so on), and data corruption that can turn your database into digital mush. In this chapter we'll show you how to make the most of Access's user interface, demystify its obscure error messages, and take preventive measures against its worst booby traps.

THE ANNOYANCE: My database was running like a dog until I found out that Name AutoCorrect was turned on by default—and it's a known cause of corruption, too! Are there any other disasters hiding in Access's default settings?

THE FIX: You bet. Access has hundreds of default settings, and they range from quite sensible to pretty troublesome. Let's take a look at some of the worst offenders, along with some of our favorite tweaks. You can set the global defaults by clicking Tools → Options; the specific defaults that follow can be set inside individual forms or reports.

Section 1.1.1.1: Global Defaults

Changing these global defaults will vastly improve Access's performance:

Turn off Name AutoCorrect.: When you change the name of a table (and many other things), Access is set by default to look for all the references to that table name and update them automatically. Although this can be convenient, it also causes a real performance hit. Worse yet, the feature has been known to cause database corruption. Just say no: select Tools → Options, click the General tab, uncheck the "Track name AutoCorrect info" box, and you're good to go (see Figure 1-1).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: My database was running like a dog until I found out that Name AutoCorrect was turned on by default—and it's a known cause of corruption, too! Are there any other disasters hiding in Access's default settings?

THE FIX: You bet. Access has hundreds of default settings, and they range from quite sensible to pretty troublesome. Let's take a look at some of the worst offenders, along with some of our favorite tweaks. You can set the global defaults by clicking Tools → Options; the specific defaults that follow can be set inside individual forms or reports.

Section 1.1.1.1: Global Defaults

Changing these global defaults will vastly improve Access's performance:

Turn off Name AutoCorrect.: When you change the name of a table (and many other things), Access is set by default to look for all the references to that table name and update them automatically. Although this can be convenient, it also causes a real performance hit. Worse yet, the feature has been known to cause database corruption. Just say no: select Tools → Options, click the General tab, uncheck the "Track name AutoCorrect info" box, and you're good to go (see Figure 1-1).

Figure 1-1: Turn off Name AutoCorrect by unchecking the "Track name AutoCorrect info" box.

Don't check the "Compact on Close" box. In this case, the (unchecked) default is right. It may sound like a good idea to have Access automatically compact your database every time you close it, but there's just one little problem: this feature can cause database corruption. Leave it unchecked

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: Access hasn't behaved right since day one. It hangs, it's unpredictable, things that worked yesterday don't work today, and I get all sorts of weird error messages. This morning it gave me a key violation error, telling me that I'm entering a duplicate value in an AutoNumber field—but my form lets Access enter data in that field itself! Am I cursed?

THE FIX: We hope not, because it's a curse we'd rather not catch. A few things could explain such painfully flaky behavior.

First, some versions of Access are more stable than others. Access 97, for instance, is generally regarded as the most rock-solid. Access 95 was pretty bad, and some regard Access 2000 as a disaster—especially with respect to corruption problems. Access 2002 and 2003 seem to be quite a bit better. So, if you're using Access 2000, your best bet is to upgrade. In addition, make sure that the version of Windows you're running is compatible with your version of Access. The same fix applies if you're sharing Access over a network. (See "Access/Windows Compatibility Issues" in Chapter 2 for more information.)

If you're stuck with a lemon, you'll have to be sure to treat it right—that means installing service packs, Jet upgrades, and so on (see the "Installation Checklist" section in the Appendix).

But what if you've done all the right things, and Access still can't walk straight? Here are some other things to check:

Bad install: It's possible that your Windows Registry is corrupted, your DLLs are improperly registered, or some other aspect of your Access (or Windows) installation is not right. See "Agonies of a Sick Installation," later in this chapter.
Network issues: Access is known to be unstable on some networks, and network dropouts are one of the chief causes of database corruption. See "That Darn Corruption," later in this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: Our travel expenses form has a calculated field that computes each employee's travel budget using information found in a separate budgets table. I'm sure the calculation is correct, but all I see in the form is a #Name? error message where the employee's total budget calculation should be. I built this expression using the Builder; it should work!

THE FIX: It is maddening that Access's Expression Builder lets you spend hours building all sorts of expressions that you can't test until you close the Builder and go back to your form or query. In your case (see Figure 1-19), you're getting a #Name? error because you can't directly reference information in a separate table. (And yes, this is an Access "feature," not a bug.)

Figure 1-19: The dreaded #Name? error—shown here, in the "Total budget" text box—can be caused by a wide variety of conditions. It's a sure sign that something is wrong!

In general, #Name? and #Error? are just crude indicators that something is wrong with the source for a field or control source. (Never heard of a "control source?" See "Bound Versus Unbound Objects" in Chapter 0.) Let's take a look at some of the most common causes for these errors. See also "Debugging Expressions" in Chapter 7.

Section 1.3.1.1: Misspelled or Nonexistent Name

If a field, table, or function name you are using is misspelled, or just plain missing, obviously Access won't be able to find it.

Section 1.3.1.2: Missing Equals Sign

If the control source for a text box on a form is an expression, you must precede the expression with an equals sign. Check the Control Source property on the Data tab of the text box's properties sheet.

Section 1.3.1.3: Name Is Out of Scope

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As Access developers, we love creating elegant user interfaces and solving tough problems with ingenious code. But the evil Borgs who manage people like us know that most of the work goes into an Access application after it's written. The most effort and the biggest headaches arise during the unglamorous tasks of deployment and maintenance.

In this chapter we'll delve into the good, the bad, and the ugly of deploying a finished Access application. To start with, we'll look at how to diagnose and fix various performance issues. We'll show you how to avoid the embarrassment of having your beautifully crafted user interface become unusable because it can't handle a measly 10,000 records, and where to look for performance bottle-necks. Next, we'll step you through the delicacies of coping with multiple versions of Access. Then, since most projects require some form of security, we'll guide you through the stupefying maze of Access security—and give you the lowdown on just how (in)secure it really is. Finally, we'll show you how to move a multiuser application onto a network without the corruption that dogs so many projects, discuss best network practices and how to avoid the tedium of manual DSN setup on every user's machine, and maybe make replication a little less scary.

THE ANNOYANCE: We have a tiny employees database, and I thought it would be handy to store our ID photos with the rest of the employee data. These are small JPGs, no bigger than 100 KB each, and I only added five of them—but my database has gone from 400 KB to about 24 MB! What the heck happened?

THE FIX: Access provides an OLE data type that accepts objects such as pictures, sound files, spreadsheets, or Word documents, so you can do the kind of thing you tried to do. Don't do it. Even tiny objects will cause your database to bloat like a dead hog on a Texas highway. That's because Access is storing not only the object, but all the rendering (display) information, too. Rendering information can easily be larger than the object itself (see MSKB 123151 for more information).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: We have a tiny employees database, and I thought it would be handy to store our ID photos with the rest of the employee data. These are small JPGs, no bigger than 100 KB each, and I only added five of them—but my database has gone from 400 KB to about 24 MB! What the heck happened?

THE FIX: Access provides an OLE data type that accepts objects such as pictures, sound files, spreadsheets, or Word documents, so you can do the kind of thing you tried to do. Don't do it. Even tiny objects will cause your database to bloat like a dead hog on a Texas highway. That's because Access is storing not only the object, but all the rendering (display) information, too. Rendering information can easily be larger than the object itself (see MSKB 123151 for more information).

Instead of storing pictures in your tables, link to them using the image control, which knows how to display them when the time comes. You'll find the image control in the standard toolbox. After you place it on your form or report, it'll prompt you for the location of the picture file to be displayed.

Figure 2-1: Only the name of the image file is being stored in the database. Code can be used to actually display the image on a form or report.

This solution is fine for simple applications, but it doesn't let you store and manage the picture locations as data within your database, which is often what you really want to do. If your image collection grows or moves, you don't want the file paths hardcoded in a control somewhere. Instead, set up the image control, then clear the Picture property, which is found on the Format tab of the image control's properties sheet. Next, add a dab of VB code to the Current event of your form that lets it read the picture location from a table (as in Figure 2-1), and set the image control to display it. The code might look like this:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: The Access 2000 database I've been running for years on my Windows 98 PC craps out on my Windows XP Pro PC. It's a simple application—a couple of macros and a few buttons on forms. Sometimes the buttons work fine, but other times the program crashes.

THE FIX: First, make sure your version of Access is compatible with XP (see the next Annoyance, "Access/Windows Compatibility Issues"). XP and Access 2000 are indeed supposedly compatible, but in many cases problems arise anyhow. Why?

Assuming that bad karma is not the problem (you never know), the most likely cause is that something (software, hardware, installed patch files) is different on the two machines. But what? With Microsoft's elegant installation process, there are only a few thousand possibilities. That's why we created the "Installation Checklist" in the Appendix—run through it to ensure that the problematic machine is up to date in all respects. If you're running Access on a network, check your machine's Network Interface Card (NIC) and connection as well (see "Best Practices for Access on Networks," later in this chapter).

THE ANNOYANCE: My boss wants me to install Access 2000 on our new Windows XP machines. Will this work? Why does Microsoft make it so hard to find out about compatibility?

THE FIX: What, you haven't upgraded to the latest version of everything? (Doing so would spare you this grief and fatten Microsoft's wallet—a win/win for everyone, no?) Well, you are right about one thing: Microsoft doesn't make it easy to find out about version compatibility. Table 2-1 shows what we were able to dig up. The general rule is that it's fine to run older versions of Access on newer versions of Windows, but don't do the opposite.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: I've heard that Access security sucks. Is it worth the trouble to master?

THE FIX: Access security is both weak (in a cryptographic sense) and poorly implemented. It's derided in the crypto community, and it's easy to crack with tools you can find on the Internet (see the "Database Lockout" Annoyance in this chapter). Still, it's useful in some situations. For example, it can protect your data from accidents—that is, the unintended actions of users. That's why it's important to grant users only those permissions they really need. Assuming you set it up correctly, Access security works just fine—for this purpose. The login will also keep out casual mischief-makers.

Beyond this, Access's security features are not enough to keep sensitive data safe. In addition to its security flaws, an even bigger vulnerability is that even read-only users must have write and delete permissions to the folder where the database is stored. A malicious user could easily delete the MDB file!

THE ANNOYANCE: We're about to convert several Access 97 databases to Access 2002 format. We have fairly complex user-level security applied to these applications. Do we have to recreate security after each conversion?

THE FIX: Nope. As long as you use the built in Conversion tool (Tools → Database Utilities → Convert Database) Access will take care of migrating the security too. However, if you convert the application by importing all the objects into a database in the newer format, you will need to reapply security info; it does not import that way.

THE ANNOYANCE: I've inherited a database that was created and maintained by an employee who has since left the company. I need to use this database, but the password I have doesn't work. I'm getting desperate!

THE FIX: Getting locked out of your own database is a common situation, and this may be the only time you are grateful that Access security is hard to apply correctly. First, identify what type of security has been applied to the database. If you're getting a password prompt with a single field (as in Figure 2-6), then you have simple security—one password for the entire database. If your prompt asks for both a name and a password (as in Figure 2-7), then you have user-level security, which is more complicated.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: I'm responsible for ensuring that our sales database is up, running, and as happy as possible. The problem is, I'm no database administrator. I know Access can get cranky; what can I do to take a proactive, preventive approach?

THE FIX: Your options will vary a bit depending on whether or not you've split your database. (For more information, see "Multi-User Databases and Split Design" in Chapter 0.)

In the unlikely case that you're administering an unsplit single-user database, with all the tables, queries, and so on in one MDB, there's not much to do. Some rules to live by:

Back up your data as often as possible. How often? Ask yourself, what's the most data you can afford to lose forever?
Compact the database regularly (Tools → Database Utilities → Compact and Repair Database). How often? Compact once per week if your data changes daily. (See the sidebar "What Is Compacting?" in Chapter 1.)
To avoid headaches in the long run, it helps if you design your database properly in the first place. See "Table Design 101" in Chapter 3 for guidelines.

If you're running a split, multi-user database over a network, there's quite a bit more to be concerned about. One common error is putting the frontend (i.e., queries, reports, forms, and so on) on the server and having users open it across the network. This is never a good idea; it puts unneeded demands on the server, makes for poor performance, and increases the risk of database corruption. Instead, install a copy of the frontend on each user's desktop machine. Only the backend (i.e., the tables) belongs on the server.

In multi-user environments, some form of record locking is essential to avoid having users overwrite each other's data. Click Tools → Options, click the Advanced tab, and set "Default record locking" to "Edited record." Be sure to leave the "Open databases using record-level locking" box checked. (For more information on these choices, see the discussion of record locking in the "Global Defaults" section of "Access's Bad Defaults" in Chapter 1.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

GIGO—Garbage In, Garbage Out—means that all the fancy forms and reports in the world don't mean a thing if the data in your base ain't got that swing. In this chapter, we tackle the annoyances that prevent you from getting and keeping good data.

Chief among these annoyances is the fundamental issue of good database design. The average Access user quakes at the thought—mostly because so-called "normalization" rules are usually explained in terms so arcane that transmuting lead into gold seems simple by comparison. If you cut through the techno-speak, though, database design is simple—and we'll show you how to do it right. Then we'll help you ensure that the data that goes into your lovely design is valid. Finally, we'll address the myriad migraines that Access causes when you try to move data around using import, export, and linking.

THE ANNOYANCE: I've heard that I'm supposed to "normalize" my tables, but the books that discuss this are really confusing. "Primary keys," "foreign keys"…what are they talking about? And "Boyce-Codd normal form" sounds like a disease. Why do I need an advanced degree to create a simple contacts database?

THE FIX: Normalizing basically means organizing data to reduce redundancy. As the esteemed Webopedia (http://www.pcwebopedia.com) pithily puts it, it involves "dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships."

Such well-designed tables are essential to maintaining data integrity and the long-term health of your database. They also make a big difference in performance as your database grows. Fortunately, you can leave the theoretical stuff to the experts; following a few simple rules will suffice for most needs. The essence of data normalization is just this: pull out repeating and reusable items and put them in their own, separate tables.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: I've heard that I'm supposed to "normalize" my tables, but the books that discuss this are really confusing. "Primary keys," "foreign keys"…what are they talking about? And "Boyce-Codd normal form" sounds like a disease. Why do I need an advanced degree to create a simple contacts database?

THE FIX: Normalizing basically means organizing data to reduce redundancy. As the esteemed Webopedia (http://www.pcwebopedia.com) pithily puts it, it involves "dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships."

Such well-designed tables are essential to maintaining data integrity and the long-term health of your database. They also make a big difference in performance as your database grows. Fortunately, you can leave the theoretical stuff to the experts; following a few simple rules will suffice for most needs. The essence of data normalization is just this: pull out repeating and reusable items and put them in their own, separate tables.

Some table-design rules to follow:

Split up your data by topic. Think of tables as file folders in a filing cabinet. Identify the main topic headings for your data, and create tables for each distinct topic. For example, if you were running a business, you wouldn't file your customer data in the same folder with suppliers' addresses and price lists, nor would you mix accounts payable with receivables. Apply the same organizational logic to the information you plan to store in your Access database.

OK, that sounds sensible enough, but when it comes time to do it it's hard to know where to start. For instance, you have a customers table (see Figure 3-1), but what data should be stored there? Orders, billing addresses, shipping addresses, phone numbers, everything? That's where the other data normalization rules come in.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: I'm trying to import a parts inventory into Access from an Excel worksheet. The Import Wizard (File → Get External Data → Import) runs fine, but then it gives me a "Not all of your data was successfully imported" message. The ImportErrors table shows a lot of "type conversion" errors, and some of the data is just plain wrong. But the wizard never asked me to specify data types!

THE FIX: The single biggest problem with importing data is getting the data types right. If you have type conversion errors, or if garbage data appears in the imported table, either the imported file has bad data or the receiving table has incorrect data types.

Let's look at bad data first. Suppose you're trying to import a column of numbers into a field that's set up to be a Number type—but a few of the numbers have feet or inches symbols attached, like 3" or 5.5'. The extra characters disqualify these values from being stored as the Number data type and cause conversion errors. Fortunately, the ImportErrors table (see Figure 3-17) points to the errant field (i.e., column) and row, making it easy to track down the bad data in the source file. You can then clean up the data and import it again.

Figure 3-17: The ImportErrors table gives you the reason for the error, the field in which it occurred, and the row of data in which it occurred.

The other common problem is specifying the wrong data type at the receiving end, which is especially a problem when you're importing worksheets. For some reason, Access doesn't allow you to specify data types during the import. Instead, it tries to guess the correct types for each field based on the first rows of data. (Depending on your version of Access, it will examine as many as 25 rows before making a decision.) If the Import Wizard sees any text, it sets the data type to Text (unless the text is more than 255 characters long, in which case it'll choose Memo). If it sees both numbers and dates, it chooses Number, and if it sees only dates, it chooses Date/Time.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

I don't know how to fix my own car, which is why I pay somebody to do it for me. This arrangement suits me just fine. But if I didn't know how to drive my own car, that would be a different story entirely. It would be almost as bad as, well, having a database and not being able to write my own queries.

That said, I'm sorry to tell you that query Design View can be one of Access's most baffling interfaces—but if you want to go anywhere, you've gotta use it. In this chapter, we'll do our best to get you over the speed bumps, and we'll offer fixes and workarounds for query Design View's idiosyncrasies and deficiencies. We'll also offer guidance for creating queries that may seem impossible, but can generate some very useful results. When you're done with this chapter, you'll know some of the tricks that hard-core Access stunt drivers use—tricks you should try at home.

THE ANNOYANCE: I need to generate a list of names and phone numbers for just those employees who live in Massachusetts—but our employees table includes workers in eight states. I can't make any sense out of query Design View. Where do I start?

THE FIX: Access's query Design View really drives people crazy. It's not intuitive, but once you get used to it it's really pretty useful. Since querying data is what it's all about, you must learn how to make query Design View dance to your tune. (Incidentally, Access does have a Simple Query Wizard, which you can find by clicking Insert → Query, but it's so simple that it's almost useless.)

To make sense out of the query Design View interface, remember that Access thinks in terms of tables. Tables, and nothing but tables. Even when your query produces a single number, Access thinks of this as a table with one field and one row. So designing a query is really about translating your question into terms that define a new (virtual) table—a.k.a. a recordset. That's what query Design View helps you do. Here's how to use it, step by step:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

THE ANNOYANCE: I need to generate a list of names and phone numbers for just those employees who

Table of Contents

Section 1.1.1.1: Global Defaults

Section 1.1.1.1: Global Defaults

Section 1.3.1.1: Misspelled or Nonexistent Name

Section 1.3.1.2: Missing Equals Sign

Section 1.3.1.3: Name Is Out of Scope