Microsoft Access Database is in an inconsistent state [duplicate]

Question

We have a client that uses our MS Acccess based software, they are up to 20 users working on the front end at the same time. Each user has its own .mde front end and the backend database is a .accdb... My question is why only this clients database keeps getting corrupted or in an "inconsistent state". None of our other clients expierence this problem.

Any tips or advice?

Decorous

It might be, that one or more workstations are connected via WiFi. – Overact

Because you ask for a tip also: I suppose you already know this, but in this scenario you should really switch to a SQL Server as backend, especially since the Express version is free after all. Furthermore, I have seen anti-virus software, which monitor network traffic, do the craziest effects already. – Assuasive

This has nothing to do with VBA or coding - the question is off-topic on Stack Overflow and should be asked in another venue, such as Super User. – Fogbound

I would also check the table structure for any memo fields. Those typically cause corruption. I found a recent article that related some corruption to certain builds of Windows 10 as well. Check to see that you don't use mde with Office 365, as that can cause a problem, and eliminate as many memo fields as possible. – Weiland

@CindyMeister - This is (now) a known issue that affects Access developers, some of whom (like me) have multi-user Access applications that have been working properly for ten (10) years or more. See my answer for details. – Archive

Answer 1

This is now a "known issue", documented in the Microsoft Support article

Access reports that databases are in an 'inconsistent state'

The article currently includes a workaround involving configuration changes on the file server. It also says that they

are testing a fix for this problem now, and will give further updates when we can confirm that this resolves the issue

Related articles (e.g., here and here) suggest that the errors are a combination of

1. a new File Sharing (SMB) feature, "Leasing", introduced in recent versions of Windows Server, and
2. removal of an older SMB protocol from Windows 10

Archive

Answer 2

Depending on the version of the OS and the version of Access being used the mde file may not run properly. If they have a 64-bit version of access, and the mde was compiled in a 32-bit version, it may not be recognized correctly, but a accdb file would work fine, and you could lock it out as well.

Also, if your using an accdb back end, and need to use a compiled front-end, switch to using accde front-ends instead, as those are the modern compiled versions and mde should only be used in versions of Access prior to 2007.

My only question for you is to find out if the users are running from a network location or a local folder - as running any Access DB front-end from a network location can cause issues. Have the accde file (or the front end) copied to their desktop first, then run from there.

Also, after download, you can run a file size verification to confirm the whole file was downloaded, as I have seen wifi connections lose part of the file during download, and then that causes corruption too. If possible, split the interface into multiple, smaller files, to make the download more reliable.

Weiland

Answer 3

It is also possible that this 1 client uses your app differently than you expect. Users are after all the ultimate debuggers.

Any table that has a primary key that is not an autonumber - is a candidate to be the cause in a multi user application should it be possible that multiple persons initiate a new record without entering the primary key value immediately....or even a single user that initiates a new record but does not complete the act entirely and leaves the PK field blank.

Other: although one can copy a back end file during use without affecting it - there may be some well-intended server functions that auto duplicate/copy/exchange files that may be the cause.

Instrumentality

Answer 4

This completely solved the issue for me (with Windows 10, 1709):

I replaced all ACCDB back-end files with MDB back-end files. I am using ACCDB front-end files with MDB back-end files. I have a password set on the back-end files but I do not enable file encryption. No corruption since then!

To do this, simply create a new MDB file and transfer your back-end objects to this new file. I did not implement the Microsoft workaround (disabling leasing).

Alternatively: replacing any DoCmd.RunSQL command with DoCmd.OpenQuery (and a query object) reduced the occurrence of corruption by about 80%.

Preface

Issue appeared with Win10 1803, not an issue on 1709, but without security updates, you shouldn't use 1709 any longer. Seems like you had suffered on "normal" corruption. – Dierdredieresis