MICROSOFT ACCESS DATABASE: WHAT IS THE ‘RECORD IS TOO LARGE’ ERROR 3047

1
MICROSOFT ACCESS DATABASE WHAT IS THE RECORD IS
TOO LARGE ERROR 3047
2
You maybe starting to think that Microsoft Access
is a buggy and is a full of bugs application
becoming very unstable indeed but the complexity
and the power of what MS Access provides out ways
some minor and silly errors that can
occur. Error code 3047 refers to records being
too large for a database file and typically
triggered when attempting to import data from
another external application.
3
The common method you will see this is when you
use the DoCmd.TransferSpreadSheet method which is
a VBA command to import and export datavia
typically Excel. Ironically, Microsoft Access is
a large database management tool but still has
issues handling the wrong size type of data and
there are size restrictions (even for the latest
2016 version). As a reminder, take a look
the specification sheet for Microsoft Access to
know what limits are set so you can determine the
correct methods for data migration and population
it may help and avoid the time-wasting
errors! If you create a Table that contains a
record with more than 4,000 characters, you will
certainly receive an error message Error 3047
(Record is too large). The simplest solution is
to cut the record characters, but if not
possible, you can create an Excel Worksheet
(which can contain a maximum of 32,767 characters
for a Cell) and then use the Linked Table, so
that you can then create a Query that contains
more than the 4,000 characters being exceeded and
repot out from there.
4
However, when you want to mass export Queries,
you need to do it in VBA using DoCmd.TransferSprea
dSheet Method to export to Excel, the Error 3047
will also raise its ugly head andprevent you from
doing that too. Importing data into a table for
larger volume character sets may require a tweak
to the table design to allow it through namely by
changing from an existingText data type to a
Memo data type which can hold larger characters
(bytes of data). Naturally, some performance may
be affected if you use too many fields set as a
Memo field, so a balance is needed here. Make
sure the data you are importing is actually
considered normalised for an Access database in
the first place since this is a different way to
storing data than say a spreadsheet. By observing
the Microsoft Access Normalisation rules, you may
actually avoid tweaking the data types and it may
just nicely fit into your table
structure. Heres a useful guide onimporting
data information (with our free eBook)here.
5
Contact us Ben Beitler
ben_at_accessdatabasetutorial.com (44) 7881
502400 United Kingdom London
https//www.accessdatabasetutorial.com/