fix access Record Is Too Large error

Is your Access database document is frequently showing error “3047- record is too large”?

If yes, then not to worry as you can fix this error by your own  because this post contain all the workarounds needed to fix this Record Is Too Large Access Runtime Error.

Issue:

Although Microsoft Access is a database management system but unfortunately it doesn’t allow users to put over the limit data in the database.

The following error usually occurs when you update or add a text field in table when the table record size exceeds about 2000 bytes for all fields combined.

Record is too large.

Trappable Error code is 3047

Acces Error 3047

Cause Of Access Error 3047 Record Is Too Large 

Records in table of Access database are having a limit of under 2k, not counting memo fields. The “record is too large” error usually occurs when you enter data into such record not when you define the table structure.

Further Reading:

5 Instant Fixes To Repair Damaged Access Macros And Modules

Resolution To Fix “Record Is Too Large” Error

Here are the fixes to resolve Access Record Is Too Large Error.  So just follow down this manual fixes and easily get rid of this record too large issue.

Fix 1: Redefine The Table

Redefine the table again by creating some short fields or by removing unnecessary fields.

 

Fix 2: Using Fields With The Memo Type

You can avoid this Record is too large Access 2016 error by using fields with the Memo type instead of Text type. Set the field type property to 12 in order to get the memo type, instead of 10 to get a text type.

When memo field crosses the limit of 250 bytes or whenever the 2k limit is reached on a record, Visual Basic will automatically puts the memo fields on a separate page in the database file.

If you text field has such related data, you could improve space usage by concatenating the fields into one large memo field.

Fix 3: Using DoCmd.TransferSpreadSheet Method

As per the Access 2016 specification, Access table has limit of 4000 records and the total size of access is 2 GB.

So if you create the table having more than 4000 records, you will receive an error message Error 3047 Record is too large.

To workaround, you can create an excel worksheet and use linked table. So, that you can create a query that efficiently keep more than 4000 records and then export to excel without any issue.

However when you mass export queries, you need to do it in VBA. When using DoCmd.TransferSpreadSheet Method, just to export the Query / Table to Excel, this particular record is too large error would prevent you to do that.

You Might Also Be Interested In:

[Infographics] Top 5 Manual Methods To Repair MS Access Database Files

How to Repair MS Access Database when “the compact and repair operation has been cancelled” error occur?

Guide to Compact and Repair Microsoft Access Database File: An Effective One

How to Back Up a Microsoft Access Database

Fix 4: Using DoCmd.OutputTo Method

To export query of more than 4000 records in VBA, try to use DoCmd.OutputTo Method. Well the syntax is a bit different from DoCmd.TransferSpreadSheet Method. But you will get well formatted headers as that in Access Query. However the file size is larger and it takes much time to get exported.

But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format, some Access format doesn’t supports well and displays error each time when you try to open the exported workbook. Users need to save the file as a new one for auto repair. To fix this, you can export as XLS format instead.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:=”Query1″, OutputFormat:=acFormatXLS, Outputfile:=”C:\test\test.xls”

 Fix 5: Use Access Repair And Recovery Solution

You can use professional third party Access Repair and Recovery Tool that is designed to fix, repair and recover all version of Microsoft Access database such as 2016, 2013, 2010, 2007, 2003, 2000, etc and repair both Access (.mdb & .accdb) database file format.

 

Conclusion:

Hopefully now you must have got what you need to do in order to fix this “record is too large” Access run-time error.  Try all these workarounds carefully but our recommendation is to go with the Access Repair And Recovery Tool if you are a novice user or don’t have much technical knowledge.

5 Instant Fixes To Resolve Access 3047 “Record Is Too Large” Error…!