Chapter 24
In This Chapter
Normalizing tables
Resolving data-entry problems
Consoling query woes
Sending the Parameter dialog box back where it belongs
Tuning up performance troubles
Importing spreadsheet data with less mess
Dealing with corruption
Yes, even so-called “experts” have problems with computers. I’ve chosen ten of the most common Microsoft Access problems and presented them to you in this chapter — with their solutions, of course!
Don’t give up hope if your problem is not on this list. I find many solutions to my computing problems by searching online. Chances are, if you’re having the problem, someone else has already had it and figured out the solution. That’s why the Internet is a valuable resource. The Appendix at the back of this book lists a few more valuable help resources.
Speaking of valuable resources, check out the ten problems in this chapter.
One of the hardest things to do (yet most important when building a database) is building the table structures properly. This process is known as normalization. A properly normalized database should never have just one table containing redundant information. Consider the following table:
Customer |
Address |
City |
State |
Zip |
Telephone |
Jones |
125 Main Street |
Jonestown |
NJ |
08000 |
609-555-1244 |
Jones |
125 Main Street |
Jonestown |
NJ |
08000 |
609-555-7890 |
Smith |
1542 Jones Hwy |
Laramie |
WY |
82051 |
307-555-5412 |
Wilson |
78 Smith Circle |
Jones |
CA |
90000 |
451-555-8645 |
Do you see the redundant information? Right you are. It’s the customer name and address. What is causing the redundancy? Right again! It’s the two phone numbers for customer Jones. The correct way to normalize this table would be to split it into two tables — one for customer name and address information and the second for phone numbers. This would eliminate the need to repeat the second Jones record in the Customers table. For more on normalization, see Chapter 4.
Here’s how to get started normalizing your tables:
Examine each table as it is currently structured.
Are you repeating any information unnecessarily (as you saw with the address and customer name earlier)?
Automatic rounding can frustrate the living daylights out of you, but correcting it is easy. By default, Access sets all number fields to accept long integers. As you may remember from your high school math days, an integer is a negative or positive whole number. To accommodate decimals, you change the field-size setting so it accepts decimals. Here’s how:
Sometimes those “helpful” features in Access can become a nuisance. One such feature is called AutoCorrect. You may be familiar with it from Microsoft Word, where it is often a great thing. Databases, however, often contain acronyms, part numbers, and the like. AutoCorrect can have a field day with such “words.” You may not even realize it as you enter your data.
You have two choices to resolve this problem.
Click the Options button in the menu down the left side of the screen.
The Access Options dialog box appears.
Click Proofing from the list on the left.
Your proofing choices appear.
Click the AutoCorrect Options button.
The AutoCorrect dialog box appears.
Uncheck some or all of the check boxes in the AutoCorrect dialog box.
You can disable some or all of the AutoCorrect features, depending on what AutoCorrect is doing to annoy you at present. Uncheck the Replace Text as You Type option if you no longer want Access to “fix” your “spelling errors” for you.
You might’ve heard this one a lot over the years: “The database deleted my record!” Well, I’ve got news for you: The database doesn’t do anything without us humans commanding it. And humans can make a couple of mistakes:
Accidental deletion: There are several ways to delete a record accidentally. Usually a keyboard shortcut for Delete is pressed, such as Ctrl+ – (delete record) or Ctrl+X (cut).
The Undo command (Ctrl+Z) will not reverse the deletion of a record.
Data error: A record may appear deleted if someone inadvertently changes a particularly vital piece of information. For example, suppose the record in question contains an order date of 12/15/16, and someone inadvertently changes the date to 12/15/06. The order date isn’t what’s expected, so the record may seem to have been deleted.
If a data error makes the record seem deleted, there are several possible fixes, as outlined in the following sections.
If you try the Undo command and the record doesn’t come back, there’s still a chance that a data-entry error is hiding it by putting it where you don’t expect it to be. Open the table that contained the record and search for it in some way other than you normally would. Look for anything out of the ordinary on similar records. Here are some examples:
If you can’t find the record anywhere, copy the record from a backup of the database file.
Query-writing is an art form. Even the experts spill their paint every now and then. Here are some common solutions to unexpected query results:
Open the problem query in Design view.
The Design tab on the Ribbon appears.
Click the Property Sheet button from the tab’s Show/Hide Ribbon group.
The Property Sheet window opens to the right of the query grid.
Click in the gray area between the field lists in the top half of the query grid.
The Property Sheet should now display Query Properties. (Look right under the Property Sheet’s title bar to confirm this.)
Click in the Unique Values row of the Property Sheet.
A drop-down list arrow appears at the end of the Unique Values row.
Select Yes from the drop-down list and run the query.
The doubling should disappear.
AND
and OR
connections in a query can quickly mess up even the hardiest of database designers. Chapter 14 has tips on untangling the mess.Examine the types of joins offered and choose the one that says something like “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal.”
The actual text you see differs according to the names of your tables. To query aficionados, this is called an outer join. Very cool.
Click OK and run the query.
You should now have all records from the Customers table whether or not there are corresponding records in the Orders table.
If your query involves several criteria, some calculated fields, and numerous joins, try breaking the task into several smaller steps instead of trying to solve the problem all at once. The step-by-step approach lets you focus on each piece, one at a time, making sure each works perfectly before moving on to the next one.
At some point, when opening a query, form, or report, you’ll see a Parameter dialog box when you don’t want to see a Parameter dialog box. Do you throw your hands in the air and curse the universe? Of course not!
Whenever you see a Parameter dialog box unexpectedly (you can set them on purpose — see Chapter 16 for details), it means that Access can’t find a field referenced by either the form or report or the query behind the form or report. Say that the problem is with a report. To troubleshoot, start with the query behind the report. Open that query in Datasheet view and see if you get the parameter. If you do, what field is it asking for? That field is the one Access can’t find. So, switch the query to Design view and find the column with the field that Access can’t find. The problem field is usually a calculated field that references other fields. Is each field and table name spelled correctly? If not, correct the spelling errors. Is each field in the table it’s supposed to be in? For example, if your reference reads Orders.LastName
and the LastName
field is in the Customers table, correct the error by typing Customers.LastName.
If the query runs without a parameter, then the problem is on the report. So, open the report in Design view and check each control on the report that is bound to a field. If Access can’t find one of the fields the control is supposed to display, it’ll put a green triangle in the upper-left corner of the control. Check each one for the green triangle. If you find the green triangle, check the spelling of the field referenced by the control. For example, if the control is supposed to display LastName
(no space) and the reference in the control says Last Name (space), then remove the space so that the control on the report matches the field name from the query. Also check the report’s underlying query to confirm the problem field is selected in the query.
An Access database may end up on the shared drive of a business so it’s available to everyone who needs it. The problem with placing the entire Access database on the shared drive is that it often runs slowly on each user’s workstation (that’s a fancy word for an individual computer). The complaints start rolling in, and you don’t know what to do.
The solution to this problem lies in splitting the Access database file into two separate files:
Front end: Contains all the database objects except the tables.
The front end resides on the user workstation.
Back end: Contains just the tables.
The back end resides on the shared server.
The front end is linked to tables in the back end. (See Chapter 9 for more on table linking.)
Splitting the dataset is not as hard as you might think. Access makes it a snap with the Database Splitter Wizard. Follow these steps to split your database:
Back up the database you’d like to split.
If anything goes wrong (unlikely, but hey, you can never be too safe when it comes to data!), you can try again with the backup copy.
If necessary, move the database you’d like to split to a folder on your shared drive.
This step allows the Database Splitter to set up table links properly for you.
Open the database file you’d like to split from the shared folder.
Make sure you have a backup copy of this database before going any further. Also make sure all database objects are closed.
Click the Database Tools tab on the Ribbon.
The Move Data group appears on the Ribbon. It contains a button called Access Database.
Click the Access Database button.
The Database Splitter Wizard dialog box appears.
Click the Split Database button and let the wizard do its thing.
You will be prompted for a back-end database filename. Enter a name, sit back, and watch the fun unfold before your very eyes.
Copy the front-end file (the original file you split) to each user’s workstation.
Have the users open the file from their workstations — and see how they marvel at the improved speed of the database! You are a hero. Yea!!
As time goes by, you find your database file growing larger and larger. This is a result of deleting objects and records over time. If, for example, you create a query and then later delete it because it’s no longer needed, Access doesn’t automatically remove the space occupied by that query from the database file. The same is true for records. As you delete records from a table, the space that those records occupied in the database file remains. Eventually, the file can become four or five times the size required to hold the data and objects within it.
Why should you care if the file size increases? Here are two reasons:
The Compact and Repair command removes the excess. It is good practice to compact your database regularly (once a week is usually fine). Always compact it after making any design changes. Here’s how:
Open the bloated database and click the Database Tools tab on the Ribbon.
The Tools group appears at the very left of the Ribbon.
Click the Compact and Repair Database button from the Tools group.
The status bar (lower right of your screen) displays a progress bar that notifies you of how the compact process is progressing. When the progress bar disappears, compacting is complete — and you’ll be left with a much trimmer (faster and more stable) database file. If you’ve split your database, don’t forget to compact both the front- and back-end files.
Click the Access Options button in the menu bar down the left side of the screen.
The Access Options dialog box appears.
Click Current Database from the list on the left.
Options for the current database appear.
Close the database and note the lower-right status bar.
The database is compacting before it closes!
It’s common practice to upgrade a collection of spreadsheets to an Access database after the spreadsheet solution no longer suits your needs. It’s also common to find the imported spreadsheet (now table) data in a state of disarray. The easiest way to solve this problem is by cleaning up the spreadsheet before you import it. Here are a few tips for a tidy import:
It started out as a day just like any other. However, on this day, you are getting an error when you open the front end of your split Access database. You can’t seem to open any forms or reports. It’s funny how a few little messages can ruin your day. You start wondering if you backed up the data file last night and when the file was actually corrupted. Then you start wondering how you’ll get out of this mess.
Fear not. There is a simple solution to a corrupt database. Here are the steps:
Double-click the file to open it.
Access will launch and attempt to repair the file. You should see a repair progress bar on the right part of the status bar. If all goes well, the file opens.
If you don’t have a backup, all hope is not lost. You can buy software designed specifically to repair corrupted Access database files. Try searching the web for repair corrupt Microsoft Access database files. Make sure the software works with Microsoft Access 2016.