Troubleshooting Microsoft Access Databases

Read an Excerpt

Datasheets—Entering & editing data

• I can’t enter or edit field data in a table
• Source of the problem
• How to fix it
• Seeing all the columns
• I get error messages when I try to paste records into a datasheet
• Source of the problem
• How to fix it:
• A couple of other pasting problems
• I can’t enter dates the way I want in an input mask
• Source of the problem
• How to fix it
• Why is a medium date format better?
• I have only part of the field value and can’t find the record I want
• Source of the problem
• How to fix it

I can’t enter or edit field data in a table

Source of the problem

When all you’re trying to do is update the tables in your database with new or revised data, it can be a pain to have Access say you can’t do it. There are several fairly simple reasons why you might not be able to edit or enter data in your table.

• The fields are of an AutoNumber data type. You can’t edit these fields because Access maintains these values automatically.
• You opened the database as read-only. If you did, the New button is dimmed in the database window.
• The record or table is locked by another user.
• The field is a calculated field.

Another possible reason your value isn’t accepted is that you are entering a number that exceeds the field size setting for a number field. Access might round off the number you enter to the largest value allowed. You might also be trying to enter too many characters in a text field. The default field size for a text field is 50 characters, but the field you’re working with might allow fewer than 50. The following solutions show how to handle each of these problems.

How to fix it

1. If the database was opened read-only, close it and reopen it normally.
2. If you see the universal don’t do it symbol (a circle with a diagonal slash), that’s an indication that the record or table is locked by another user. Wait a while until the record or table is available again.
3. If you want to change the value in a calculated field, you need to change the value of one of the fields used in the calculation. You can’t change the value in the field where the calculation is performed.

If Access changed a number value you entered or if Access displayed an error message about the value not being valid, follow these steps:

1. In a number field, the Field Size property might be set to Single. Open the table in design view, and select the field you’re having trouble with.
2. On the General tab of the Field Properties area, change the Field Size property to Double. Save the changes to the table design.

(Image Unavailable)

3. Switch the table to datasheet view, and enter the value again.
4. If changing the field size doesn’t fix your problem, you might have tried to enter text in a number field. With the table open in design view, check the field’s data type and change it to Text if necessary.

If your problem lies with the size of a text field, follow these steps:

1. Open the table in design view, and select the field you’re working with.
2. On the General tab of the Field Properties area, increase the number in the Field Size property to the maximum number of characters you expect to enter. The maximum for a text field is 255 characters.

(Image Unavailable)

Seeing all the columns

When you are entering or editing data in a table too large to view on one screen, keeping track of which record you are working on can be difficult. Keeping one or more of the important columns on the screen while you edit can help orient you.

To do this, use the freeze column feature. Select the column or columns you want to remain visible, and then click Freeze Columns on the Format menu. As you scroll to other columns, the frozen columns remain visible at the left side of the table. When you are through editing the data, you can unfreeze the columns by clicking Unfreeze All Columns on the Format menu. Unfortunately, Access doesn’t put the thawed columns back where they belong, so you will have to drag them to their previous positions.

I get error messages when I try to paste records into a datasheet

Source of the problem

Pasting records into a table sounds so easy. Just like fourth grade. Unfortunately, Access is a little pickier than your fourth grade teacher was. If Access can’t paste any or all of the data you want to move or copy, you will see a message describing the problem with a clue about how to fix it, just like the messages on the homework you got back from your teacher. After explaining the problem, Access displays another message, indicating that it has saved all the records that it couldn’t paste in the Paste Errors table. Reviewing the Paste Errors table is a convenient way to troubleshoot the problem you have.

Access might not be able to paste data for several reasons, including the following:

• You tried to paste text with more characters than the field you’re pasting it in allows.
• The value you are trying to paste isn’t compatible with the type of data the destination field can accept.
• The source field contains a value that doesn’t fit with certain property settings in the destination field, such as a validation rule, an input mask, or a setting in the Required or AllowZeroLength property.
• You tried to paste records from more fields than the destination table contains.

The following solution shows how to deal with these sticky pasting problems.

How to fix it:

1. In the message box that explains the problem, click OK.

(Image Unavailable)

2. Click OK in the message about the Paste Errors table, and then open the Paste Errors table in datasheet view. You might need to correct the errors that caused the paste failure one by one.

(Image Unavailable)

3. Open both the source and the destination table in design view.
4. On the General tabs of the Field Properties areas of the source and destination tables, compare the Field Size property settings of the problem fields. One of the fields might be too small to contain the data you are pasting. For example, text from a field that allows 255 characters won’t necessarily fit into a field that allows only 40. Adjust the Field Size property setting in the appropriate table.

(Image Unavailable)

5. Make sure the data types are compatible. For example, you can’t paste text into a field with a Number data type.
6. Check the property settings of the destination fields for validation rules, input masks, and other restrictions. The data you are pasting might not follow the rules set up for the field you are pasting the data into.
7. If the problem is that you are pasting records from more fields than the destination table can handle, select fewer fields to paste.
8. After you’ve modified settings and field types, save any changes you’ve made to the tables’ design.
9. Select the records in the Paste Errors table and click the Copy button on the toolbar.

(Image Unavailable)

10. In the destination table, select the blank new record row and click Paste.
11. Delete the Paste Errors table from the database.

A couple of other pasting problems

There are a few other problems you might encounter when pasting records from one table to another in Access. One possibility is that the field you are trying to paste into might be in a hidden column. If so, go to the destination table in datasheet view and click Unhide Columns on the Format menu.

