Access Database Corruption

plokij5006
plokij5006 used Ask the Experts™
on
Hi,

We're having an issue at the moment with an access database repeatedly going corrupt. We've been restoring it from shadow copies for now but this obviously isn't a feasible long term fix.

The database itself is on a file server (running Server 2008 R2), and is accessed one of two ways: via Access 2019 on some users desktop machines, and the rest of the users via Access 2010 on a virtual terminal server running 2008 R2.

The issues first arose after office was updated (including access) to 2019 for the desktop users, so I'm pretty sure it's the database being accessed by two different versions that's causing issues.

Does anyone know if there is a way around this? We'd have liked to upgrade the server to Access 2019, but it seems that it needs to be running server 2019 to do so, and we'd ideally like to avoid having to upgrade the Server OS.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
More likely it's the leasing bug:

https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d

 But some more details would be better.   How long has it been going on, any recent changes, error message your getting, setup, etc.

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
and by the way, is the database "split" into a front end and back end with each user having their own copy of the front end (this is what I meant by setup)?

Jim.
ste5anSenior Developer

Commented:
What does corrupt mean?

Microsoft introduced an Access error for all supported Access versions, which manifest when executing queries as "Query 'xyz' is corrupt. In this case see:

Access error: "Query is corrupt"
There are fixes for some versions, for others you need to rollback the November security updates.

When you mean file corruption, then you need to check the following things:

- Is the application split into separate front-end (forms, reports, queries) and back-end (data) database file?
- Has every user its own front-end copy in his user profile (local to the machine)?
- Are the users aware of explicitly closing the front-end before sending the machines to hibernate or before carrying around the notebook?
- Is the back-end database file accessed over LAN? WLAN and instable WiFi is a no-go.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Your server version has nothing to do with your Access version - especially since the ONLY thing you should have on that server is the Access backend (i.e. the database tables only). As the other Experts have said, each user should have their own local copy of the Frontend, and all of those Frontends connect to the same backend on your server.

If you're sharing the Frontend, and if your users have different versions of Access, you could certainly be running into corruption issues. A database opened in 2019 could easily render that database unable to be opened by earlier versions.

Author

Commented:
Hi All,

Thanks for the contributions - much appreciated. I'm not too sure on if the DB is split into a front/back end - I think it is but I'll need to confirm that and come back. The actual error we're seeing is below:

erroraccess.png
ste5anSenior Developer

Commented:
I'm not too sure on if the DB is split into a front/back end [..]
Check this, and when it's not, then split it. Each users must use it's own local copy of it.

Using different Access versions on a shared front-end won't work.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
As I commented earlier, it's most likely the leasing bug and you need to turn off leasing:

https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d

Jim.

Author

Commented:
Hi Jim,

We've implemented that reg key on the server hosting the database, but it's corrupted again unfortunately.

Thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
So is this something new then or an old long standing problem?  

 and as a double check, I would verify that the key was added properly and that the net service was stopped and re-started (or the server rebooted).

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
and outside of Access 2019 being added, any other changes?  Especially in the networking area.

 Also, are users on wired or wireless connections?  Everyone using the app should be on a wired connection.  

Jim.

Author

Commented:
Hi Jim,

It's a new issue that's only starting to occur around a month ago or so, at the start we'd restore and it would be fine for a week or so, but now it's generally only lasting an hour or so before it goes corrupt again. The users are all connecting using a wired connection. The key is definitely showing, we restarted the services but didn't give the server a reboot, which is probably worth a shot. In terms of changes being made, the only thing I can think of is the Office/Access 2019 rollout to workstation users, this happened in July or so.

Thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
In terms of changes being made, the only thing I can think of is the Office/Access 2019 rollout to workstation users, this happened in July or so.

 and prior to that, no issues?

Jim.

Author

Commented:
Hi Jim,

No issues before that, it was working fine with all users on the same version of Access (2010) which makes me think that the only way to get it working again properly is to get the server upgraded, but we just want to make sure that there isn't a fix we can implement for it.

In regards to the database being split, I'm not actually too sure if it is. Apologies if this is a daft question but is there a way to check if it is?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
In regards to the database being split, I'm not actually too sure if it is. Apologies if this is a daft question but is there a way to check if it is?

 Open the DB on the server; should be nothing in there but tables.   This would be a "back end".

 If you open the DB from the users station, then it should have everything but tables local to it.   The tables will all show as linked tables.  This would be the "front end".

 An un-split DB would have everything in it; tables, forms, reports, etc.

 You should also check the shortcut on the users station; should be pointing to a local drive/directory.    If it's pointing to the server, then most likely it is not split (although it could be a shared front end, pointing to a back end).

 The best setup is for the app to be split into a FE/BE, and each user of the app should have their own copy of the FE.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
Maybe...just maybe there could be some "action" that you or a user performs and leads to corruption...its rare but it does happen...
1st step ensure that you have FE+BE....just open the application on one of your workstation and check the tables...if your tables (most) have a little arrow before their name
Clipboard03.jpgThen probably you are following the FE+BE...if not then its a shared database (bad)

Author

Commented:
Thanks everyone - we've managed to confirm that it is indeed a split database. At the moment, we're restricting users from accessing the database, and are just going to let the users with 2019 use it. If it doesn't corrupt, we're going to ask the 2010 users to edit it and see if we start getting the issues.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
If it's split, then does each user have a copy of the FE installed locally on their machine? That is, they're not all launching the same FE from a network shortcut?

To recap: In a multiuser environment, each user should have their own copy of the FrontEnd installed directly on their local machine. Each of those copies would connect to a common backend.

Author

Commented:
Hi All,

It looks like our issue is resolved - the backup we were using seems to have been after some slight corruption. After restoring further back, we've seen no further issues after making the leasing reg change. Thanks for taking the time to give us a hand with this one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial