Error 3407 - Record is too large

[CedarTree]

Hello. Using a DB that has been repaired / compacted. And I'm running a series of SQL statements that update one table based on another table. The VBA runs fine for almost every field but as I near the end of the process (field #47 of #49), I get error 3047 record is too large. Any suggestions please? The issue seems specific to that one field (if I skip that one field, the rest continue to work so I doubt it's a memory leak, etc.). But when I look at the data in that field, I don't see any weird characters, or apostrophes, etc. Thanks.

 

[CedarTree]

Edit: it seems it has to do with records that have an & in the field value. How to fix please? Thanks!

 

[CJ_London]

Just a guess, use the replace function to replace ‘&’ with ‘[&]’

 

[cheekybuddha]

How to fix please?

Remember, we can not see your screen.

Perhaps post an example of the SQL statement you re using.

Also, describe the table involved (field names, datatypes etc)

Also, show any VBA code that runs.

49 fields in a table is often a sign of sub-optimal table design. Please explain the situation.

 

[CedarTree]

I lessened the # of fields (removed superfluous ones and it works now). Thanks.

 

[Pat Hartman]

There is a hard limit on the length of a record. 4,000 excluding Long Text and OLE Object fields when the UnicodeCompression property of the field is set to Yes

 

[The_Doc_Man]

I lessened the # of fields (removed superfluous ones and it works now). Thanks.

This should always be done. The more fields you have in a query, the less efficient Access will be in retrieving the records because it has to fit the "result set" in fixed-size disk buffers. The bigger the records in the result set, the fewer will fit into a single buffer.

 

[Pat Hartman]

I do agree with Doc though. Queries should never return more columns or rows than are absolutely necessary for the task at hand.

Select * is just lazy and wasteful.