Error 3407 - Record is too large (1 Viewer)

CedarTree

Registered User.
Local time
Today, 04:54
Joined
Mar 2, 2018
Messages
404
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

Registered User.
Local time
Today, 04:54
Joined
Mar 2, 2018
Messages
404
Edit: it seems it has to do with records that have an & in the field value. How to fix please? Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:54
Joined
Feb 19, 2013
Messages
16,604
Just a guess, use the replace function to replace ‘&’ with ‘[&]’
 

cheekybuddha

AWF VIP
Local time
Today, 09:54
Joined
Jul 21, 2014
Messages
2,274
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

Registered User.
Local time
Today, 04:54
Joined
Mar 2, 2018
Messages
404
I lessened the # of fields (removed superfluous ones and it works now). Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 19, 2002
Messages
43,257
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
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:54
Joined
Feb 28, 2001
Messages
27,156
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

Super Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 19, 2002
Messages
43,257
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.
 

Users who are viewing this thread

Top Bottom