Access VBA "Record Too Large"

Question

I have not found an answer to this issue on the net: maybe an Access bug?

I have Windows 10, and Access 2016. I have 102 fields and 40 records. There are 14 long text fields for each record. None of the long text entries contain more than 200 characters. The records in question are set to "Long Text" (used to be "Memo" in earlier Access versions).

The software that I wrote and have used for 5 years with Access 2010, imports an Excel Workbook. Now I use that same software with Access 2016 and have started getting the error described here. This is the 4th db I have setup using Access 2016 and this is the first time I have seen this problem.

When I tried to type entries into one or two cells in a "long text" field in a record, an error was generated saying the "Record is too large". The same field on other records work as expected. Only the cell on that given record is generating an error. Like I said, I have never seen this error in other versions of Access.

I have performed 1) "compact and repair", 2) exported the table to a new table, and 3) exported the table to Excel and, with a new Access record, cut and paste by hand all 102 records. Item number 3) works most of the time, efforts 1) and 2) have never fixed the problem.

The incident leading me to seek help is that this time, performing step 3) above, with a new record, I have one cell that generates the "record too large" error again. I noticed the entry cell in Excel that I was copying from had several semi-colons: I removed them, tried to cut and paste to the Access Cell with no success. I tried typing the entry into the cell instead of pasting it and get the error.

I really am at a loss to what the issue is with this problem and I need some help. Has anyone ever experienced this issue?

Answer 1

I have 102 fields and 40 records. There are 14 long text fields for each record. None of the long text entries contain more than 200 characters.

I'd refactor the schema, yesterday. This is exactly what one-to-one relationships are for. Move a subset of columns into another table, relate PK to PK. 102 columns is too many concerns stuffed into one single table. Break it down - regardless of the "record too large" error.

That said if none of the long text entries contain more than 200 characters, then why are they long text in the first place? I'd make them variable-length character columns (that would be nvarchar on SQL Server, not sure about Access), with perhaps 255 characters capacity.

Answer 2

thank you for all of the input. I cannot post the code. I was, however, able to work a solution for the issue as described below in case others get in this same predicament.

1. Export offending Table to Excel, preserve formatting.
2. Export offending Table to Access, same db, preserve definition, do not export data.
3. Change several (offending) Fields in the blank (new) table from "Short Text" to "Long Text" (Memo).
4. Append the exported Excel sheet created in step 1. into the blank Table created in step 2.

These steps resolved my issue and got me out of a painful jam. Thank you all for the help and ideas. v/r, Johnny

Answer 3

There is a chance you found Excel-import-related bug in Microsoft Access.

Create the table anew to work around the defect to get its internal data right.

The problem is in larger tables auto-created on import from Excel. Even if the length or count of their fields does not exceed any limits, you can still start getting error "Record too Large". Executing Compact and Repair action does not remedy this issue.

So if you are sure that your data structure does not exceed Access limitations and then you re-create the table with the same fields and their lengths, the error is gone.

As a proof, currently I have two tables in my database, both with identical internal structure. The one created by import reports "Record too Large" and the one I created manually (by copy-paste of fields in design view) is OK.

So we can say that upon import from Excel, there is a specific Access bug which was not corrected as of today (2018-10-17) in Access 2016. Work it around in the above way.