You can get the error 3047: Record is a too large while importing/exporting data in Access or when adding or editing the records. It usually appears if the size of the records exceeds the maximum size limit allowed by MS Access. This error may prevent you from modifying the records or exporting the data to an external file.
What Causes the Error 3047: Record is too Large?
There are different reasons that can lead to the Access error 3047. Some of them are:
- Memo field (Long Text) size limit
- Large attachments
- Fields in the table exceeded the maximum limit of the file
- Importing a large dataset beyond the import size limitations
- The database contains duplicate data
- Database is corrupted
Resolve Access Error 3047: Record is too Large
First, check and remove the unnecessary attachments in the records in the database. It may help you reduce the overall Access file size. If it doesn’t fix the issue, try the below methods:
Method 1: Check the Field Size Issue
The number of fields in a recordset should be up to 255 and the record set size should not be more than 1 gigabyte. If the Access file’s data exceeds the designated size limit, then you can get the Record is too large error. You can check the field size using the below steps:
- In the Navigation pane, right-click on the desired field of the table that you need to change.
- Click Design View.
- In the design grid of the table, right-click on the field that you need to check and select Properties.
- In the Field Properties, check the Field Size option under the General section.
The field size value should be between 1 and 255. If the size value is more than 255, then convert the field to Memo Field/Long Text. Converting a field to a memo data type can help increase the storage capacity of that field.
Here are the steps to convert the field’s data type:
- In the Navigation pane, double-click on the desired table.
- Click on the field that you need to change.
- Click Fields. In the Properties group, click on the arrow in the dropdown list.
- Select Long Text and then save the applied changes.
Method 2: Use DoCmd.TransferSpreadSheet Method
You can have a maximum of 2 GB database file with a maximum of 4000 records. If the records you’re working with exceed the maximum record size limit, then you can get the error 3047. To fix this, you can create an Excel workbook and use a linked table. Create a query in Access (with a linked table representing that Excel file) and use the TransferSpreadsheet method to export to Excel.
Method 3: Use DoCmd.Output To Method
You can encounter the error 3047: The record is too large while exporting the query to another application. Try the DoCmd.Output To method in VBA to export Access query of more than 4000 records. Using this method, you can specify the output format to help you control the data you are trying to export. For more accuracy, you can export the query to XLS format.
Method 4: Split the Database
You can also split the database in Microsoft Access to resolve the size limit issue. Splitting the database segregates the data into two files, i.e., the back-end and front-end. The back-end database file contains the data tables and table links, whereas the front-end database contains data objects, such as forms, reports, and queries. It helps in reducing the overall size of the database file.
Method 5: Normalize the Table in the Database
The Access error 3047: Record is too large occurs due to large size of the records in the database. If you are getting this error while importing/exporting data between Access and other applications, then you can normalize the table. Normalizing the table in database helps in organizing data into multiple-related tables and eliminates data redundancy which eventually helps in reducing the record size. To do this, you can use the Access Table Analyzer. Here are the steps:
- Go to Database Tools and click Analyze Table.
- The Table Analyzer Wizard will display all fields in the table with repeated information. In the below image, you can see that the supplier information is repeated.
- Click on the Next option to split the table into two or more related tables.
- Click Next and follow the on-screen instructions.
The Table Analyzer divides the table with repeated fields into separate tables. This will reduce the database size and make it more efficient.
Method 6: Repair the Database
Sometimes, the MS Access error 3047 can occur due to corruption in the database. The corrupted database can create inconsistent records, leading to the Record is too large error. You can use the Compact and Repair utility in Access to optimize the database file, reduce its overall size, and repair it. Here are the steps:
- Open the Access application.
- Go to File > Info > Compact & Repair Database.
Access creates a copy of the compacted and repaired database file at the same location where the original database is stored.
If the Compact and Repair tool fails to fix the issue, then you can opt for an advanced Access repair software, such as Stellar Repair for Access. This Access repair software helps in fixing the corrupted .MDB/.ACCDB files and recovering all the objects, like tables, records, reports, queries, etc. The software can handle all types of corruption-related errors, including the Access error 3047: The record is too large.
Final Thoughts
The Access error 3047: Record is too large can usually occur while exporting data from Access or when trying to modify records that exceed the permitted limit. This error is related to the size of the records you are trying to export or modify. You can try the methods discussed above to resolve the error. If nothing works, then use Stellar Repair for Access – an advanced Access repair software that can repair Access database files with 100% integrity. It can recover all the data from the corrupted database. The tool is compatible with all Windows operating systems, including Windows 11. You can download the software’s demo version to evaluate its functionality.
Was this article helpful?
