Record is too large

[james28]

Hello all,

I have a small issue with a database. In 2010, I understand that the record size is capped at 4,000 characters, excluding memo and ole. That is perfectly fine. In one of my records, I was at about 3,900 characters and it gave the error that the record was too large and I could not do anything. After compacting and repairing, it fixed the issue. The number of characters did not change though. Is there any reason this would happen and any way to prevent it in the future?



Thank you!

[rpeare]

Unfortunately MS access is not memory efficient, when you change/delete/add data it 'saves' the space in your database and doesn't release it until you do a compact/repair. So if your field was one that routinely gets updated or changed you will probably get it again at some point. In versions of Access greater than 2007 you can set the database to compact on close which *should* relieve this issue for you.

[james28]

Rpeare,

Thank you for the suggestion. Do you know if there is any risk to the database corrupting itself on a compact/repair? My fear in having it run automatically would be that it would break itself.

[rpeare]

I have only encountered a problem with compact/repair in one instance, and it was on a database that was routinely updated with about a million records a week (all records were deleted, then updated from an external text file). The database (2003 version) hadn't been compacted in a long time and it was gigantic, I don't know whether that was what caused the failure or not, but the database became corrupt when it finally was attempted.

The long and the short is that I haven't ever encountered a problem with compact/repair except under one extreme circumstance, and the database was backed up regularly so it was possible to restore it, then import all the objects to a new database as an alternate form of compact/repair.

[james28]

Rpeare,

Thank you for the follow up. It was very informative.

Do you happen to know how the auto compact/repair is handled on a split database?


For example, I have the database split so that the actual tables are never directly edited. Instead, users use my front end database, which has a few forms and reports. If I were to add the auto compact/repair to the front end database, would I compare/compact the linked tables?

[June7]

Review this discussion https://www.accessforums.net/access/...xit-45876.html

C&R on frontend does nothing to the backend.

Since users should be running their own copy of frontend and should not be doing any design edits, don't see how auto C&R is useful for split db.

[rpeare]

I'll add to what June said, once a database is built a back end should not really need to be compacted/repaired except very occasionally. When I build a database I try to disallow deletes in case something gets removed by accident there is no way to get it back unless you restore the whole database. However, if you do allow the removal of data (particularly large amounts of it) a compact/repair may be needed periodically on the back end. If you are not doing lots of deletion/addition of records as June said, there's really no reason to need to do a regular compact/repair on the back end.

Secondly I do not use bound forms at all so I do not know how they perform in terms of bloating your front end database. I use a lot of unbound forms/controls on all my data entry and those *do* bloat over time, but I haven't really had one that *needed* to be compacted/repaired more than about once every 3 months. June has very rightly pointed out that once your development is done the bloat factor is pretty small under normal circumstances.