I have been having a problem with by Access back-end part of my company database, maybe someone has an idea of what is causing this and how to resolve it?
Here is an explanation/example of the problem:
- Back-end crashes, front end cannot access it anymore.
- i look at my back-end file (ETDB_be.accdb) it has a size of 29,484kb.
- I already have a system in place to do regular backups of the back-end file to Excel and that automatically generated file of all the tables and data has a size of 9,876kb
- I re-start the computer holding the back-end file (only way i know to force all open connections to close)
- I make a copy backup of the back-end file just in case
- i the open the back-end file in Access and perform a "Compact and Repair Database" function
- After that i close the database and everything starts working again (front end connects and everything works no problem)
- But i notice that now the back-end file (ETDB_be.accdb) now has a size of 5,556kb - and if i do a backup to Excel it has a size of 9,876kb exactly like before.
So it looks like the access back-end file is somehow getting 'bloated' over time, reaching a point where it stops working, and doing a "Compact and Repair Database" function reduces its size back to what it should be (without changing the actual amount of data in it) and makes it work again (thankfully that works perfectly to solve the problem)
This happens to be roughly every 2 months.
Any ideas if there is a way to avoid this regular problem? or is there a way to automatically do a "Compact and Repair Database" every week or so?
as always, may thanks for any advice
Here is an explanation/example of the problem:
- Back-end crashes, front end cannot access it anymore.
- i look at my back-end file (ETDB_be.accdb) it has a size of 29,484kb.
- I already have a system in place to do regular backups of the back-end file to Excel and that automatically generated file of all the tables and data has a size of 9,876kb
- I re-start the computer holding the back-end file (only way i know to force all open connections to close)
- I make a copy backup of the back-end file just in case
- i the open the back-end file in Access and perform a "Compact and Repair Database" function
- After that i close the database and everything starts working again (front end connects and everything works no problem)
- But i notice that now the back-end file (ETDB_be.accdb) now has a size of 5,556kb - and if i do a backup to Excel it has a size of 9,876kb exactly like before.
So it looks like the access back-end file is somehow getting 'bloated' over time, reaching a point where it stops working, and doing a "Compact and Repair Database" function reduces its size back to what it should be (without changing the actual amount of data in it) and makes it work again (thankfully that works perfectly to solve the problem)
This happens to be roughly every 2 months.
Any ideas if there is a way to avoid this regular problem? or is there a way to automatically do a "Compact and Repair Database" every week or so?
as always, may thanks for any advice