How to Troubleshoot 10 Common Access 2019 Problems

That’s just not normal—normalizing empty Access tables

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.

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

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

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

• 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 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

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

• 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

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

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.

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

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.

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

• 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

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!