How to prevent "unknown database design" errors caused by damage?

[Leo W]

I have an Access-database running with multiple users.



It had one back-end combined with dozens of front-ends of which 5 to 10 would be active simultaneously.
Occasionally, we had a damaged file that would block one of the front-ends. Which was then replaced.
When the damage occurred in the back-end it could double in size, but would still function. It was repaired and compressed once a week.
Over the years the back-end had grown to be close to 0.5 Gb and recently after two events close after each other it even passed 2.0 Gb.

To prevent running into the size limit of Access again, I decided to split the back-end into three separate databases with the largest, after compression, now being reduced to 0.21 Gb.

Following this action, two days ago, there have been multiple damaged files, all in the new, smaller, back-ends blocking the complete function of the database for everyone.
The error that is shown is "unknown database design" followed by one of the back-ends.
The damage may be repaired by Access, but not always. I then have to reinstall a back-up.

What is causing these errors and how can I prevent these from happening?
Will returning to a single back-end reduce these errors?

[NoellaG]

It could be you're running against the limits of Access there. Maybe time to upgrade to another back-end database system like MySQL, MariaDB or SQL server (express or standard)?

[Leo W]

We are indeed in the process of out-phasing this MS-Access application. But the new programs or updates that need to take over the current functionality may still take 2 years (or more) to all be in place.
In the mean time I'm juggling to keep everything running.

Would it be possible to work with different types of back-ends? For instance 2x MySQL and a third BE still as Access?
Two of the three back-ends contain only one large table each. These might easily be changed to another format.
The third back-end contains dozens of smaller files which are constantly used/updated. I wouldn't want to convert all these.

[NoellaG]

You only need one back-end server. On 1 server you can place several databases, and of course 1 database can contain several tables. For documentation on MySQL see https://dev.mysql.com/doc/refman/8.0...ize-limit.html.
MySQL also is better with working with several simultanious connections. So once you have a MySQL database, I don't see the point of keeping some backends on MS Access. Just put all your tables in one database again, as it was before.
Create the database and then upload all tables from the different tables into this one BE table. Normally the front-end in Access can remain the same if you don't change the datastructure. Just link the tables to the new backend.

If you upgrade to SQL server, the process is pretty quick and smooth. Best use the SQL server import data task to do this (much better than the Access upgrade wizzard). Of course, you'll have to do some tuning afterwards to speed things up. With MySQL, I don't know the possibilities, but I bet there is an easy way too.

[Bulzie]

Have you tried to see what is causing the corruption? Can you reduce the size of the backend files by deleting or archiving old data? Are you having multiple users editing the same records? I agree that you would be better going to SQL but if you can find out what process is causing the issues, maybe try to optimize that.

[Leo W]

This weekend nothing went wrong, but there was hardly any activity.
Just realized, the first 3 days it always crashed, for the first time of the day, between 09:30 and 10:00, every day (business starts at 08:00).
With no additional crashes on Thursday or Friday.
I'll ask the different departments for their morning routines.