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.
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.
However, when you want to mass export Queries, you need to do it in VBA using DoCmd.TransferSpreadSheet 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 existing‘Text’ 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.
Here’s a useful guide on importing data information (with our free eBook)here.
Tags: building microsoft access databases, converting microsoft access databases, deploying your microsoft access database, how to speed up slow microsoft access database, microsoft access database, Microsoft Access Database Error