How to Troubleshoot 10 Common Access 2019 Problems

Here are ten of the most common Microsoft Access problems and their solutions. Don’t give up hope if your problem is not on this list. 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.

That’s just not normal—normalizing empty Access tables

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

If you’re having trouble normalizing empty tables, fill each table with five to ten records. Viewing the tables with data usually makes it easier to spot normalizing issues.

Here’s how to get started normalizing your tables:

1. Examine each table as it is currently structured. Are you repeating any information unnecessarily (as you saw with the address and customer name earlier)?
2. If you have duplicate information, determine why you’re repeating it (for example, the multiple phone numbers for one customer).
3. Break the one table into two tables to eliminate the redundancy.
4. Repeat Steps 1 through 3 for each table until all redundancy is eliminated.

You may find that splitting one table into two still does not eliminate all redundancy in a table. In that case, keep splitting the tables until all redundancy is gone.

You type 73.725, but Access changes it to 74

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:

1. Open the table in Design view and then click the field that’s not cooperating.
2. On the General tab of the Properties area at the bottom of the screen, click the Field Size box.
3. Click the down arrow at the end of the box, and then select Single, Double, or Decimal from the drop-down menu that appears.
4. Save the table, and your automatic rounding problem is over.

For details about the difference between Single, Double, and Decimal field sizes, press the F1 key while in the Field Size property box. The Help screen gives a detailed description of each field size, the numbers it will hold, and the amount of space reserved for that size. Access requires an Internet connection to use its help system.

The words they are a-changing—problems with Access AutoCorrect

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.

• Undo AutoCorrect’s effects as they occur. Press Ctrl+Z right after AutoCorrect has botched your data entry. Access puts the data back to the way you typed it. Unfortunately, for this to work you actually have to notice that Access has changed what you entered.
• Turn AutoCorrect off entirely. To turn off AutoCorrect, follow these steps:

1. Click the File tab in the upper left corner of the Access screen.

2. Click the Options button in the menu down the left side of the screen.

The Access Options dialog box appears.

3. Click Proofing from the list on the left.

Your proofing choices appear.

4. Click the AutoCorrect Options button.

The AutoCorrect dialog box appears.

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

6. Click OK two times to save your changes.

You can now type your problem text correctly, without AutoCorrect’s interference, and have it stay as you typed it.

Was there and now it’s gone—accidental data deletions in Access

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/19, and someone inadvertently changes the date to 12/15/09. 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.

Undo

Don’t panic. Before doing anything else, press Ctrl+Z. That’s the Undo command. If the record comes back, you’re in luck. Undo reverses data-entry errors that may cause the record to appear deleted. However, this will work only if you Undo right after the data-entry error takes place.

Search for the missing record

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 normally search for orders by date, search by client. See whether an order similar to the missing one exists for that client and has an unusual date (say, the same month and day as the missing order but with the wrong year).
• Try looking at all orders on the date in question to see whether the client on each order seems to be correct. It could be that the client was changed inadvertently on the missing order.

Backup recovery

If you can’t find the record anywhere, copy the record from a backup of the database file.

This solution works only if you’ve backed up your database since the record was originally added. If you back up at night and the record was entered during the same day it went missing, that record will not be in your backup.

You run an Access query, but the results are unexpected

Query-writing is an art form. Even the experts mess up every now and then. Here are some common solutions to unexpected query results:

• Check criteria for accuracy. A single misplaced keystroke is all it takes to turn your query into a dud. Check your criteria for spelling or syntax errors — and then run the query again.
• Try the Unique Values property. Ever see two copies of each record in your query results when you were expecting just one? A quick fix often comes from using the Unique Values property. This property tells Access to stop with the doubling, already — and, if the query results contain a group of exact duplicates, to return only one row from the group. Here’s how to use this property:

1. Open the problem query in Design view.

The Design tab on the Ribbon appears.

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

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

4. Click in the Unique Values row of the Property Sheet.

A drop-down list arrow appears at the end of the Unique Values row.

