Compacting Error 1026 (Record is too large) (1 Viewer)

[Stormin]

Hi all,

I have a working database that I built. Recently (on a copy) I added some more functionality to a table that stores vehicle model codes i.e. factory code that designates model, engine, gearbox, trim level etc.
The long model code that is stored in the table is a concatenation of several strings that all point to different parts of the vehicle. Usual stuff.

I needed to run a query relating each individual part of the string with another table, so in order to separate out the long code I added 11 calculated fields to the CODES table each with a simple expression such as MID$([LONGCODE],4,2) which takes characters 4 and 5 into their own field. I then use these fields as relationships to other tables that describe what each section means.

Everything works fine until I "Compact and Repair" the database. I get an MSysCompactError table which describes an error on the CODES table for every record. Access also deletes all the records in the CODES table without prompt. When using the in-built backup function this happens silently with no error table (good job I checked it before pushing it live!)

ErrorCode | ErrorDescription     | ErrorRecid | ErrorTable
    -1026 | Record is too large. |            | CODES

This is definitely related to the calculated fields since it was working correctly before I added them, and when I remove them it also works correctly with no error upon compact or backup.

My current workaround is that I've built the MID$ expressions into the SQL for the new query. However this is not an ideal situation for future editing by another user, especially when the original function works and is simple enough to stay in design view. Occam's razor and all that.

Does anybody have any ideas on how to stop the compacting error, or can elaborate on "Record is too large."? I have searched online but to no avail.

Thanks in advance

 

[NauticalGent]

Hi Stormin,

Unfortunately, I do not have an answer as to why this is happening. However, allow me to offer some advice: Do not use calculated fields. I do not know why the developers at Microsoft thought this was a good idea...maybe it was to help the Excel crowd get over their intimidation.

I have had nothing but problems with them and you can achieve what you are trying to do at form/report run-time.

Best of luck!

 

[Stormin]

Ha, I knew I would get stick for using calculated fields. I guess I'm an Excel user through and through

If there is no blaringly obvious fix I'm reluctant to try and find one, since I have a stable and working workaround. Plus the less I use calculated columns, the better, right?

Update:
My second and more user-friendly solution is to create a query that achieves the same result as the calculated columns (using the same MID$() function), and then match the reference tables against that. Works and is easy to see exactly what is going on should another non-code user edit in the future.

I have a sneaky suspicion this is what I should have done in the first place. Damn my Excel brain.

 

[NauticalGent]

I have a sneaky suspicion this is what I should have done in the first place. Damn my Excel brain.

Hardest hurdle to jump, but once you clear it, the world expands in ways you will stare at in amazement.

Good job on the re-structuring!

 

[Minty]

To be fair calculated columns are available in SQL Server and have been for some time and I can see certain circumstances where there would be some use for them. In theory they are no different to a calculated field in a query.

I wonder if there is a limit on how many you can have per table? 11 may be too many?