When using Access, many users found some records to be missing from their forms or reports while fetching data from a table. An issue with the records in the table, incorrect form properties, or improper VBA code/SQL query syntax can result in Access not showing all data records. This post will help you understand the reasons behind missing records in MS Access and discuss some methods to resolve it.
Why is Access not Showing All the Records?
If the Access is not showing all the records, it could be happening due to an enabled filter in the Design View of a form or a report. There are several other reasons behind this issue –
- Corrupted database
- Incorrect criteria set for fields
- Incorrect VBA code or SQL query used for joining tables
- Incorrect table name used in a VBA code
- Reserved words/symbols used as field/object names resulting in JET engine errors
- Wrong datatype used in respective fields
- Corrupted table index
Remove the filtered fields to Resolve the ‘Access table is not Showing all Records’ Issue
If the Access table is not showing all records, it could be due to the saved objects with enabled filters. When you create a new object like a form or report in MS Access, apply a filter to it and save it without removing the filter. Saving the object with an enabled filter makes it permanent for that object. This is why Access will show filtered data with missing records.
To fix the issue, you will need to edit the object’s Record Source property and remove the filters.
You can also prevent this from happening by removing the filter from a newly created object before saving it.
Here are the steps to remove filters from an existing object in a filtered state –
- Open your Access database.
- Right-click on the problematic table and click on Design View in the context menu.
- Select the Design tab and click on Property Sheet under it.
- Navigate to the Data tab in the Property Sheet and click the button with three dots.
- It will display a prompt asking you to create a query based on the table.. Click Yes to proceed.
- It will open the Query Builder.
- Find filtered fields and filter criteria in the displayed object’s recordset query.
- Go to the query design grid, click on the grey bar at the top of the Field row to select the column with the applied filter.
- Press the Delete key on your keyboard to remove the applied filter and all its parameters.
Note – If there are more filters applied to the recordset, repeat the steps above for each filtered field.
- Once you have removed all the filters, exit the Query Builder.
- Click Yes to save the changes.
The steps mentioned above will help you remove filters from an existing report or form.
Repair Corrupted Access Database
In some cases, database corruption results in missing data. However Access database corruption can also happen due to improper shutting down of application or system, hardware failures, etc., which could result in missing data. In some cases, a corrupted database index can also be behind the Access table is not showing all records issue.
Unusually large databases or shared databases can also get corrupted due to performance issues or users leaving records in an unknown state. In case of database corruption, you can try to use MS Access’s built-in Compact and Repair utility to repair the corrupted database.
Fix 1: Use Compact and Repair Utility
The Compact and Repair utility is a built-in tool that helps you fix Access database corruption by eliminating database inconsistencies and issues. This utility also resolves database index corruption and eliminates unused space from the database, thereby improving the overall performance of the database file and reducing its size.
Here is how you can use the Compact and Repair utility –
- Open the Access database file.
- Select Database Tools in the menu bar.
- Click on Compact and Repair Database.
After using the Compact and Repair utility, your corrupted database should work properly.
Fix 2: Use an Access Repair Software
If using the Compact and Repair utility didn’t help fix Access database corruption, you can use an advanced Access database repair software like Stellar Repair for Access. It is powerful software that allows you to repair corruption in the Access database. It can also repair severely corrupted database indexes.
You can use this software to recover database objects, such as forms, tables, reports, etc., from severely corrupted Access databases, and save them in a newly created database file. The software supports MS Office 365, MS Access 2019, 2016, 2013, and earlier versions.
Here are the steps to repair a corrupted Access database file using Stellar Repair for Access:
- Download and install Stellar Repair for Access on your Windows PC.
- Launch it.
- Click on the Select Database option under the Home menu.
- Click on the Browse button and select the corrupted Access database file.
- After selecting the file, click on Repair.
- After repair, the software will display all the recoverable objects of the database file. You can click on any object to see its preview.
- Select the objects you want to recover and click on Save Database.
- Now, select the destination to save the repaired database file. Click OK.
The software will save the repaired file at the selected location.
Conclusion
Several reasons like database corruption, enabled filters, or incorrect VBA code syntax, result in missing records from an Access table. If the Access table does not show all records, you can follow the abovementioned methods to resolve the issue.
The Compact and Repair utility can help you fix minor database inconsistencies and corruption. However, severely corrupt database files may fail. In such situations, you can rely on a specialized Access database repair tool like Stellar Repair for Access. With its advanced database repair algorithms, you can easily repair various errors related to database corruption.
