Access Database is getting corrupt again and again

Gina Whipp:  I've been advocating for individual front-ends for years and only some of my clients have the back-end set to compact on close.  I was speculating whether it may have been one or the other of those issues.  I need to go through my list of people who have had problems to see if there is a commonality between the ones who have not had issues and those who have and the first place I'm going to look is at the back-end.  I have fifty-some different clients so it's going to take a while to spot check.

Yes, individual front-ends ensure that when one user goes down they don't all go down.  Until Microsoft introduced this bug, that is.

SFBAZ,

The ONLY way when having multiple Users is for them to each have their own Frontend.  A link to a single Frontend is just asking for corruption.

I would NEVER set the Backend to close whether one User or multiple.  However, having it do so with multiple Users is again, just asking for corruption.  That said, if your Backend grows to a point of needing compacting constantly there is most likely another issue that is causing such bloat and that is what you should be looking at.

Has anyone considered the effects of having users work from their own copies of the front-end database vs. a shortcut to a single copy of the front-end database on the server?  I have always recommended that users each have their own copies of the front-end but now I'm wondering if some of the problems could be related to that and maybe I should have them go back to a shortcut.  

Also, could there be an issue with having the back-end set to compact on close with multi-users?  I'm grasping at straws here...

Shane,

Thanks for updating the post. Can you please consider this alternative approach which is based on the disable leasing workaround, but limiting it to one share instead of the entire server? 

1. Create a folder on the server and place the uncorrupted database in that folder.
2. Share the folder on the server and give it a share name (e.g., MyDB)
3. Start Powershell as Admin on the server.
4. Set the leasing mode for the new share to None with the following commands:

    set-smbshare MyDB -LeasingMode None

    get-smbshare MyDB | Format-List -Property *    (make sure Leasing Mode is set to None)

5. Restart the server or do NET STOP SERVER then NET START SERVER.
6. Make sure ALL the client PCs open the database file or point to it in a front-end database using the UNC path for the new share name. If they don't use the right share name, disable leasing will still be on.

Note: To turn leasing back on if needed (in case of problems):

    set-smbshare MyDB -LeasingMode Full     (then restart the server or do NET STOP SERVER then NET START SERVER)

If this approach does essentially the same thing to disable leasing for the database files on that share, it will isolate the change to that share. Thus no bad side effects for other functions on the server. Server administrators will be much more likely to implement the change.

Has anyone else tried this or have any feedback? I have posted it here before and suggested it to some of my customers, but I also made the client change I mentioned earlier, so I cannot say conclusively if this will have the same results as the suggested workaround by Microsoft. I wanted to mention this in case it will help those who can't disable leasing on the entire server.

@DLyons2,

The issue you are seeing with update queries is a new problem, introduced with the November 12 Office update, and is unrelated to the ongoing issue being discussed in this thread.

Fortunately a fix is available for this issue in most versions of Office (and it will be soon for the remaining versions).

See this link for details: https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

Shane