This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method.
Access Error 3047 Record is too large
Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database.
According to Access 2016 specifications , Access Table record is limited to 4000 characters, and the total size of Access is 2GB.
If you create a Table that contains a record with more than 4000 characters, you will 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 (maximum 32,767 characters for a Cell) and use Linked Table, so that you can create a Query that contains more than 4000 characters, and then export to Excel without problem.
However when you want to mass export Queries, you need to do it in VBA. When using DoCmd.TransferSpreadSheet Method to export the Query / Table to Excel, Error 3047 Record is too large would prevent you from doing that.
Root cause of Error 3047 Record is too large
According to the explanation of Microsoft website, the error is due to the follow reason.
You defined or imported a table with records larger than 4K. This error occurs when you enter data into the record — not when you define the table structure. Redefine the table by making some fields shorter, removing unneeded fields, or moving some fields to other tables.
However this statement doesn’t explain how the error happens when using DoCmd.TransferSpreadSheet Method. In my testing, it is possible to export a Query that contains more than 4000 characters from Linked Table, but when you have to JOIN another Table, I believe Access performs checking.
Workaround for Error 3047 Record is too large
In order to export Query with records of more than 4000 characters in VBA, try to use DoCmd.OutputTo Method.
The syntax is slightly different from DoCmd.TransferSpreadSheet Method, but you will get well formatted headers same as that in Access Query. However, the file size is larger and it takes longer time to export.
But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.
DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:\test\test.xls"