Would db corruption be further minimized if I remove querying on a backend table from the user's front end?

Question

I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was not being split between front end and back end. The main process was that a user filled out a form and then VBA code would write this information to a table.

I now want to make a new version of this database where the front end is split from the back end. This is because there were numerous times where the whole database became corrupted and all our data, queries, and forms were lost.

I noticed that on one of the forms I have a control that is querying the table that is being moved to the backend. I'm not sure if having each user's local copy of the front end query the backend table just for this one control is worth it. I want to minimize db corruption, so should I just remove any sort of querying on the backend table for small stuff and only allow users one major write to the backend table? Or does this not matter?

Answer 1

If by 'querying' you mean the control is simply running a select statement against the data table, it is doubtful that would corrupt any data. If your control's query is manipulating data (add, delete, change) then it could very well be corrupting the data.

Thirty users on an Access DB is challenging. You may want to look into using a more powerful DB for the backend. If you don't, be sure to repair and compact your Access DB nightly.