We help IT Professionals succeed at work.

MS Access Frequently Crashing and Creating Automatic Backup

Davisro
Davisro asked
on
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?
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
<< 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?>>

 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.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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 ….).
DavisroBudget Analyst

Author

Commented:
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
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
<<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.
DavisroBudget Analyst

Author

Commented:
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?
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
<< 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.
DavisroBudget Analyst

Author

Commented:
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?
President / Owner
Fellow 2019
Most Valuable Expert 2017
Commented:
<<It has happened three times today when I had my other database open. Is it possible that could be an issue?>>

 An issue in that it triggers the leasing bug yes.   The fact that your dealing with a .MDB no.

 You need to turn off leasing as a first step.   Beyond that, database corruptions can be caused by a number of things (i.e. anti-virus) and it usually takes some detective work to figure out what.

 But if the machine hosting the DB file(s) is past Windows build 1903, then you would be open to the leasing bug.  Microsoft has not given us any details about what triggers it and when it might occur, so it's hard to say if that's what you are running into or not.   The only way to check is to turn off leasing and see if the corruptions go away.  If not, then you look further.

  But anyone with recent "inconsistent" database errors that start up all of a sudden have all seen the problem go away when leasing was turned off. It accounts for 99% of the cases.

Jim.
DavisroBudget Analyst

Author

Commented:
Ok. Thanks Jim
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Let me reiterate Jim's comment.  

You REALLY SHOULD SPLIT THE DATABASE, and every user SHOULD HAVE THEIR OWN COPY of the front-end.
DavisroBudget Analyst

Author

Commented:
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
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
DavisroBudget Analyst

Author

Commented:
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
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
<<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.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.