Troubleshooting Microsoft Access Databases

Overview


Trouble constructing a Microsoft Access database? Confounded by error messages?

With this handy ""Troubleshooting"" guide, it's easy to pinpoint -- and solve -- your own software problems. Fast! Each section opens with a troubleshooting chart to help quickly diagnose the source of the problem. It offers clear, step-by-step solutions to try right away, plus a full chapter of things to do to stay out of trouble or learn a new trick. Continuous support via the Troubleshooting ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (23) from $1.99   
  • New (4) from $6.50   
  • Used (19) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$6.50
Seller since 2005

Feedback rating:

(369)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
Redmond, WA 2000 Softcover New Condition New, all pages clean, crisp, and tight, never used. Multiple copies available this title. Quantity Available: 3. Category: Computers & ... Internet; ISBN: 0735611602. ISBN/EAN: 9780735611603. Inventory No: ABE516896132. Read more Show Less

Ships from: Burgin, KY

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$12.00
Seller since 2007

Feedback rating:

(324)

Condition: New
2001 Trade paperback New. No dust jacket as issued. (s11-1) Trade Paperback is brand new in Near Mint cond. Trade paperback (US). Glued binding. 352 p. Eu-Undefined. Audience: ... General/trade. Read more Show Less

Ships from: Oxford, MA

Usually ships in 1-2 business days

  • Canadian
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$15.99
Seller since 2008

Feedback rating:

(169)

Condition: New
0735611602 BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$50.00
Seller since 2014

Feedback rating:

(164)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview


Trouble constructing a Microsoft Access database? Confounded by error messages?

With this handy ""Troubleshooting"" guide, it's easy to pinpoint -- and solve -- your own software problems. Fast! Each section opens with a troubleshooting chart to help quickly diagnose the source of the problem. It offers clear, step-by-step solutions to try right away, plus a full chapter of things to do to stay out of trouble or learn a new trick. Continuous support via the Troubleshooting ""Latest Solutions"" Web site provides monthly updates on additional problem solving information. Books in the ""Troubleshooting"" series are colorful, superbly organized, and easy to read, giving even novice users the confidence to fix it themselves -- without calling tech support or wasting time on futile trial and error. Covering both Access 97 and Access 2000, this book shows how to troubleshoot access tables, forms, reports, and data access pages; share data; and more.

Read More Show Less

Editorial Reviews

Booknews
A handbook for beginning to intermediate users to diagnose and solve common problems with an Access database or its tables, forms, reports, or other elements. Each chapter starts with a flow chart and then goes on to provide specific solutions. Includes a quick reference to the top 20 problems with Access (such as incorrect data, trouble with printing, etc.). Anderson is a former computer systems analyst and programmer in the defense industry. Annotation c. Book News, Inc., Portland, OR (booknews.com)
Read More Show Less

Product Details

  • ISBN-13: 9780735611603
  • Publisher: Microsoft Press
  • Publication date: 1/6/2001
  • Series: Eu-Undefined Series
  • Pages: 352
  • Product dimensions: 7.46 (w) x 9.30 (h) x 0.70 (d)

Table of Contents

