MS Access Frequently Crashing and Creating Automatic Backup
Hello Experts:
We have a departmental Access database in the .MDB format that's been hovering around 1.6-1.7gb in size. The database is exhibiting unusual behavior in that as of a few days ago, it began frequently crashing and creating an automatic backup. There are 1,200 objects in this database...would it help if we created a new shell and imported all of the objects into the new database?
We have a departmental Access database in the .MDB format that's been hovering around 1.6-1.7gb in size. The database is exhibiting unusual behavior in that as of a few days ago, it began frequently crashing and creating an automatic backup. There are 1,200 objects in this database...would it help if we created a new shell and imported all of the objects into the new database?
Also, investigate for uneeded data (too old, no longer used ect ….) and move them into an archive copy.
1.7GB is unusual for an Access Database, and that's close to the max allowed size (2GB).
If the database's size can't be reduced, look for another SGBD (SQL server, MySQL, Oracle ect ….).
1.7GB is unusual for an Access Database, and that's close to the max allowed size (2GB).
If the database's size can't be reduced, look for another SGBD (SQL server, MySQL, Oracle ect ….).
ASKER
Hi Jim. Its a single file, not split, with about seven users on a windows server (that's all I know about the server) . We're using Microsoft Office Prof Plus 2013.
Nothing has changed in the environment. There was the Update Query bug in Dec which is fixed. Other than that I cant tie the crash/backup to a particular action (yet). It isn't too bad....happened twice two days ago but not yesterday. Then once today. We've also been getting random "inconsistent state" errors
Nothing has changed in the environment. There was the Update Query bug in Dec which is fixed. Other than that I cant tie the crash/backup to a particular action (yet). It isn't too bad....happened twice two days ago but not yesterday. Then once today. We've also been getting random "inconsistent state" errors
<<We've also been getting random "inconsistent state" errors>>
See:
https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d
You probably had an update installed, which is why you are seeing it now.
Turn leasing off on the machine that is hosting the DB.
and you really should look into splitting the app into a Front end/back end.
Jim.
See:
https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d
You probably had an update installed, which is why you are seeing it now.
Turn leasing off on the machine that is hosting the DB.
and you really should look into splitting the app into a Front end/back end.
Jim.
ASKER
Jim, I only see KB4534273 as recent. I did see your earlier posts regarding leasing. Other observations:
1. In addition, there is one person in the database now and it is working fine for him, but when I attempted to open it I got the Inconsistent State error, even though he can continue to run queries. When he closed it the database kicked off a crash/backup
2. I have a second database in the .accdb format with a few tables linked to the main database in the .mdb format. It has happened three times today when I had my other database open. Is it possible that could be an issue?
1. In addition, there is one person in the database now and it is working fine for him, but when I attempted to open it I got the Inconsistent State error, even though he can continue to run queries. When he closed it the database kicked off a crash/backup
2. I have a second database in the .accdb format with a few tables linked to the main database in the .mdb format. It has happened three times today when I had my other database open. Is it possible that could be an issue?
<< I only see KB4534273 as recent.>>
Office back to 2013 is largely the same, but this is not an Office bug, it's a Windows one that impacts Access (as well as other products like Access that allow multi-user access to files).
Leasing allows client side caching of a file.
Jim.
Office back to 2013 is largely the same, but this is not an Office bug, it's a Windows one that impacts Access (as well as other products like Access that allow multi-user access to files).
Leasing allows client side caching of a file.
Jim.
ASKER
I have a second database in the .accdb format with a few tables linked to the main database in the .mdb format. It has happened three times today when I had my other database open. Is it possible that could be an issue?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok. Thanks Jim
Let me reiterate Jim's comment.
You REALLY SHOULD SPLIT THE DATABASE, and every user SHOULD HAVE THEIR OWN COPY of the front-end.
You REALLY SHOULD SPLIT THE DATABASE, and every user SHOULD HAVE THEIR OWN COPY of the front-end.
ASKER
Hello Dale. Do you think the single file structure is contributing to this problem? It seems to happen even when there is only one person in the database. Also, this application is 14 years old and this behavior just started this week
Today, people can connect to the network with wireless connection, wich is a major source of troubles (and corruption).
Splitting the database will keep the data safe.
Splitting the database will keep the data safe.
First step, split the database and give each user their own copy of the FE. This will result in smaller FE and BE files.
Then, if the BE file is still over 1 GB, I would split that into two or more back-end files. The problem with this scenario is that if you have relationships defined, as you should to prevent orphaned records, you need to make sure that you keep the tables with related records in the same back-end. For example, you might put all of the HR related tables in one back-end, Accounting tables in another BE, etc. If you do this, you will then have to relink those tables from the new BE into the FE. Make sure you use the full network path in the connection string to the back-end rather than the drive name.
Then, if the BE file is still over 1 GB, I would split that into two or more back-end files. The problem with this scenario is that if you have relationships defined, as you should to prevent orphaned records, you need to make sure that you keep the tables with related records in the same back-end. For example, you might put all of the HR related tables in one back-end, Accounting tables in another BE, etc. If you do this, you will then have to relink those tables from the new BE into the FE. Make sure you use the full network path in the connection string to the back-end rather than the drive name.
Personally I would not change anything right off.
A split design is the best for a number of reasons, but if you haven't had issues to this point and all of a sudden are, then the answer is most likely not with splitting, so you are making a change that potentially could introduce new problems.
Get the corruption issue sorted out first, then split after that.
Jim.
A split design is the best for a number of reasons, but if you haven't had issues to this point and all of a sudden are, then the answer is most likely not with splitting, so you are making a change that potentially could introduce new problems.
Get the corruption issue sorted out first, then split after that.
Jim.
on second thought, I would agree with Jim regarding turning off leasing as the first priority.
Getting that done may resolve the corruption problem. But even if it does, you still need to split the database and give everyone their own copy of the FE.
Getting that done may resolve the corruption problem. But even if it does, you still need to split the database and give everyone their own copy of the FE.
ASKER
Fabrice: re wireless connections. We have considered that and have asked users to not disconnect from their docking stations (which then connects wirelessly) with the database open.
Dale/Jim: Thanks for your comments. And thanks for explaining the issue of keeping related tables in the same database. This is good to know. One question that someone raised that I don't know the answer to, is that if you split the database, what is the difference between everyone using the same front end and everyone having their own front end? Thx
Dale/Jim: Thanks for your comments. And thanks for explaining the issue of keeping related tables in the same database. This is good to know. One question that someone raised that I don't know the answer to, is that if you split the database, what is the difference between everyone using the same front end and everyone having their own front end? Thx
<<what is the difference between everyone using the same front end and everyone having their own front end? Thx>>
Couple different things:
1. It allows for temp tables to be used easily without worrying about one user stepping on another.
2. Ditto for Access objects, say a querydef is modified in the running of a report. If you don't prevent multiple users from running the report, a user could get bad data.
3. Some objects are saved automatically, and it's possible for one user to step on another.
Outside of that, it's somewhat of a myth that users can never share a FE or it will lead to corruption. That's not entirely true at this point. Microsoft made a change quite some time ago where the VBA project is saved as a chunk and to modify code, you need exclusive access.
But even with that aside, there are many cases where all users share the same copy and it works fine, but if you are not careful about development, you can have issues.
It also cuts down on network traffic; share a copy and everything is coming over the network to your station. Again, not as much of an issue as it once was, but still, it's not efficient.
Last, it makes it easy to roll out changes. You don't have to worry about the data tables when you update other things.
So the best setup is a split DB with each user having their own copy.
Jim.
Couple different things:
1. It allows for temp tables to be used easily without worrying about one user stepping on another.
2. Ditto for Access objects, say a querydef is modified in the running of a report. If you don't prevent multiple users from running the report, a user could get bad data.
3. Some objects are saved automatically, and it's possible for one user to step on another.
Outside of that, it's somewhat of a myth that users can never share a FE or it will lead to corruption. That's not entirely true at this point. Microsoft made a change quite some time ago where the VBA project is saved as a chunk and to modify code, you need exclusive access.
But even with that aside, there are many cases where all users share the same copy and it works fine, but if you are not careful about development, you can have issues.
It also cuts down on network traffic; share a copy and everything is coming over the network to your station. Again, not as much of an issue as it once was, but still, it's not efficient.
Last, it makes it easy to roll out changes. You don't have to worry about the data tables when you update other things.
So the best setup is a split DB with each user having their own copy.
Jim.
Fabrice: re wireless connections. We have considered that and have asked users to not disconnect from their docking stations (which then connects wirelessly) with the database open.
Rule of maximum pain (or Murphy's law): Never trust users.If you leave the opportinity to do someting stupid, sooner or later, it will be done.
In other words: Better feel safe than sorry.
Hard to say, but certainly it would not hurt. Can you go into a little more detail about the crashes and how the DB is setup.
Is it a split design (each user has a copy of a front end and a single back end with the data)? What is serving up the back end? Station or server? and what version of Access is everyone using? Anything changed in the environment recently?
Jim.