You might also be pasting a column name instead of data into a cell. This happens when you select data in a datasheet by clicking the left margin of the cell. You actually select the column name along with the field value. Then, when you paste the value, you are pasting only the first section of the copied text, which is the column name. Delete the column name from the destination table, and select only the data in the cell, not the complete cell. Then copy and paste it into the receiving record.

Another problem could be that you did not select a destination for the field or fields you want to paste. Or, you might have tried to cut data from or paste data into a field that is locked, unavailable, or calculated, or an AutoNumber field.

The reason for a pasting problem could be as simple as lack of permission to add or edit data in the destination database. If that’s the case, reset your database permissions and try pasting the records again. You can set database permissions by clicking Security on the Tools menu. One other pasting problem might be that the primary key or unique index field of the record you are pasting is a duplicate of one already in the destination record. If that’s the case, you’ll need to edit the primary key value before you can successfully paste the data.

I can’t enter dates the way I want in an input mask

Source of the problem

As much as you’d like to think that you can make a process as simple as entering data foolproof, we all know that errors can cunningly sneak into our data through the back door. One of the really helpful features of Access, the input mask, which tries to control what and how much data you can enter in a field, can still generate a few problems of its own.

The input mask displays fill-in spaces, often punctuated with special characters such as slashes (/), commas, and periods. An input mask often limits the number of characters you enter in a field, which can cause problems when you have a longer or shorter value to enter.

Input masks are very particular. If you often hear a beep when entering data in a field, an input mask may not match the way you are used to entering data. For example, if you enter a date as 11502 (meaning January 15, 2002), an input mask that requires six characters could display the value 11/50/2 instead of 01/15/02 because you didn’t enter the first 0. And, neither the month or day can have a value of 50, so this results in an error.

You might also have trouble entering a date in a field with an input mask if the mask conflicts with the display format. For example, dates might be displayed in the format 6-June-2002, while the input mask shows __/__/__. This results in you having to enter a value that doesn’t look like the values already in the field. You will hear a beep if you don’t fill in all the blanks or if you try to enter the name of the month that appears in the displayed value.

Here are some solutions for these problems with input masks.

How to fix it

If you see an error message that a date value isn’t appropriate for the input mask, do the following:

(Image Unavailable)

1. Select the entire entry.
2. Press the Delete key.
3. Enter the date value following the input mask, including the leading zeros.

If the input mask conflicts with the format in which a date field is displayed, take these steps to correct the problem:

1. Open the table in design view, and select the field that is giving you trouble.
2. In the Field Properties area, click in the Input Mask property box and then click the Build button (…).
3. In the Input Mask Wizard dialog box, select the Medium Date mask that matches the setting in the Format property box in the table design.

(Image Unavailable)

4. Click Finish, and save the table design.

Why is a medium date format better?

To correct the problem with seeing dates in a format that’s different from how you’re asked to enter them, you could also change the field’s Format property to Short Date. However, changing the format to Medium Date is a better solution because it eliminates any ambiguities that can result from the Short Date format. The Medium Date format shows the abbreviated month name instead of all numbers. With the Short Date format, however, if you enter 08/13/02, Access interprets it as 13-Aug-2002, but if you enter 13/08/02, the date displayed is 02-Aug-2013. If you enter a date in which both of the first two entries are less than 12, Access treats the first entry as the month and the second as the day: 10/08/02 is interpreted as 08-Oct-2002, while 08/10/02 is interpreted as 10-Aug-2002.

I have only part of the field value and can’t find the record I want

Source of the problem

It’s pretty easy to find everyone listed in your database with the last name of Johnson. When you try to find all the records that mention cats in a memo field, however, it can get a bit tricky. Or maybe you want to locate all the businesses on 5th or 6th Avenue so that you can bombard them with ads. If you know part of a value and that part is at the start of a field, you can simply sort on that field and track down the information you need. But difficulties arise if the part of the value you know is embedded in the field. In these cases, you have to resort to the old poker scam of wildcards, which are symbols that take the place of one or more letters or numbers.

You might have already used these special characters in place of real ones and had a problem with the values that turned up. Wildcards are usually used to search text and memo fields, but, if you are careful, you can massage them to search in date and number fields as well.

If you’re not finding the right records, you might be using the wrong wildcard. If you are using more than one symbol, they might be placed in the wrong order. Another problem might be options set in the Find and Replace dialog box.

If you have tried to find data that includes one of the wildcard characters, you might not have found the records you expected. Looking for a wildcard character takes special preparation because Access thinks you are using it as a wildcard instead of it being the character you want to find. You’ll have no problem looking for values that include exclamation points (!) or closing brackets (]), but other symbols need special treatment.

Here are some ways to use wildcards to your advantage.

How to fix it

If you want to find records with a certain value in a field, for example, any order for any product with "tofu" in its name, do the following:

1. Open the table in datasheet view, and place the insertion point in the column for the field you want to search.
2. On the Edit menu, click Find.
3. In the Find What box, type *tofu*.
4. In the Match box, select Any Part Of Field.
5. Click Find Next.

(Image Unavailable)

6. To find additional records with the same partial value, click Find Next again.

If you are looking for addresses on specific streets (in this example, 5th or 6th Avenue), you can mix wildcards with text as follows:

1. Open the table in datasheet view, and place the insertion point in the column containing the addresses.
2. On the Edit menu, click Find.
3. In the Find What box, enter *[56]th Ave*.

(Image Unavailable)

4. In the Match box, select Any Part Of Field.
5. Click Find Next.

Read More