Can someone tell me if this is going cause a problem.
We are using an Access based system that has around 10 users and I set up the database so that when it closes it automatically compacts. Trouble is one user may exit the database but the other 9 users could be in it. Will this
1) Cause issues for the other users in the database if they are still updating dat
2) Corrupt the database.
If either of these issues are the case, is there a way of only compatcing the database when the last user has exited the system?
I am not sure what kind of problems will occur. But what we always do, is separate the application layer (front-end) from the datalayer (back-end). Every user will have his/her own application which has a linked tables to the back-end database. From front-end database it is possible to manage compacting of the back-end database. Separation of these layers will have the advantage when installing an upgrade in the front-end, you can leave the back-end database intact.
Also it is better to compact the front-end database when it will be closed. Running queries will blow up the front-end database also.