Chapter 3. Data, Tables, and Database Design
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.
DATABASE DESIGN AND DATA INTEGRITY
Table Design 101
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.
Give every item of data its own field. For example, many customers will have both a cell phone and a land line. You might be tempted to create a single “phoneNumber” field and put both numbers into it, separated by a comma. This is never a good idea. Instead, create a “cellPhone” field and an “officePhone” field, and let each field hold a single item of data (see Figure 3-2). Similarly, in good database design mailing addresses are typically broken up into “addressLine1,” “addressLine2,” “city,” “state,” and “zipCode” fields.
Put repeated items into separate tables. If your customer has two or more phone numbers, you should create distinct phone number fields in the customers table. But what if you need to record something that repeats indefinitely, such as customer orders (including customer order numbers)? If you stored them in the customers table, you’d have to create fields such as Cust_Order1, Cust_Order2, Cust_Order3, and so on, ad infinitum. The obvious problem here is that you don’t know how many orders you’ll need to provide fields for. This clearly indicates that you need to create a separate table just for orders. In a properly designed database, new data adds rows, not fields. (When you’re designing tables, you have to think about how they’re going to be used. If, in the course of normal use, you envision your users adding new fields, something’s very wrong.)
Put reusable information into separate tables. Any information that you’ll be entering multiple times is “reusable” information. Your goal in database design is to avoid entering the same data twice. Instead, find a way to reuse the data that’s already been entered. Not only does this save work, but it avoids data entry errors or discrepancies that will make it difficult to maintain a database.
An example is a product description. You might be tempted to put that right into the orders table (Figure 3-3), but the product descriptions don’t change, and you’ll find yourself entering the same descriptions again and again for each new order. This is a sure sign that this information belongs in a separate table (Figure 3-4).
These rules don’t cover every situation, but you can go pretty far with them. However, there’s one important concept that we haven’t yet addressed. Normalizing is all about splitting up your tables the right way—but once they’re split, you need a way to connect them. For instance, if you create a separate table for orders, you need a way to track which orders go with which customers. This is done by forming “relationships” among your tables (the subject of the next Annoyance).
MSKB 234208 is a good, non-technical article on normalization. For a slightly more technical tutorial, check out http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html. It’s not written about Access, but normalization is the same in any relational database.
Relationship Angst
THE ANNOYANCE: I just designed my first database, but when I tried to put data into it, Access gave me a "primary key violation” error message. What’s wrong? More to the point, what is a key, and what is a primary key, and why should I care?
THE FIX: Setting up correct table relationships is the second half of good database design. (The first half is defining your tables correctly; as discussed in “Table Design 101.”) In this fix, we’ll look at relationships from the ground up.
When you designed your database, you sorted your data into separate tables—you “normalized” it. Defining relationships between tables is how you pull that related data back together again. The links you create ensure you don’t forget how the data is related, and they remind you to enter (and prevent you from accidentally deleting) data that’s needed to complete the picture.
The element that forges the relationship between two tables is the “key.” It’s like a Social Security number—a unique identifier for each record in that table. Good table design requires that every table have at least one field that acts as a unique key. We call this the primary key field. In Access, you designate it by right-clicking the field in Design View and selecting “Primary Key.”
Be vewy, vewy careful when you pick the primary key field—its value must be unique for each record. You might think that the businessName field in a customers table would make a good primary key, but it probably won’t. (After all, you could have two customers with the same business name in different states.) The simplest approach is to add an “ID” field to every table (for the customers table, we’ll call it “customerId”) and use the AutoNumber data type for that field. Every time you add a new record to the customers table, Access will enter a new, unique number into that record’s customerId field. The numbers are meaningless—they simply serve as unique identifiers. (See the sidebar “Primary Keys and Duplicate Records” for deeper thoughts on ID fields.)
To see how keys are used to create relationships, consider two tables: one listing customers and the other listing orders. As we saw in the previous Annoyance, the orders table should not have any customer information in it (such as shipping address, business name, and so on), because that’s all reusable information that you don’t want to retype into each order. So how do you create a report that tracks which order goes with which customer, and presents all that information on one screen? You simply add a field to the orders table that refers to the primary key of the customers table. You may as well call it by the same name, customerId (see Figure 3-5). Now you can see why it’s essential that the primary key be unique. Every order in the orders table has a customerId, and that is the only way you’ll know which customer the order belongs to. Incidentally, the customerId field in the orders table is known as a foreign key; it refers to the value of a primary key field in another table.
Once you’ve defined primary and foreign keys, you will probably want to set Access to enforce these relationships. For instance, you can tell Access to prevent you from adding customerId values to the orders table that don’t already exist in the customers table. This is a very good idea, because if you don’t, a typo could leave you with no way to determine which customer an order belongs to.
To define a relationship, choose Tools → Relationships. Right-click anywhere in the Relationships window and select Show Table. Add the tables you want to relate to the Relationships window by highlighting them and clicking the Add button in the Show Table window. Then drag the primary key field from one table and drop it onto the matching foreign key field in the other table. The Edit Relationships dialog will open. Make sure the field names look right, and then check the “Enforce Referential Integrity” box. This will prevent you from deleting a record that’s related to a record in another table. (You can safely ignore the other options, but you may want to check out the discussion of the Cascade Delete option in “Can’t Delete Records,” later in this chapter.) Click the Create button, and Access will draw a line connecting the two tables. If you ever need to edit or delete the relationship, you can do so by right-clicking the line.
The customers/orders relationship used here as an example is a very common type of relationship—a one-to-many relationship, where every order belongs to exactly one customer, and one customer can have many orders. But there’s another important kind of relationship—the many-to-many relationship. Consider the relationship between suppliers and products. Several suppliers might supply the same product, and one supplier might supply several different products. If you followed the customers/orders example and added a supplierId field to the products table, each product could have only a single supplier. Likewise, if you added a productCode field to the suppliers table, each supplier could supply only a single product.
The problem is that when two tables are directly related, only one of the tables can be on the “many” side. But you need a way to enable both sides of the relationship to be “many.” To do that, you’ll have to introduce a linking table that will serve as an intermediary. Both products and suppliers will have one-to-many relationships to this linking table, enabling each product to have many suppliers and each supplier to supply many products (see Figure 3-6).
A linking table typically just contains the foreign keys from each of the tables that it links. For example, to link suppliers and products, you’d add a products-suppliers table with just two fields: productCode and supplierId. Each supplier will appear in this table once for each product supplied, and each product will appear in this table once for each supplier who supplies it. The primary key for this linking table will be a composite key using both fields. (In table Design View, hold down the Ctrl key and click both fields to select them, then right-click and choose “Primary Key.”) To define this relationship in the Relationships window, simply treat this many-to-many relationship as two one-to-many relationships.
I Don’t Want to Design a Database from Scratch
THE ANNOYANCE: I run a small business, and I just need a simple database to handle sales contacts. I don’t need anything fancy or customized. Surely this application has been designed a thousand times by people who know Access better than I ever will. Isn’t there some way to avoid building it from scratch?
THE FIX: There sure is. You have a variety of options, depending on your needs and how much you can afford to pay. For a very generic contact database, take a look at the prefab database templates that come with Access. Choose File → New, and in the New Document task pane select one of the template options (such as “On my computer” in Access 2003, or “New from template” in Access 2002). Figure 3-7 shows the Databases tab, with choices such as Contact Management, Expenses, and so on. Selecting one of these will start the Database Wizard, which lets you customize the template a bit. They’re not fancy, but they’re a decent starting point. If you don’t see the kind of database you’re looking for here, there are more choices at http://office.microsoft.com/templates/default.aspx. You’ll also find a few full databases you can download at web sites such as http://www.rogersaccesslibrary.com and http://www.mvps.org/access/resources/downloads.htm.
You might also poke around in newsgroups or at Utter Access (http://www.utteraccess.com) to see if anyone has a database they’d be willing to share.
If your needs are more complex, or you don’t want to take your chances with a homegrown job, consider paying for a commercial solution. Many off-the-shelf Access packages are available for common business needs, and in many cases the seller will customize upon request. A Google search for “off-the-shelf Access contact database” should find quite a few, but it can be hard to sift through the chaff. Alternatively, try posting a query in Google groups; many vendors read these groups. For most businesses, many of your database needs (such as A/R, order entry, inventory control, and so on) fall under the rubric of accounting packages. Access MVP Tony Toews maintains a list of such off-the-shelf packages at http://www.granite.ab.ca/accsacct.htm.
Finally, if you can’t find the right off-the-shelf solution, there are many professional Access developers who will be happy to put together a custom solution for you at a reasonable price. (“Reasonable” varies, of course, depending on the complexity of the project—but you’ll probably end up paying between $25 and $100/hr, and it might take anywhere from a week to a couple of months.)
Bad Field Names
THE ANNOYANCE: I’ve just been brought in to rescue a half-finished database design. Among other things, the field names are a mess, with spaces and many reserved words. Why does Access allow this sort of thing?
THE FIX: Microsoft tried to make Access user-friendly by allowing you to name your database objects, fields, and controls just about anything that fits into 64 characters. For instance, names can include spaces and apostrophes, like this: “Employee’s and temporary staff’s benefits table.” What Access doesn’t tell you is that using spaces or apostrophes in names can cause major headaches with Visual Basic. For example, when you’re creating SQL queries you’ll have to surround everything with brackets (and your SQL will be nonstandard), since SQL does not allow spaces.
Another common mistake is choosing field names such as “date” and “name,” which are reserved words in Visual Basic; using them can cause unexpected problems, some of which can be very hard to track down. Most of the time VB will think that you’re referring to a built-in method or object when, in fact, you’re simply trying to refer to one of the fields you’ve created. Worse yet, problems may not show up until later, and they can be quite difficult to debug. If your database already has poorly chosen field names, see “Find/Replace for Database Objects” in Chapter 1 for a way to fix the problem.
Note
Camel case is what you get when you run words together and use capitals where the second word begins, to form theHump.
If you need to suggest a space, either use an underscore (as in “total_payments”) or use “camel” case (as in “totalPayments”). Whatever you do, avoid using VB reserved words—you can find a list of these in MSKB 286335. While you’re at it, replace generic field names such as “error” or “sum” with more specific names such as “accountingError” or “paymentSum.”
There is one downside to using names such as “leasePaymentsSum” for fields in tables: although they aren’t meant to be seen by end users, these names become the default display labels in Datasheet View, or when you create a form based on the table. Of course, you can go into form Design View and change the label to anything you want, but that’s an extra step. To make life a bit easier, use the field’s Caption property. For example, when working in table Design View, you can set the caption of the leasePaymentsSum field to “Sum of Lease Payments.” Then controls based on that field will automatically have understandable labels.
Flaws in the Decimal Data Type
THE ANNOYANCE: We use Access 2003 for our weather database, and we store temperature data using the Decimal data type. But when we try to sort those temperatures from high to low, Access treats negative temperatures as if they’re higher than positive ones! (See Figure 3-8.)
THE FIX: There are a number of problems with the Decimal data type, and this sorting bug is one of the worst. (See MSKB 837148.) As a workaround, you can add an index to the field, which will enable Access to sort the values in the field correctly. But if you use the data in expressions, aggregate queries, and so forth and try to sort on the calculated values, you’ll run into the same problem—and the index won’t help. Another workaround is to convert everything to the Double data type before you sort; just wrap the expression to be sorted in
CDbl(), like this: CDbl(temperature).
A better solution is to avoid using the Decimal data type at all (or at least where possible). Decimal is a scaled integer data type, which means that although it displays numbers with a decimal point, it stores them internally as an integer and a scaling factor. (The scaling factor is a value that represents the number of decimal places to the right of zero. So, 15, 1.5, and 0.15 all get stored as a pair of values; the integer is always 15, but the scaling factor is 0, 1, or 2, respectively.) This enables Access to avoid the rounding errors that occur with the Single and Double floating-point types. If absolute accuracy is important to you, and you only need four places to the right of the decimal point, enter the data as the Currency data type, which is also a scaled integer. Despite its name, its wide range (–922,337,203,685,477.5808 to 922,337,203,685,477.5807) and fixed-point accuracy make it a good choice for many numeric applications. Simply set the format of your currency field to General Number to get rid of the dollar signs and display the numbers as entered.
AutoNumber Nightmares
THE ANNOYANCE: We use an ID field set as an AutoNumber data type as the primary key field in our orders table. The problem? To match our hard copy records, we need the ID field to start numbering at 11001, and not skip values between records. But Access always starts AutoNumbers at 1, and if we delete a record, it creates a gap in the sequence.
THE FIX: Access doesn’t let you set the starting value of an AutoNumber field; nor does it guarantee there won’t eventually be gaps. If the AutoNumber field’s New Values property is set to “Increment” (the default), Access will generate sequential numbers, but it won’t reuse numbers that get discarded when you delete a record or undo the creation of a new record. What AutoNumber does guarantee is that you’ll get a unique number that can be used as a primary key—and for most purposes, that’s all you need. (In Access 2002 and earlier versions, compacting the database resets the AutoNumber ID to the last one used, recovering any deleted numbers. However, a bug in the current Jet service pack removed this feature in Access 2003.)
Given how AutoNumbers are normally used, you shouldn’t be concerned about their actual values, or about gaps in the sequence. Still, if these issues are a problem for you, there are steps you can take. Setting the start value of an AutoNumber field is easy to do with an SQL statement, but ensuring that there are no gaps—despite deletions or undos—is considerably more complex, especially in a multi-user scenario.
Check to see if your database is set to use the ANSI SQL-92 extensions. To find out, select Tools → Options, click the Tables/Queries tab, and look in the SQL Server Compatible Syntax area to see if the “This database” box is checked. If it isn’t, check it now. (This feature is not available in Access 2000.) Once you’re using the ANSI SQL-92 extensions, you can specify both the start value and increment of an AutoNumber field using an SQL Create Table statement, like this one:
CREATE TABLE tblMyTable (myAutoNumber AUTOINCREMENT (11001, 1));
Create a blank query and paste this code into SQL View; then run it. It creates an AutoNumber field that starts at 11001 and increments by 1. You can add your other fields in Design View. (Or, if you’re good at SQL, you can specify all your fields in this statement.)
If you’re not using SQL-92, you can set the start value of an AutoNumber field by using this SQL statement:
INSERT INTO tblMyTable (myAutoNumber) VALUES (11000);
Simply paste it into the SQL View of a blank query, edit it to suit your specifics, then run it. After running this query, go into the table and delete the record. The next record will start at 11001.
Can’t Create a Relationship
THE ANNOYANCE: No, I’m not trying to get on Oprah. I’m trying to define a relationship between patients and visits tables so I can enforce referential integrity—that is, prevent users from accidentally deleting records in one table that are related to still-existing records in the other table. The problem is that there’s a ton of existing data in these two tables, and apparently someone has already done exactly the thing I’m trying to prevent from happening in the future…and Access won’t let me enable referential integrity unless the tables are already ship-shape. How can I repair the messed-up data so I can turn on the Enforce Referential Integrity option?
THE FIX: Sometimes when you try to add a relationship between tables, Access won’t let you because the existing data has invalid foreign keys. In this example, you probably have some patient IDs in the visits table that aren’t found in the patients table. Finding and extracting the records that are causing the problem can be a real headache—the trick is to let Access do it for you, by running a query that finds unmatched records.
Run the Unmatched Query Wizard (Figure 3-9) from the database window by choosing Insert → Query → Find Unmatched Query Wizard and clicking OK. In the wizard’s first screen, add the table that has the foreign key (in this case, the visits table), and in the second screen add the table that has the primary key (in this case, patients). Tell the wizard to match on the foreign key field. When the wizard finishes, Access will run the query; it will find all the records in the first table where the foreign key doesn’t match a primary key in the second table. You can then select the problematic records, and delete them or fix them by hand.
Can’t Delete Records
THE ANNOYANCE: I need to delete erroneous records from my clients table, but because it has an enforced relationship with the active projects table, I get the following error: “The record cannot be deleted or changed because table <tblProjects> includes related records.” Why can’t Access just delete these related records, too? I know they’re related, and Access does too, so shouldn’t there be some way for Access to just blow them all away?
THE FIX: By default, Access doesn’t automatically delete records that are related to the record you’re trying to delete. This is a reasonable default—otherwise, it would be too easy to create orphaned records (i.e., records containing foreign keys that point to nonexistent other records). But, if you really know what you’re wishing for, you can have it that way. Choose Tools → Relationships and right-click the relationship line that connects the two tables. Choose Edit Relationships, and check the “Cascade Delete Related Records” box (Figure 3-10). This tells Access that when you delete a record whose primary key is linked to a foreign key in a related record, the related record(s) should be deleted, too.
Default Values Don’t Apply to Existing Records
THE ANNOYANCE: I set the default value of our leaseDate field to 2002-02-10, which is the date that most of our leases started. However, the records don’t show this date at all—not in the form, and not in the table, either.
THE FIX: Remember that default values are applied only when a record is created. This means that defaults will not be applied to records that already existed when you set them up.
If you want to add a default value to preexisting records, you must use an update query. How? Create a regular (select) query that finds all the records with null values in the field. In the database window, click Queries → Create Query in Design View, and put “Is Null” in the Criteria line. Then convert the select query to an update query by choosing Query → Update Query, and enter the default value in the Update To line. Running this query replaces nulls with your default. Alternately, if you’re entering data in a form (new or preexisting) and you come to a field that has a default value set up for it, use Ctl-Alt-Space to fill in the default value.
Setting up defaults that are fixed values (such as “Unknown” or “25”) or are based on simple expressions (such as =Date()) is easy. Just open the table in Design View, click in the field’s Default Value property, and type in the default value. For example, to mark every record in a table with its creation date, add a createDate field to the table and set its Default Value to =Date() (Figure 3-11). Just be sure that your field size is large enough to accept the default you specify. You can apply defaults to fields in a table, or controls in a form, but be aware that some functions (such as
DLookup, DSum, CurrentUser, and so on) cannot be used at the table level; you’ll know immediately, because Access will give you an error when you try to save the table. If you need to use one of these functions, apply the default at the form level.
Defaults that are derived from other data in the same record can’t be set using the Default Value property. For example, suppose you have a transactions form, and you want the default commission to be equal to the transactionAmount times the commissionRate—values that are stored in the same record. The problem is that you can’t reference other fields in the same table in a field’s Default Value, and you can’t reference other controls in the same form in a control’s Default Value. That’s because the default gets applied when the record is created; it can’t derive itself from other values in the same record because they don’t exist yet. The fix is to add some code to the After Update event of the controls on your form that your calculation depends on. Once the other controls are filled in, the code computes the result and inserts the default value into the calculated control. Here’s some sample code:
Note
If the calculated value will never be changed by hand, it’s not a default value; it’s just a calculated value—and generally it shouldn’t be stored in the database at all, since it can always be calculated.
Private Sub txtTransactionAmount_AfterUpdate() If Not IsNull(Me!txtTransactionAmount) And _ Not IsNull(Me!txtCommissionRate) Then Me!txtCommission = Me!txtTransactionAmount _ * Me!txtCommissionRate End If End Sub
Simple Validation Rules
THE ANNOYANCE: I’m trying to define a validation rule for Zip Codes that will catch the occasional data entry error on my form. But Access’s #@$#! Help system doesn’t tell me a darn thing. Help!
THE FIX: Simple validation rules can be applied via the properties sheets of fields (in table design) or controls (in form design). As a general rule, it’s better to do it at the table level, to ensure that the same rule gets applied no matter what route the data takes to the table. Either way, Access’s help on data entry validation rules definitely comes up short. What they say is true: you can use just about any expression for a validation rule. But—Hello? Come in?—that doesn’t help much if you don’t know which expressions to use in the first place.
In practice, just a few kinds of expressions solve most common validation needs. Table 3-1 gives some examples, and the following sections discuss the various operators.
|
Rule |
Usage |
|
|
Greater than or equal to 25. |
|
|
Not equal to zero. |
|
|
Less than (i.e., prior to) January 1, 2004. |
|
|
Accepts both 5-digit Zip and Zip+4 Codes. You combine the two patterns with the |
|
|
Matches specific characters with a pattern of numbers. This example accepts only phone numbers starting with area code 541. |
|
|
Accepts a set of characters, that you specify using brackets. This example accepts phone numbers with area codes 541, 543, 546, and 549. |
|
|
Accepts phone numbers whose area codes start with 21. Character sets also allow the use of a hyphen (-) to indicate a range of characters. Ranges don’t have to be numeric: |
|
|
Accepts any six characters, as long as the first two are B and L. |
|
|
Accepts any string of characters, of any length, beginning with BL and ending with 5. |
|
|
Accepts four-digit numbers prefaced by SKU and followed by the letter A, B, or D. The first digit must be 0, 1, or 2. |
|
|
Accepts any date between March 15, 2002 and today. |
|
|
Accepts any number in this list. Note that numbers don’t require quotation marks. |
The Like Operator
When you want the input to match a certain pattern, turn to the Like operator. For example, for a string of five digits (such as a Zip Code), use Like "#####". (The quotation marks are required.) For a string of five characters (digits, letters, or punctuation), use Like "?????". Like recognizes three wildcard characters:
? Represents any single character |
# Represents any single digit |
* Represents zero or more characters |
For example, "###" means three digits, but "###*" means three digits followed by anything else.
The Between Operator
To restrict input to a continuous range of values, use the Between operator. For instance, Between #2004-02-01# And #2005-02-01# will restrict input to dates within the year between February 1, 2004 and February 1, 2005. (Note the special syntax required for bracketing dates.) To restrict numerical input to values between 100 and 500, enter Between 100 and 500. Between can be applied to dates, numbers, and even text (to restrict entries to, say, words starting with letters between B and L—this is used primarily in queries). It is inclusive, which means that the endpoints are always included.
The In Operator
To restrict input to a set of discrete values, use the In operator. For example, In ("MA", "CT", "RI", "VT", "NH", "ME") restricts input to these state abbreviations. (The quotation marks are required.) A better solution might be to provide a combo box with a value list on your data entry form; that way, users are guaranteed to follow the rules. (See “Activating the Wizards” in Chapter 5.)
Record-Level Rules
Sometimes you want a validation rule to enforce a relationship between different fields in a record. For instance, you might want to ensure that proposedFee is greater than the sum of fixedCosts and variableCosts. The rule is simple: > fixedCosts + variableCosts. (As you might expect, > means “greater than.”) Note, however, that you can’t assign this validation rule to the proposedFee field in your table, because field-level rules can’t reference other fields. Instead, open the table in Design View, click View → Properties to display the properties sheet, click on the Validation Rule field, and enter this rule: [proposedFee] > [fixedCosts] + [variableCosts] (see Figure 3-12). The brackets tell Access you’re referring to fields.
Complex Validation Rules
THE ANNOYANCE: I’ve ended up with some duplicate entries in my contacts database, and it’s my fault; when I enter a “new” person, I often forget to check to see if she’s already in the database. I tried to create a validation rule to check if the person I’m entering is already there, but I just couldn’t figure out how to do it. Help!
THE FIX: Validation rules do a good job of catching data entry errors in fixed patterns, such as Social Security and phone numbers. But for more complex rules, you’re better off writing a bit of VB code and placing it in the control’s Before Update event. By doing the validation there, you enable Access to catch bad data, display a custom error message for the user (see Figure 3-13), and cancel the update.
You can also allow the user to override the rule. The following example checks for duplicate names in a contacts table, issues a warning, and allows an override in case two different people have the same name. (You’ll need to have at least a little VB knowledge to adapt it—it’s not just a question of replacing placeholders.)
Private Sub Form_BeforeUpdate(Cancel As Integer) If DCount("*", "tblContacts", "lastName = """ & Me!txtLastName _ & """ And firstName = """ & _ Me!txtFirstName & """") > 0 Then If MsgBox("There is already a user by that name in the" _ & "table. Do you wish to add it anyway?", _ vbYesNo) = vbNo Then Cancel = True End If End If End Sub
Subtypes and Supertypes
THE ANNOYANCE: I’m designing a database for a retreat center that hosts many different kinds of events—workshops, weddings, and so on. I’m pretty sure that each kind of event needs its own table, because the kind of information we collect for each event type varies. Do I create a single table with everything in it, or multiple tables that I somehow relate to each other?
THE FIX: Workshops, weddings, and the like are all subtypes of one supertype: events. Since subtypes and supertypes are common in the world, they’re pretty common in databases as well. For example, employees can be subdivided into union, temporary, and exempt. All events have some common attributes (name, location, and so on), but different kinds of events have their own unique attributes as well (weddings have caterers, workshops have instructors, and so on). When faced with organizing this kind of information, a database designer must choose between using a single table, using multiple unrelated tables, or creating a supertype table (which includes the common attributes) that’s related to multiple subtype tables (which include the unique attributes).
The simplest option is a single table that accommodates all the types. This works if the data you collect is mostly the same for all event types. For example, if every type of event has a start date, an end date, a sponsor, and so on, then just go ahead and create a single events table with a field that specifies the event type. As discussed above, you’ll probably need a few extra fields for attributes that are specific to certain event types. That’s fine; just leave those fields blank when they’re not needed.
However, if event types vary significantly, using one table doesn’t make sense. Weddings and workshops, for example, may require a completely different set of fields, so you could just go ahead and create separate tables for them. This is not a bad solution, but it can lead to complications later. For example, you’ll need to collect payments for both types of events, so your payments table will need to have a foreign key field for the ID of the event—but how do you know whether that ID refers to the weddings table or the workshops table?
A better solution is to use supertypes and subtypes in your design, as shown in Figure 3-14. This gives you the best of both the single table and multiple table solutions.
First create an events table, and create the fields common to all event types. Then create subtype tables, such as weddings, workshops, and so on. Give each subtype a primary key that’s compatible with the primary key of your supertype. For example, if your events table uses an AutoNumber ID field, give each subtype table an ID field that’s a Number data type, with the Field Size set to Long Integer. (The foreign key field must be able to hold any data that would be valid in the primary key field, and generally speaking, you make the foreign key the same data type as the primary key. It’s not obvious, but AutoNumbers are Long Integers.) Next, create a relationship between the supertype and each subtype by linking the primary keys of the two tables (one supertype, one subtype) in the Relationships window. When Access sees you linking two primary keys, it knows that this is a one-to-one relationship—i.e., that each record in the supertype table will correspond to exactly one record in one of the subtype tables.
For most purposes, you can treat tables that are in a one-to-one relationship as if they are a single table. Add them both to a query, and the join on the primary key field makes them behave just like a single table. Similarly, there’s no need to use a subform when including both tables on a form. Just base the form on a query where they’re joined—that’s what the Form Wizard does.
The benefit of this design reveals itself in situations where you need to base a foreign key on the supertype’s primary key. For example, every row in the payments table needs the ID of the event that the payment is for. As we saw above, without a supertype table you’d have to mix keys from different tables in the same foreign key field. With a supertype table, the design becomes a simple one-to-many relationship between the events (supertype) table and the payments table.
Duplicate Records
THE ANNOYANCE: In our equipment database, we enter most equipment by serial number—but we can’t use serial numbers as our primary key, because some items don’t have them. Unfortunately, we don’t always remember to check first to see if a serial number is already in the database, so we’re getting duplicate records. How can we prevent this?
THE FIX: As you suspected, the best way to avoid duplicate records is to use a “natural” primary key, such as serial number or employee badge number, that is uniquely associated with the data you’re recording. Since Access won’t let you add duplicate primary keys, you can’t add the same item twice. In practice, though, it’s rarely this easy, because usable natural keys are hard to find. And as you’ve discovered, you may need to record data where the natural key is missing, or there may be duplicates of natural keys that you thought were unique (such as two people with the same Social Security number—it happens!). That’s why we usually recommend that you use an AutoNumber ID field as a primary key (see “Relationship Angst” and the sidebar “Primary Keys and Duplicate Records,” earlier in this chapter).
If you’re sure that the serial numbers will be unique, you can avoid duplicates by adding a unique index to the serial number field. In table Design View, simply set the field’s Indexed property to “Yes (No Duplicates).” Unlike a primary key field, a unique field can have null values, but Access will still prevent you from adding duplicate values. (If you sometimes need to allow duplicate values but want the system to warn you before accepting them, see “Complex Validation Rules,” earlier in this chapter.)
Relationships Window Ghosts
THE ANNOYANCE: I’m trying to delete a table from my database, and Access is telling me that I can’t because the table is involved in relationships with other tables. But when I look in the Relationships window, no such relationships are listed.
THE FIX: The Access Relationships window is at best a crude tool, and it can be misleading. Remember that it doesn’t automatically show all relationships—it just shows relationships for tables that you’ve added to the window. That means that in order to see a relationship, you first have to add the relevant tables to the Relationships window. If you don’t mind discarding your current Relationships layout, choose Edit → Clear Layout, then choose Relationships → Show All. If a relationship exists, it will show up.
Before you discard your current layout, look for tables that may have scrolled off the screen. Select any visible table in the Relationships window, and then tab through all your tables. Each time you tab, watch carefully to see if the highlight vanishes; if it does, that means that the current selection is offscreen. Hit Delete to remove that table, and its relationships, from the Relationships window (since it’s offscreen and there’s no way to get it back on!). Do this for any offscreen tables, then add back any tables you need, putting them somewhere you can see them. Once you’ve found the ghost relationships, you can alter them so Access will allow you to dump that table.
Relationships Window Layout Distress
THE ANNOYANCE: Our inventory database has 103 tables and 95 relationships. I’ve spent hours laying them out in the Relationships window just the way I want. Recently, I needed to import everything into a new database. The relationships imported fine, but the layout is gone. I want to smash something….
THE FIX: You’re not the first person to go ballistic over this one, and fortunately, Michael Kaplan was mad enough to fix the problem. His free SysRel Copy Utility (Figure 3-15) lets you transfer Relationships window layouts between databases (which also means you can share layouts between two users of the same database). You can download this utility from http://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12. You might also want to have a look at Stephen Lebans’s free code to save and restore multiple relationship layouts (http://www.lebans.com/saverelationshipview.htm). If you need a professional tool for viewing, documenting, and analyzing your design, check out Total Access Analyzer (http://www.fmsinc.com/products/analyzer/), which costs $299 and up.
Attack of the Nonexistent Tables
THE ANNOYANCE: Much to my horror, Access has populated my Relationships window with tables that don’t exist. I do have an Orders table, but where did Orders_1 and Orders_2 come from!?
THE FIX: Whenever a table has more than one relationship to another table, Access adds extra copies of that other table to the Relationships window. Maybe Access thinks this makes it easier to lay out the relationship lines. In any case, the extra copies are distinguished by adding an incremented suffix—if Orders is the first copy, Orders_1 is the second and Orders_2 is the third (see Figure 3-16). The underlying tables are not affected; these names show up only in the Relationships window. The issue typically comes up when you use the Lookup Wizard “data type” in table design, because the wizard adds a relationship (not to mention an index) as part of the lookup process. (This is one more reason many people avoid the use of lookup fields. See “Hide Foreign Keys” in Chapter 5 to see how to avoid them.) The bottom line is, don’t worry about the extra tables; they’re just visual clutter in the Relationships window.
IMPORTING, LINKING, AND EXPORTING DATA
Errors with Imported Data
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.
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.
These rules make sense—except when the first rows of data are not representative of the rest of the data in the column. For example, a list of part numbers could, by chance, start out with all numbers (10012, 11201, 23113…) and then, further down, become alphanumeric (AQ12013, E4320…). You’d need a Text data type for that field, but what you’ll get is a Numeric field—and all those alphanumerics will generate conversion errors.
A simple fix is to set up your Access table with the correct data types before you do the import. But not so fast! If you import the worksheet into your Access table, Access will ignore the data types you so lovingly crafted and will spit out errors. (For some reason known only to the kids in Redmond, Access does allow you to set data types when importing, but only if you’re importing a text file.) Instead, once you’ve set up your Access table with the correct data types, simply copy and paste columns from your Excel worksheet into your Access table. Select a column in Excel, then open your Access table in Datasheet View and choose Edit → Paste Append. This way, your data types will be preserved. Note: if you have fields in your Access table that aren’t in your Excel worksheet, first open your table in Datasheet View and arrange your columns to match your Excel worksheet. Then select those columns before you paste.
Another simple solution is to insert a single dummy row at the top of your spreadsheet with the desired data types. For instance, a text value in that row will coerce Access into assigning the Text data type to the whole column. Once you’ve imported the data, just delete the dummy row. Yet another trick is to precede the first value in an Excel column with an apostrophe. Excel will ignore the apostrophe but will be forced to store the number as text, which will then import as the Text data type in Access. (You can also use Excel’s
TEXT() function to tell Excel to store numeric values as Text.)
Finally, a clean, flexible solution is to save your worksheet as a text file (say, CSV) and then import it, setting the data types in Access’s Import Specification dialog. If you have a lot of spreadsheets to import, consider a commercial tool such as 4TOPS Excel Import Assistant (http://www.4tops.com), a $99 Access add-in that gives you full control over the process.
Linked Spreadsheet Woes
THE ANNOYANCE: I have an Access table that’s linked (through Access’s Get External Data dialog) to an Excel worksheet containing student data from our registrar. The data in the worksheet is fine, but when I view it in Access, some fields are truncated, some have garbage, and some appear only as #NUM!. All I wanted it to do was link.
THE FIX: Linking to an Excel worksheet presents many of the same problems as importing data from an Excel worksheet (see the previous Annoyance), because Access must likewise assign specific data types to the worksheet’s data. If you’re seeing nonsense data or #NUM! values, either you’ve got bad data, or Access has chosen the wrong data type.
Note
If you only need to display (not edit) the worksheet data in Access, you can simply link to or embed an Excel object in your Access form or report; there’s no need to import the data or create a linked table. In form or report Design View, click Insert → Object, choose “Create from File,” and then browse to your worksheet file and click OK, then OK again. The Excel worksheet will show up as a read-only (i.e., static) image—but if you check the “Link” box when you insert it, it will be kept in sync with the original worksheet, and the data will always be current.
Bad data results when you have items of one data type mixed in with items of another data type (e.g., a single numeric value mixed into a column of dates). Databases, Access included, do not like items of different data types in the same column. As an example of Access guessing the data type wrong, let’s say you have a column of Zip Codes, and the rows that Access analyzes when trying to determine the data type happen to hold only 5-digit codes. Access will assign the Numeric data type to the field—and then shriek #NUM! when it encounters Zip+4 Codes later in the worksheet. (Since Zip+4 numbers include hyphens, they must be stored as Text.)
Access assigns each column in a linked spreadsheet a “data type” in order to treat it as a “table.” And once Access assigns that data type, there’s no way to change it. Instead, you must change the way the data is represented in Excel. Usually this means representing numbers as text. You can’t do this by simply formatting the cells, which doesn’t change the underlying data. You must do it by adding a preceding apostrophe (which will be ignored by Access but will cause it to display the numbers as text), or by using Excel’s
TEXT() function. Note that it’s not enough to adjust only the first row of data in Excel; you must alter at least 25 rows so that Access thinks the majority of them are Text.
As for truncated data, if fixing your data types doesn’t solve the problem, we’ll guess the data is coming from worksheet cells that hold more than 255 characters. Access maps such cells to the Memo data type. Memo fields can contain up to 65,535 characters, but by default Access formats the field to show only up to 255 characters. The data is still there, but Access ignores it, and you can’t change the design of a linked table. If you want a ton of text data in Access, you’ll have to import it, rather than linking to it—which is often a better solution in the long run, anyway. If you must have your data available in spreadsheet form, import the data into Access and then link to it from Excel.
Data Incorrectly Imported as Dates
THE ANNOYANCE: All our spreadsheets use our company’s internal (and nonstandard) date format of “yyyymmdd.” These are not dates, they are numbers stored as text—don’t ask me why. When I try to import them into Access, they are assigned the Text data type, but I need them to be dates in Access. And the Import Wizard doesn’t let me specify the data type!
THE FIX: The easiest solution is to save each worksheet as a text file (e.g., in CSV format) and then run the Import Wizard (File → Get External Data → Import). Why save the worksheet as text? The wizard won’t let you specify data types if you try to import a worksheet, but it will if you import a text file. Here are the steps:
As soon as you attempt to import the text file, the Import Text Wizard will open. Click the Advanced button to create the specification (see Figure 3-18) and set up the dates to match your format.
For “yyyymmdd,” set the Date Order to “YMD,” clear the Date Delimiter box, and make sure that both the “Four Digit Years” and “Leading Zeros in Dates” boxes are checked. (If you think you’ll need to do this import again, click the Save As button and give this import spec a name. Next time, just click the Specs button to recall it.)
Once the import spec is completed, step through the wizard, assigning appropriate data types to each column of your data. The wizard will apply your date specification to any columns that you specify as dates.
An alternative solution—useful if your dates are in a format that the wizard can’t parse—is to import the spreadsheet into a dummy Access table, leaving the dates as Text. Then create an append query (open a new query in Design View, then click Query → Append Query) that pulls the data out of the dummy table, formats the dates correctly, and appends them to the true table. To do this, first create an append query as if you were going to simply copy the data between the two tables, then replace the date field (i.e., the date that you’ve added to the Field line) with an expression similar to this one:
CDate(Mid([myDateField],5,2) & "/" & Right([myDateField],2) & "/" & Left([myDateField],4))
This expression parses out month, day, and year from the text string and then coerces the result into a Date/Time data type (for instance, it will convert 20051203 to 12/03/2005).
Obscure Excel Import Errors
THE ANNOYANCE: I often need to import Excel worksheets created by our financial people into Access. Most of the time it works fine, but occasionally I get obscure errors such as “Unparsable Record” or “Subscript out of range.” I have no idea what these errors mean.
THE FIX: The ImportErrors table’s error messages are usually fairly meaningful (see Figure 3-19).
However, your error messages require further explanation. According to Microsoft, a record is “unparsable” when the text delimiter character (e.g., a quotation mark) is included in the data itself. (Such delimiters should be doubled, as in "This is a ""sample"" with internal quotes.".) But this is only a problem with text file imports. Your problem is due to a more common obstacle: too many characters in the record. Records in an Access table are limited to 2,000 characters each (except for Memo or OLE fields, which are stored separately). A row in a large worksheet can easily exceed this amount. If it does, Access populates as many fields as it can, leaves the rest empty, and slaps an unparsable error in the ImportErrors table.
When a “Subscript out of range” error shows up in the Import Wizard, it typically indicates that you’re importing a worksheet with too many columns. An Access table can have a maximum of 255 fields; a worksheet, up to 256 columns. (Note that if you get this error after seeing another message, such as "ActiveX component can’t create object,” it’s not a problem with your worksheet; some other index is out of range, and your Access installation has gone awry. Follow the procedures in “Agonies of a Sick Installation,” in Chapter 1, to fix it.)
For more information on these errors, check out the "Troubleshoot importing and linking” section in Access Help. It’s listed under Working with Data → External Data → Importing, Exporting or Linking to Data in the Table of Contents—but not in Access 2000 (sigh). It can also be found online at http://office.microsoft.com/en-us/assistance/HP051885461033.aspx.
Unhelpful Error Messages During Import
THE ANNOYANCE: I have some old data stored in a worksheet that I’m trying to import into an existing table in Access. The Import Wizard seems to go along fine, but then, when it’s all done, I get the brilliant, informative message: “An error occurred trying to import file <filename>. The file was not imported.” Grrr!
THE FIX: This error message is really dumb. If this is the only error message you’re getting, check that your column headings in Excel match your field names in Access exactly; if there is so much as an extra space, the import will fail. Access doesn’t care about the order of the columns, and you don’t have to put data in every field in the table. But Access must be able to find a matching field in your table for every column heading in Excel. If the column headings and field names do match up precisely, there may be extraneous data in your worksheet, outside the columns you’re trying to import. Sometimes a stray keystroke far offscreen is the culprit. Deleting the cell’s contents isn’t enough—you must delete the empty columns, too. And wait—it gets better. Often this error is preceded by a long error message (see Figure 3-20) that begins: “Microsoft Access was unable to append all the data to the table.” Then it goes on to explain the reasons why zero records might have been deleted or lost!
This would be hilarious if you hadn’t needed to finish your conversion 20 minutes ago.
What’s it mean? Most likely, your worksheet has data that violates an existing validation rule, or is missing data in a required field. You’ll need to fix your data.
Can’t Import Word Tables
THE ANNOYANCE: I’m embarrassed to admit that I have years’ worth of business data stored in Microsoft Word tables. I’ve tried to import this data into Access, but the Import Wizard doesn’t recognize Word files. Don’t tell me I’m going to have to type all this data into Access!
THE FIX: Access can’t directly import Word documents (or their tables). One possible fix is to copy the tables from Word and paste them directly into an Access table. Another is to copy the tables into a format that can be imported, such as an Excel worksheet.
For the former process, copy your table in Word, open the Access table in Datasheet View, and choose Edit → Paste Append. Access will add rows as needed. Of course, the data coming from Word must be compatible with the data types of the fields in Access—otherwise, you’ll get a PasteErrors table, and Access will skip those rows. Note that you can paste into any contiguous region of your table, so if you have fields in Access that aren’t in Word, simply open your table in Datasheet View and arrange your columns to match your Word table. Then select those columns before you paste.
Since data in Word tables is seldom arranged to match an Access table, it’s often useful to move it to Excel first, using Copy and Paste Special (Text). Put each table on a separate worksheet and massage the data as needed (see the sidebar "Massaging Word Tables”) before importing the Excel sheet into Access. As you can see from the preceding Annoyances, importing from Excel is not without its tribulations, but it’s doable.
Copy/Paste to Excel Is Broken
THE ANNOYANCE: When I used Access 97, I didn’t have any problems copying and pasting from a datasheet to an Excel worksheet. (I like to use Excel’s statistical functions for computations.) But since I “upgraded” to Access XP, my numbers show up in Excel as text, and I have to fiddle with them before I can use them. This is an improvement?
THE FIX: It isn’t! In fact, it’s a bug in Office XP (see MSKB 328933). Fortunately, it was fixed in Office XP Service Pack 3 (for details, see MSKB 307841). In the meantime, the workaround is to Paste Special into Excel, choosing Excel’s BIFF5 (Binary Interchange File Format) format.
Before importing your data into Access, it can be useful to do a little massaging in Excel. First of all, if you’re storing, say, all of your dates or all of your product codes in the same row, you’ll need to use Excel’s transpose function to turn those rows into columns, since Access expects like items to be stored in columns. Simply select your data, then copy and paste it into a new worksheet, again using Paste Special but this time checking the Transpose box (Figure 3-21). Once your data is in columns, check each column to be sure that all the items match the data type you’re intending to import them to. Then open Access and run the Import Wizard by clicking File → Get External Data → Import.
If you don’t have Excel, open Word, select Table → Convert → Table to Text, click “Separate text with commas,” click OK, and import the resulting text file. This may be a good solution if there are line breaks in your data within table cells. Managing these isn’t trivial, but if you need to do it, consult MSKB 290169 for detailed instructions.
Exporting Reports Produces Weird Numbers
THE ANNOYANCE: I’m trying to export an Access 2003 report to Excel. The Access report includes a text field of internal accounting codes (such as 002-00001-003) that show up as weird numbers (e.g., 37653 and 38018) in Excel. Why is this happening, and how can I stop it?
THE FIX: Oh, the woes of exporting Access reports! For some reason, when Access exports text fields in a report, it perversely tries to interpret these fields as something numeric or date-like—despite the fact that they’re clearly defined as text. Access considers anything with one or two hyphens (or slashes) as a date, and strings such as 6a or 20P will be interpreted as times. (Why the weird numbers? That’s how Excel represents dates and times! If you apply a date/time format to those cells, you’ll see what’s going on.) To make matters worse, Access interprets text strings such as 20E070 or 5D4 as if they were numbers in scientific notation, and they’ll show up in Excel as 2E+71 and 50000. Ridiculous!
If it makes you feel better, this is a known Access bug (see MSKB 823222) and is just one more reason to avoid exporting reports. As a general rule, you’ll have much better luck exporting tables and queries to Excel. But if you absolutely must export a report, the workaround is to append an apostrophe to your text field. Open the report in Design View, and open the properties sheet of the text box that contains your field. If, for example, the name of the text field is acctCode, set the Control Source to =[acctCode] & "'" (see Figure 3-22). The ampersand character concatenates two strings, so this expression appends an apostrophe to whatever’s in your text field. Make sure that you name the text box itself something different from the control source (e.g., txtAcctCode), or you’ll get #Error!.
Now your data will export to Excel as text. The only problem is that it will show up in Excel with apostrophes. To get rid of them, you can use Find/Replace and replace the apostrophes with nothing, or run the snippet of code that Microsoft provides in the above-mentioned Knowledge Base article, which will remove them automatically. We admit it: this fix is ugly. Try to avoid it.
Hidden Apostrophes in Exported Data
THE ANNOYANCE: I exported my table to Excel (using Excel 97-2002 format), and all the text strings have appeared with an apostrophe in front, like this: ‘my text. Whose idea was that?!
THE FIX: It’s not well advertised, but Access and Excel both use this cute apostrophe trick to ensure that text data isn’t mistakenly interpreted as numbers or dates. What you’re seeing are “hidden apostrophes,” and you can’t get rid of them with Find/Replace. But you don’t need to—they display only in the formula bar, and they don’t print out. You can prevent these rogue characters from appearing by checking the “Save formatted” box in Access’s File → Export dialog box (see Figure 3-23). Note that this box is grayed out when you’re exporting a report, but it’s available for tables and queries.
If you’re running an older version of Access or Excel, and “Save Formatted” isn’t available, you can remove the apostrophes using this simple code:
Sub RemoveApostrophe() Dim CurrentCell As Range For Each CurrentCell In Selection If CurrentCell.HasFormula = False Then CurrentCell.Formula = CurrentCell.Value End If Next End Sub
In Excel, save this code as a macro by choosing Tools → Macro → Visual Basic Editor and pasting the code into a module. If there’s no module available, choose Insert → Module to create one. It will be saved as part of your worksheet. Before you run the code, select the range of cells in the worksheet that you want to clean. Then choose Tools → Macro → Macros and run the RemoveApostrophe macro.
Exported Numeric Data Is Truncated
THE ANNOYANCE: We’ve got three years’ worth of geophysical data stored in an Access database using the Double data type—a floating-point type that can handle very big numbers (such as 1.0034 x 1023) with any number of decimal places (e.g., 1.930024). We need to export the data as text files, but Access truncates all our numbers to two decimal places!
THE FIX: According to Microsoft, this is a feature, not a bug (see MSKB 153364). Fortunately, there’s a simple workaround: set up a query for your table, and use the Format function to preserve your decimal places. For example, suppose your table has a field called sensor13, which represents the reading of the global subterranean frammelstat sensor, and you want to preserve four decimal places to the right of zero. Add that field to your query, and apply formatting such as the following to it:
exportSensor13: Format([sensor13],"##0.0000")
Then export the query as text. Choose File → Export, and in the “Save as type” drop-down, select “Text Files.” Then click Save, Save All, or Export. This summons the Export Text Wizard. By default, the wizard will surround your data with quotes—which you probably don’t want. Click the Advanced button and set the wizard’s Text Qualifier field to “{none}” (see Figure 3-24). You can save this setting as an export specification from this dialog, using the Save As button.
Exported Text Data Is Truncated
THE ANNOYANCE: I set up a macro in Access to export a table to Excel. The table has several memo fields, and they all appear chopped off in Excel. What’s the problem?
THE FIX: There are two possible causes:
If you’re exporting to an older Excel format (that is, anything before Excel 97), cells are limited to 255 characters.
If Access interprets your data as a text field rather than a memo field, it will truncate the data before it reaches Excel.
When you’re designing this kind of macro, the
OutputTo action seems like a good choice—it even lets you select Excel 97-2002 format. Unfortunately, because it’s an old function (and has now been superceded), OutputTo is actually limited to the older Excel 95 format—so it treats memo fields like text fields. Instead, you must use
TransferSpreadsheet, with its export option (see Figure 3-25). Set the Spreadsheet Type to “Microsoft Excel 8-10” (i.e., 97-2002). For export, the Has Field Names field is ignored. The same goes if you’re using these functions in VB code.
When exporting reports (and only reports), Access always uses the older (Excel 95) format, regardless of your choices in File → Export. This virtually guarantees that memo fields will be truncated. The best approach is to avoid exporting reports to Excel; export the underlying query instead.
You may find other data, such as calculated expressions that exceed 255 characters, truncated as well. Likewise, if you concatenate text fields—for instance, if you export mailing addresses as a single expression—Access will treat the expression as a text field and truncate it. The workaround is to convert your query to an append query that stores its results in a table with a memo field. Then export the memo field data; it won’t be truncated.
Miscellaneous Export Annoyances
THE ANNOYANCE: We have a dozen Access 2003 databases containing clinical research data. I just noticed that when I export a table or query with null fields to Excel, and those Access fields turn into Excel cells, data from the adjacent cell slides over to fill the empty cell produced by the null Access field. The ultimate result is that several subjects’ blood pressure numbers become their ages, and their ages become their heights. Do I need to call our malpractice attorney?
THE FIX: Put down that phone. This is one of Access’s many maddening export annoyances. This is a known bug (see MSKB 294410) that shows up only if certain conditions are met. To swat it, simply install the latest Jet service pack. Here are a few more export annoyances of note, and how to get around them:
- Junk in hyperlink fields
When you export hyperlinked fields, such as URLs or email addresses, you may see junk such as this: phil@yahoo.com #
http://phil@yahoo.com. That’s because Access stores hyperlink data in multiple parts, including a display part, a full address, a subaddress (if needed), and so forth. At least, that’s the idea—the meaning ofhttp://phil@yahoo.comis beyond us. If you want to strip out the junk, create a query using an expression like this:expEmailAddress: HyperlinkPart([EmailAddress], 0)Put this expression in the Field line of the query grid to include it in your query results. You can get more information about the
HyperlinkPartfunction in VB Help. See “Create Email Links” in Chapter 7 for an alternative to using the Hyperlink data type.- “Too many rows” error when exporting to Excel
Excel 97 and later versions are limited to 65,536 rows. You can’t export more than that number of rows to a single worksheet. But if you’re getting this error with fewer rows, it’s because Access thinks you’re exporting to an older version of Excel (the limit used to be 16,384 rows). This happens if you use the
OutputTofunction or action (calling the function with a macro); instead, use theTransferSpreadsheetaction. It also happens if you’re exporting a report; export the underlying query instead.- Wrong data type
When exporting Access data to Excel, or another brand of database, the data types that appear in the result are sometimes incorrect. For instance, your query may output a perfectly proper Access date string that Excel insists on interpreting as text. The fix is to use a VB function in your query to coerce the data type so it will wind up in the appropriate format in Excel (or wherever). These coercion functions all begin with “C,” such as
CDate, CStr, CInt, and so on. For instance, to coerce a text string to be treated as a date, addCDate(myDateText)on the Field line of the query grid, instead of the raw date field. Of course, Access will throw an error if the text string isn’t something that can be interpreted as a date.- Can’t export to a specific worksheet in an Excel workbook
When you export to Excel using File → Export, or a macro, the only thing you can specify is the name of the workbook. If you need to place data on a specific worksheet, or even in a specific location on a specific worksheet, and you don’t mind digging into some VB code, use
TransferSpreadsheet. Despite what Access’s Help file says, you can export to a named range using the “range” argument. This enables you to specify exactly where in your Excel workbook the data goes. Depending on your versions of Access and Excel, you may also be able to use sheet names and unnamed ranges. See “Automating Import/Export,” later in this chapter, for details.
Putting Data on the Web
THE ANNOYANCE: We want to make the data in our bibliographic database available on the Web. We tried exporting static HTML, but our data changes too frequently and no one wants to maintain the static pages. We would use ASP pages, but our IT department doesn’t run an IIS server. Aargh! Access has so many different ways to do this, but none of them seem to fit. Why couldn’t they just give us one good way to get our data on the Web?
THE FIX: The ability to publish data that’s always up to date and is readable by anyone with a browser and an Internet connection is transforming our information culture. It’s no accident that Microsoft tried to make Access web-friendly; it’s just too bad they didn’t do a better job. In this fix, we’ll go over your options for putting Access data on the Web, and make some recommendations.
Two primary factors determine how you generate web pages from Access data: whether your data is static or dynamic, and whether you want it to be read-only or editable.
Note
While all of these solutions produce some kind of web page, that alone doesn’t actually get your data on the Web. For that, you’ll also need a web server—that is, a computer that’s connected to the Internet and is running server software such as Apache or IIS. The server responds to browser requests by delivering your web pages.
Static and Read-Only
If your data doesn’t change (e.g., if it’s a collection of historical data) or changes only infrequently, and your goal is to publish it on the Web so people can view (but not edit) it, then static HTML pages are a reasonable solution. Using File → Export, you can export tables, queries, and reports; just set the “Save as” type to “HTML Documents (*.html; *.htm).” For tables and queries, Access creates an HTML page that looks like Datasheet View. In older versions of Access, exported reports also look like datasheets, but starting with Access 2002, they look like reports. Forms still export as datasheets based on the underlying record source.
Left to its own devices, Access creates very basic HTML pages. There are a couple of options that let you enhance them. For tables and queries, checking the “Save formatted” box on the Export dialog usually produces a nicer result: more polished visually, with shading, borders, and other professional touches. (Reports always have this option checked.) You’ll get an HTML Output Options dialog that lets you choose both a character encoding and a template. If you need to handle international characters (with diacritics, etc.), you’ll want one of the Unicode options. The template is any web page that you want Access to embed the data into; without a template, Access will just create a standalone page.
To tell Access how to embed the data in your template, simply include the special tags in your web page: use <!—AccessTemplate_Title—> for the title and <!—AccessTemplate_Body—> for the data; if the export spans multiple pages, you can also use the FirstPage, PreviousPage, NextPage, LastPage, and PageNumber tags. Access replaces the tags with their corresponding items. Only reports will be broken into multiple pages by Access.
Dynamic and Read-Only
If your data changes frequently, static HTML pages are a poor solution. A better option is to build the web pages on the fly. With this approach, every time a browser requests data, the database is queried and the results are built into a nice, neat page. This is how most database-backed web sites work, and it’s something almost any web developer can set up for you.
In Access, you can create dynamic web pages by exporting tables and queries in Active Server Pages (ASP) format. Use File → Export and set the file type to “Microsoft Active Server Pages (*.asp).” You’ll get the same display options as with static HTML. The difference is that ASP pages don’t include a snapshot of your data; instead, they use code that knows how to retrieve the data from the database. During the export, you’ll need to specify a Data Source Name (DSN)—that is, the name of the ODBC data source on your web server that connects to your database.
ASP has one significant limitation: it only works with Microsoft’s web servers (IIS Version 3.0 or later, and Personal Web Server). If you don’t want to be locked into that choice, see our recommendation in the next section.
Dynamic and Editable
A fully interactive web application allows users to view and edit data on a web page. Microsoft’s solution is Data Access Pages (DAP)—but the technology comes up short.
The goal of DAP is to deploy Access-like functionality in Internet Explorer. When your browser loads a DAP page, a suite of ActiveX controls known as the Office Web Components is loaded. These provide database connectivity and an enhanced user interface. In the Access 2000 version of DAP, users had to have an Office 2000 license to view and edit the data. With Access 2002 or 2003, unlicensed users can view, but not edit, data. As you can see, DAP is not a general-purpose web technology, but rather a web-based extension of Microsoft Office. It tends to be used mainly on intranets, as an alternative to giving everyone a copy of Access. DAP pages are complex to design and tricky to debug, and we don’t recommend using them.
Besides, web developers solved this problem years ago, with an open source scripting language called PHP. (PHP was designed precisely to make it easy to put data on the Web.) For example, the following is the complete HTML and PHP code for a web page that queries a database and then displays the results in a table. Even if you don’t understand the details, you can see that the amount of code required is very small and is mostly self-explanatory. The code connects to the database, submits the query, then loops through the results row by row and outputs the data in apple-pie order. Although this example only displays data, it is equally easy to accept input using an HTML form and then write it to the database.
This code builds an HTML table with all the authors and titles found in the tblBooks table:
<html>
<body>
<?php
$conn=odbc_connect("MyDateStoreName", "user", "pwd");
if (!$conn)
{
exit("Connection Failed: " . $conn);
}
$sql="SELECT * FROM tblBooks";
$result=odbc_exec($conn,$sql);
if (!$result)
{
exit("Error in SQL");
}
echo "<table><tr>";
echo "<th>Author Name</th>";
echo "<th>Book Title</th></tr>";
while (odbc_fetch_row($result))
{
$author=odbc_result($result,"AuthorName");
$title=odbc_result($result,"Title");
echo "<tr><td>$author</td>";
echo "<td>$title</td></tr>";
}
odbc_close($conn);
echo "</table>";
?gt;
</body>
</html>
We don’t expect you to write your own PHP code based on this example, but it’s reasonably easy to do—and worth a small investment of time to learn how. (A good place to start is David Sklar’s Learning PHP 5, also published by O’Reilly.) With a little HTML and PHP wrangling, you can make your Access data web-accessible. You’ll also be able to use any platform (Windows, Linux, and so on), any web server (Apache, IIS, and so on), and any browser. That’s a big win.
Automating Import/Export
THE ANNOYANCE: Every week we get the same worksheets from human resources and have to import them into an Access database. Isn’t there some way to automate this dreary process?
THE FIX: If you just want to click a button that imports or exports a specific file, it’s pretty easy. In this example we’ll discuss importing, but you can export using the same methods. Depending on the target of your import, you’ll use either
TransferSpreadsheet (for importing data from an Excel or Lotus worksheet),
TransferText (for importing from a text file), or
TransferDatabase (for importing from another database). To put these methods (which belong to the DoCmd object) into the Click event of a button, see “How to Create an Event Procedure” in Chapter 0. To add a custom error handler, see “Better Error Handling” in Chapter 7.
For instance, to import the A1:G12 range from Sheet1 of the hr_weekly.xls worksheet, you’d use something like this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _ "tblEmployees","C:\data\hr_weekly.xls", True, "Sheet1!A1:G12"
“True” here means that the first line of the worksheet has column headings. To import the whole worksheet, just omit the last argument. As always, consult VB Help for more information on the various arguments. If you need to let the user choose the file to import, see “File Choosers” in Chapter 7. Note that while Help insists that you can’t use the “range” argument with an export, this is a lie. All Access versions covered in this book can export to a named range, and later versions of Access and Excel (2002 and later) can export to specific sheets and unnamed ranges as well.
If you want to automatically import or export the same set of files each week, it takes a little more work. Start by creating a table, say “tblMyImportFiles,” that lists the names of the files you want to import and the receiving tables (see Figure 3-26). Then iterate through the table in code, importing each file into its table. If you need to support multiple file types, just add a column to the table to store that information.
The following code iterates through the tblMyImportFiles table and imports each spreadsheet listed in the fileName column into the matching table listed in the import-Table column:
Public Sub cmdImport_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strImportTable As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMyImportFiles")
With rst
If .RecordCount > 0 Then
Do Until .EOF
strFileName = ![fileName]
strImportTable = ![importTable]
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strImportTable, _
strFileName, True
.MoveNext
Loop
End If
.Close
End With
End Sub
Put this code in the Click event of a button, as shown here. Note that TransferSpreadsheet will append to your table. In some cases, you may want to delete the old records first before doing the import.
One last thing: if you receive an error such as “Field ‘F3’ doesn’t exist in the destination table,” this means that Access thinks there’s an extra column in the worksheet, and it doesn’t match any field in the table. There probably was data in a column that has since been cleared. Remember that clearing a cell’s contents isn’t enough—you must delete the column, or Access will attempt to import it. Just select the whole column and select Edit → Delete.
Exporting Data on the Fly
THE ANNOYANCE: I’m trying to write a VB application that lets users export specific data, on the fly, from our HR database to a spreadsheet. I thought TransferSpreadsheet would do the job, but it only works with static tables and queries, and I’m building my recordset on the fly. There must be some way to do this.
THE FIX: There is—but note that this fix is only for those who are comfortable writing VB code. The trick is to automate Excel (not Access) and use its CopyFromRecordset method to assemble the data for your export on the fly. As you can see from the following code, once you’ve defined your worksheet range, a single call to CopyFromRecordset sucks your recordset into Excel:
Sub exportRecordset()
Dim rst As ADODB.Recordset
… 'Create recordset
Dim objApp As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim objRange As Excel.Range
Set objApp = New Excel.Application
Set objWorkbook = objApp.Workbooks.Open("c:\fullPath\myExport.xls")
Set objWorksheet = objWorkbook.Worksheets("ExportSheet")
objWorksheet.Range("namedRangeForExport").Clear
objWorksheet.Range("namedRangeForExport").CopyFromRecordset rst
objWorkbook.Close True
objApp.Quit
Set objApp = Nothing
rst.Close
End Sub
Note that although we used an ADO recordset, CopyFromRecordset accepts DAO recordsets as well. We exported the data to a named range in the worksheet, but you could have used a literal range. (The range only needs to be cleared if it has old data in it.) By default, CopyFromRecordset copies the entire recordset. If you supply it with optional row and column arguments, it will take that number of rows and columns.
Get Fixing Access Annoyances now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
