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 is limited to 4000 records, and the total size of Access is 2GB.
If you create a Table that contains more than 4000 records, you will receive an error message Error 3047 Record is too large. To work around, you can create an Excel Worksheet and use Linked Table, so that you can create a Query that contains more than 4000 records, 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 to do 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 records which come from Linked Table, but when you have to JOIN another Table, even though the number of resulting records is less than original records (but over 400 records), Error 3047 will happen. I believe Access checks the number of records when you join a Table, but it doesn’t check when you only apply criteria to the Linked Table.
Workaround for Error 3047 Record is too large
In order to export Query of more than 4000 records 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"