Avatar of Davisro
Davisro
Flag for United States of America asked on

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?
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon
Jim Dettman (EE MVE)

<< 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 Lambert

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 ….).
Davisro

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jim Dettman (EE MVE)

<<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.
Davisro

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?
Jim Dettman (EE MVE)

<< 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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Davisro

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
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Davisro

ASKER
Ok. Thanks Jim
Dale Fye

Let me reiterate Jim's comment.  

You REALLY SHOULD SPLIT THE DATABASE, and every user SHOULD HAVE THEIR OWN COPY of the front-end.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Davisro

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
Fabrice Lambert

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 Fye

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

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 Fye

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.
Davisro

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Dettman (EE MVE)

<<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 Lambert

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.