About this book xi
Troubleshooting tips xiii
Charts and graphs 2
I made changes to a chart in Microsoft Graph, but they were not retained 4
The legend labels on my chart don't say what I want 6
The columns in my chart aren't in the order I want 8
My chart appears dimmed or blank 10
Controls--Managing data 12
The order of the rows in my list box or combo box isn't right 14
I see #Error or #Name? in a field instead of the value 16
I don't get the right results with conditional formatting 18
I need to deal with Null values in a form or report 20
The text box control labels are in the way 22
The items in my list box or combo box take too long to appear 23
Controls--Placing and formatting 24
My option group doesn't work the way it should 26
I'm having trouble creating, organizing, and sizing a tab control 28
I can't make an image fit on a command button 30
I get an error message when I click a hyperlink 32
Conversion 34
I'm having trouble converting an Access 2.0 database 36
My code won't compile 38
I see a message that an object library is missing 40
I see an error message about being out of memory or having too many indexes 42
Data--Setting field properties 44
Some data is incorrect 46
The values in a field don't have the same format 48
I don't know what information to enter in a field 50
I'm having trouble converting a field to another data type 52
Datasheets--Entering and editing data 54
I can't enter or edit field data in table 56
I get error messages when I try to paste records into a datasheet 58
I can't enter dates the way I want in an input mask 62
I have only part of the field value and can't find the record I want 64
Datasheets--Viewing data 66
I can't find the records I want 68
I can't change the way my datasheet displays data 70
I can't get my subdatasheet to look the way I want 72
The size and behavior of my subdatasheet are wrong 76
Exporting 78
Numbers don't look right after I export them to a text file 80
I don't see the subform in my report when I publish it with Word 82
Exporting Access data to dBASE or Paradox causes problems 84
I get errors when I export a table to Excel 86
Excel doesn't show the totals in the report I exported 88
Expressions 90
The expression I used displays #Error 92
The text I combined with a field value isn't displayed 94
The mailing labels I created have blank lines 96
I'm getting the wrong results with date calculations 98
Filtering 100
My filter doesn't return all the records I want 102
My filter returns too many records 106
My filter returns the wrong records 108
The filter I created isn't saved with my table 110
The list of values in Filter By Form is wrong 112
Forms--Designing 114
My form is the wrong size and doesn't display complete records 116
I can't seem to synchronize two forms 118
I can't get my text boxes and labels to line up right 120
I'm having trouble aligning a background picture in my form 124
I can't get the tab to move in the sequence I want 126
Forms--Viewing data 128
I'm having trouble entering records in my combo box or list box 130
My subform doesn't print the way I want 132
My form is blank where there should be data 134
My subform doesn't show the data I expected 136
Importing and linking 138
I can't open or update a linked table 140
I'm having trouble with an imported table 144
I get errors when I import a spreadsheet or text file to my Access database 146
I get errors when I try to append a spreadsheet or text file to an Access table 148
Macros 150
I can't put a multiline message in a message box 152
I get a "Can't find the macro" error message 154
My SetValue macro runs at the wrong time 156
I can't copy with Ctrl+C anymore 158
Menus 160
I can't restore my built-in menu bars 162
I can't reset my built-in menus 164
Some of the built-in menus or commands aren't displayed 166
My custom menus don't show up when I open my form 168
My shortcut menus aren't right 170
Pictures and OLE objects 172
I can't open an OLE object 174
My calendar control doesn't look right and shows the wrong date 176
I see an icon where I put a picture 178
I can't edit my OLE object 180
The picture is distorted and doesn't fit the frame 182
Queries--Action 184
My action queries cause errors 186
I can't get my append query to work right 188
Using a Find Duplicates query to delete duplicate records didn't work 190
I'm not getting the results I need with a parameter query 192
Queries--Calculations 194
I keep getting blanks instead of real values 196
I get a strange result when I use the First function in a query 198
My query shows the wrong calculation results when I try to summarize data 200
My AutoLookup query isn't working 202
Queries--Crosstab 204
Too many columns in my crosstab query caused an error message 206
Saving a crosstab query as a report doesn't work 208
I'm having trouble grouping rows in my crosstab query 210
I'm having trouble grouping columns in my crosstab query 212
Queries--Selection criteria 214
I get the wrong records when I combine criteria 216
What's up with my wildcard characters? 218
I see too many records in the query results 220
My query doesn't return as many records as I expected 222
Queries--Simple select 224
I don't see all the records from one of the tables in my query 226
The records aren't in the order I wanted 228
The query doesn't display the number of columns I expected 230
My query takes too long to return the results 232
I can't sort on a lookup field in my query 234
Relationships 236
I get an error message about ambiguous outer joins 238
Access won't let me edit or delete a record 240
I can't build the relationship I want in a query 242
I can't enforce referential integrity on a relationship 244
Reports--Creating 246
Calculated fields are empty in report preview 248
I can't group records in a report the way I want 250
I can't keep duplicate data out of my report 252
My report snapshot isn't working right 254
Reports--Previewing and printing 256
When I print my report, every other page is blank 258
My report has too much white space 260
Some of my mailing labels are blank 262
My multiple-column report isn't printed right 264
Sorting 266
My records aren't in the order I want 268
I'm having trouble sorting on lookup and memo fields 270
I can't sort a text field in numeric order 272
I can't sort grouped records in a report the way I want 274
Table design 276
I get an error message after designating a primary key 278
I need to control errors in my data 280
Too many records have the same value in the index 282
I don't know which data types and properties to choose in a new table 284
Toolbars 286
I can't restore my built-in toolbars 288
I can't reset my built-in toolbar buttons 290
Some of the built-in buttons aren't displayed 292
I can't customize the toolbar I need 294
When I try to paste an image on a button, it doesn't look right 296
Index 299
Read More Show Less

First Chapter

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

Datasheet — Entering & editing data

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


TIP:
In the Open dialog box, you usually have four options when you click the Open button: Open, Open Read-Only, Open Exclusive, and Open Exclusive Read Only. (In Access 97, click the Commands And Settings button in the Open dialog box to see the options.)
  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.
  3. (Image Unavailable)

  4. Switch the table to datasheet view, and enter the value again.
  5. 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.
  3. (Image Unavailable)

Seeing all the columns


TIP:
If you need more room than 255 characters, you can change the Text data type to Memo, which has a much larger size limit.

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.
  2. (Image Unavailable)

  3. 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.
  4. (Image Unavailable)

  5. Open both the source and the destination table in design view.
  6. 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.
  7. (Image Unavailable)

  8. Make sure the data types are compatible. For example, you can’t paste text into a field with a Number data type.
  9. 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.
  10. If the problem is that you are pasting records from more fields than the destination table can handle, select fewer fields to paste.
  11. After you’ve modified settings and field types, save any changes you’ve made to the tables’ design.
  12. Select the records in the Paste Errors table and click the Copy button on the toolbar.
  13. (Image Unavailable)

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

WARNING:
Fix the errors and complete the pasting before you attempt another paste operation. The Paste Errors table holds records only temporarily. The records in the table are overwritten with the next paste failure.

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.
  4. (Image Unavailable)

  5. Click Finish, and save the table design.

TIP:
If you have a field that needs to conform to a specific pattern—for example, a product code or a catalog number—you might want a special input mask to help with data entry. You can ask the Input Mask Wizard for help in creating one and save it for future use.

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


TIP:
The asterisk (*) wildcard takes the place of any number of characters and must be used as the first or last character in the Find What box.

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.
  6. (Image Unavailable)

  7. 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*.
  4. (Image Unavailable)

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

TIP:
The pair of square brackets ([]) enclose alternative values or a range of values. For example, using [5-9] in the expression would find addresses on 5th, 6th, 7th, 8th,
or 9th Avenue.
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)