Import in ACCESS: DoCmd.TransferSpreadsheet acImport - ERROR "Record is too large"

[esoestemp]

Hello,
I need some help with the following issue.
The task is about importing more than 100 sheets from one excel file.
I tried with SavedImportExports, but it is not flexible for management.
Then I made some progress with Macro>ImportExportSpreadsheet - in design view and the modified in VBA to the following code.

It is working, but for some imports I receive error - "Record is too large".
Please for help how I can solve this?

===================
Option Compare Database

Function Macro1()
On Error GoTo Macro1_Err


DoCmd.TransferSpreadsheet acImport, 10, "TargetTable1", "SourceTable", True, "Sheet1$"
DoCmd.TransferSpreadsheet acImport, 10, "TargetTable1", "SourceTable", True, "Sheet2$"
.
DoCmd.TransferSpreadsheet acImport, 10, "TargetTableN", "SourceTable", True, "SheetN$"

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function
===============

Thanks in advance!

 

[ranman256]

I have NEVER seen this. How can a single RECORD be too large.
Does any cell have a large text paragraph?
Are all fields in the target table set to max? ..(ie, text = 255, value = long, etc)

 

[Micron]

how many fields are there? I think there is a limit of 2000 bytes. Maybe on some records you have exceeded this if they're all 255 and/or contain memo fields. Maybe add up the memory byte values for all the fields and see what you get.

 

[esoestemp]

Thanks ranman256&Micron,

Good suggestion - in some of the cells there are 6-700 characters - it seems the issue.
How can I set memo flag in the import function?

 

[Micron]

I have no idea what a "memo flag" is and didn't find anything on it. When they comment on the record size limit, some say it's 2000 bytes, some say 2000 characters, some say 255 fields. You did not answer the question of how many fields. Suggested solutions lie in breaking up the transfer into more tables, or transferring data as delimited text. Not sure I grasp how that one will help.