How to Fix Access Table not Showing All Records?

MS Access users often face problems or issues while working with the application or database file. One common issue, reported by several users, is Access table not showing all records. The users encountered this issue when trying to import the records into an Access database table or loading the records from table into a form or report. This issue may arise because of several reasons. In this article, we will look into the reasons for the Access table not showing all records issue and see how to fix this issue.

Reasons behind Access Table not Showing All Records Issue

Listed below are some reasons why you may face the Access table is not showing all the records issue:

• Issue with the records in the table
• Incorrect form properties
• Issues with table structure
• Using a multi-table query as the Record Source for a form
• Using special characters in object names
• Corruption in the table or database file
• Some records are hidden
• Missing or deleted records in the table
• Insufficient permissions to access the table records
• Corrupted table indexes
• Primary key is missing
• Field size limitation
• Linked tables might not be refreshing automatically (in case of split database)
• Incorrect JOIN operation used in SQL VBA code to fetch data from tables with defined relationships

Methods to Resolve Access Table not Showing All Records Issue

If the primary key is duplicate or missing, you may see partial data in tables. So, make sure that primary key field is not empty and there is unique primary key for each table. However, if you are still not able to see the complete Access table records, follow the methods explained below.

Method 1: Remove Filter via Object Property

When you apply a filter to a newly created form or report and then save it without removing the filter, the filter cannot be turned off. So, the form or report will display the filtered view of the underlying table or query and does not display all the data. You need to remove the filter, before saving a newly created form or report, from the object’s design to view all the records in the underlying table or query. For this, you can go to Navigation Pane > Design View.

In case you have saved the object (i.e., a form or report) while it is still in filtered state, you need to edit the object’s Record Source property to remove the filter. To do so, perform these steps:

• Open your Access database file.
• Right-click the form or report from the Navigation Pane and then click on Design View to open the object (i.e., the form or report) in design view.

• Click on Property Sheet under the Design tab.

• When the Property Sheet opens, click the three dots next to the Record Source property.

• The object’s record set query is displayed in the Query Builder window.

• Search for the filtered fields and filter criteria in the query design grid.
• In the query design grid, click the gray bar above the Field row to select the column containing the filtered field.
• Click on the Delete option to remove the filtered field, along with its criterion.
• Repeat the above steps to remove each filtered field from the object's design.
• Exit the query designer. Click Yes when prompted to save the changes and update the property.

Method 2: Change the Field Size

Each field in an Access table is allocated a specific size and character limit. If this limit is exceeded, the data in the fields appear to be incomplete.

You can try resizing the rows and columns accordingly to see the complete data.

Method 3: Check for Hidden Objects and Records

If your Access table is not showing all records, then it might happen that some records or objects are hidden. You can check the Display Options and unhide the records and objects. Here is what to do:

• Open the Access database file.
• Right-click on the navigation pane and select Navigation Options from the context menu.

• The Navigation Options wizard will open. Select all the given options under the Display Options section, including Show Hidden Objects, Show System Objects, and Show Search Bar. Click OK.

This will show you all the hidden objects and records in the table.

Method 4: Repair the Access Database File

The Access table is not showing all records issue can also be the result of corruption in the database, either at object level or data level. You can use the MS Access' built-in Compact and Repair tool to repair the corrupt database file. To do this,

• Launch the MS Access application.
• Click on Open and browse for the problematic database file.

• Once you have opened the problematic file, click on File > Info > Compact & Repair Database.

The Compact and Repair tool will scan the problematic database file for issues and fix them.

If the tool fails to fix the issue, then you can use Stellar Repair for Access - a powerful Access database repair tool that can repair severely corrupted/damaged Access database (ACCDB or MDB) files. The tool can retrieve all the database objects, such as tables, forms, linked tables, indexes, macros, etc., and save them in a new database file. It can also recover deleted records from the database file. The tool supports database files created using MS Access 2019, 2016, 2013, 2010, 2007, 2003, and 2002.  

Conclusion

In this article, we’ve discussed the ‘Access table not showing all records’ issue and how you can fix this issue. However, if the Access database is corrupted, you can use the Compact and Repair utility to resolve minor corruption issues. If this utility fails to bring the desired outcome, you can rely on a specialized Access database repair tool, such as Stellar Repair for Access. It can help you repair a damaged or corrupted Access database file in a few simple steps. Moreover, it can recover all the database objects, such as tables, forms, queries, and more, from even severely corrupted ACCDB/MDB files without any hassle.

Read More

What to do if compact and repair is not working in MS Access?

Compact and Repair Access Database without Opening