5. Select Yes from the drop-down list and run the query.

The doubling should disappear.

• Correct the selection logic. Juggling a bunch of AND and OR connections in a query can quickly mess up even the hardiest of database designers.
• Fix table relationships. If your query results show way too many records, and the query uses two or more tables, improper relationships (also called joins) are the likely cause.
• Check table relationship types. If your query involves two or more tables, and you get fewer records than you expected, incorrect table relationships are the likely cause. For example, if you have an order entry database and run a query listing all customers and their orders, by default, you'll see only those customers who have placed an order. To see all customers, whether or not they’ve placed orders, do the following:

1. In Design view, right-click the join (the line connecting the two tables) and choose Join Properties from the menu that appears.

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

3. 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 relationships, 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.

If your query still doesn’t work no matter what you do, ask someone else to take a look. I’ve often worked on a tough query problem for hours, shown it to someone else, and heard those magical words: “That’s simple. Just do this.” And the problem is solved. Getting a fresh pair of eyes on the problem often solves things fast.

The dreaded Parameter dialog box in Access

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

The slowest Access database in town

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). You’ll also likely run into errors if multiple people attempt to use the database at the same time. 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.

All you’re really sharing is the data — so the data is all that should go on the shared drive. By setting things up this way, the only information that must travel across the network is the data requested by the user. Such a setup dramatically speeds database performance and allows multiple users to enter and edit data at the same time.

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:

1. Back up the database you want 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.
2. If necessary, move the database you want to split to a folder on your shared drive. This step allows the Database Splitter to set up table links properly for you.
3. Open the database file you want 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.
4. Click the Database Tools tab on the Ribbon. The Move Data group appears on the Ribbon. It contains a button called Access Database.
5. Click the Access Database button. The Database Splitter Wizard dialog box appears.
6. 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.
7. 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!

Don’t have a shared drive or want to get rid of your share? You can still have multiple users in your database at one time by placing your data in the cloud.

Your Access database file is as big as a house

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:

• A smaller database file runs faster. Performance is a key component to happy database users. You want your forms to load quickly and your queries and reports to run as fast as possible.
• A regularly compacted database is more stable. If the database is used often, compacting regularly helps keep file and table corruption from occurring.

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:

1. Open the bloated database and click the Database Tools tab on the Ribbon. The Tools group appears at the very left of the Ribbon.
2. 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.

Want a database file to compact each time you close it? Follow these steps:

1. Click the File tab on the Ribbon.
2. Click the Access Options button in the menu bar down the left side of the screen. The Access Options dialog box appears.
3. Click Current Database from the list on the left. Options for the current database appear.
4. Check the Compact on Close check box.
5. Click OK to save your changes.
6. Click OK from the resulting message box.
7. Close the database and note the lower right status bar. The database is compacting before it closes!

Compact on close is used best on the front-end file only. Compacting the back end on close may cause corruption of the back-end file should another user be in it when you close out of it. Do not turn on Compact on Close on the back-end file.

You get a mess when importing your spreadsheet ito Access

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:

• Double-check information coming from any spreadsheet program to be sure that it’s consistent and complete. Above all, make sure that all entries in each column (field) are the same data type (all numbers, all text, or all whatever).
• Remove any titles and blank rows from the top of the spreadsheet. An ideal spreadsheet for import will have field names (column headings) in row 1 and data starting in row 2.
• Make sure your spreadsheet column headings are short and unique so Access can easily translate them to field names during import.

We’re sorry; your Access database file is corrupt

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:

1. Browse to the folder that contains the back-end file.
2. 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.
3. Close the back-end data file.
4. Reopen the front-end file and everything should be working normally.

If, after following the preceding directions, the corrupted file still doesn’t open, you have a serious problem that could take some effort to clean up. The next step is to resort to a backup copy of the database. Check what data is missing between the backup and your recollection of the corrupted file. Yes, you’ll have to reenter any missing data. Sorry!

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 2019 and it is from a legitimate company.