How to Deal with “Record is too large” Error in MS Access

This article provides solutions for dealing with error ‘Record is too large’ in MS Access.

You might have come across a situation where you are attempting to import a seemingly small file in your Access database, but it is not as small as you thought it to be. This fact comes to light when it irks you by giving a warning that says ‘Record is too large’, error 3047. This might come as a complete surprise because the size of your file can actually be very small. To be able to deal with this error you first need to understand why exactly the error is all about. So to start with, that will be our very first point of focus.

Causes behind the error

What many Access users are not aware of is that there is a limit on Access records that restricts them to take not more than 2000 characters for the ‘actually available space’. Which means that a user has been provided with just 4000 bytes of Unicode, this is the kind of limit that is hardly challenging to exceed. A lot of the times they exceed in this limit is purely unintentional, leaving the oblivious user with another complex error to deal with.

Moving ahead of the causes behind this error, we will now attempt to understand the possible solutions to a given problem.

Dealing with the error

A few of the common fixes that you can make use of when witnessing this error have been explained below.

1. Try Redefining the Table – One of the most commonly opted for fixes to this problem is to try redefining the table all over again. This will involve not just the removal of old and unnecessary fields, but also the addition of a few new fields.
2. Make use of Memo Fields – You can try dealing with error 3047 by making use of Memo type fields and not Text type fields. You can set the field type to memo by setting the value of field type property to 12. If you have it at 10, you will again get a text field. If you end up crossing the given limit in case of a memo field, Visual Basic will make use of a separate page for putting these fields in the database.
3. Make use of DoCmd.TransferSpreadSheet Method – According to the 2016 edition of MS Access, the limit of each table is set to 4000 records, with a total size of 2 GB. So if you end up creating a table that crosses this limit, you will have to deal with this error.

Workaround

Apart from using an Access fix tool or ways of dealing with the error given above, you can also make use of a workaround that involves making use of an excel spreadsheet and link it back to the table. This will allow you to have a query that is able to store beyond 4000 records and export to excel whenever there is a need, that too without any errors.

However, a key thing to keep in mind over here would be to make sure that you are making use of VBA when exporting mass queries

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and excel recovery software products. For more information visit www.datanumen.com