Access Database is getting corrupt again and again

Back in May 2020 there was an update ver. 16.0.12827.20160

Which broke queries on our Office 2016 retail installs.  The only way to fix this was to rollback the update. 

if you look for the pre release notes they are not available any more. 

https://docs.microsoft.com/officeupdates/release-notes-office365-proplus?Ver=16.0.12827.20160&Branch=FirstReleaseCurrent&Arch=64%2c64&XMLVer=1.6&xmlPath=http%3a%2f%2fofficecdn.microsoft.com%2fpr%2fwsus%2fofl.cab&xmlFile=O365Client\_64bit.xml&ServicePath=https%3a%2f%2fconfig.office.com%2fapi%2ffilelist%3fChannel%3dFirstReleaseCurrent&Version=16.0.12827.20160&AllLanguages=True

If I remember correctly the issue was something in the path changes. 

• This update fixes an issue in Microsoft Office where Visual Basic for Applications projects with references that are expected to be found by searching locations specified in the PATH environment variable may not be found properly at runtime, leading to VBA runtime errors.
• This update fixes an issue in Visual Basic for Applications in Microsoft Office where certain VBA projects that contain references to code libraries with DBCS characters in the library name or library path would be viewed by the Office application as corrupt on load.

I have not tried to update those systems since. I may go ahead and try an newer update to see if works since it has been a few months.

Looking at the archived release pages there has been updates to fix queries in Access. 

The issue with having many clients is that they all need to be on a working build. Then after the update to one or two you can find out if the update breaks the functionality of your database.  Then you have to roll it back. If you just update everyone's build and it breaks, you end up with a bunch more work doing rollbacks. 

https://docs.microsoft.com/en-us/officeupdates/monthly-channel-archived#version-1907-july-29

https://docs.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

https://support.microsoft.com/en-us/topic/fixes-or-workarounds-for-recent-issues-in-access-54962069-14f4-4474-823a-ff7e5974a570

If you are required to use a company VPN to connect to their app/database, is there an improvement in performance after the SQL conversion ?

Hi George,

In my experience, a SQL Server backend will perform better than the equivalent Access back-end in concurrent multi-user use once any inefficient Access functions have been optimised for SQL.

More specifically, I believe it is still not recommended to connect an Access front-end to an Access back-end over anything other than a cable LAN. If your VPN involves an Internet connection in the aforementioned configuration, then any corruption you are experiencing is just as likely to be due to the intermittent nature of Internet connections as the Windows 10 issue, in my view. Having said that, I do have one client who was running such an application over a (very expensive) corporate global WAN without issues until the users started working from home. But this is quite a light-weight application, specifically designed with this usage in mind, and it is rare for more than one user to be updating at any one time. Even so, use on domestic connections is now causing regular corruption.

SQL Server, on the other hand, is perfectly suitable for this kind of usage. Therefore, the answer to your question, if I have understood it, has to be "yes". (I just wish my client's IT department would agree to its use in the above case!)

I have been able to convert my own applications in around 100 hours, but these are systems which have "outgrown" Access. I am aware of cases where the data has simply been migrated across using the automated tools, and the application has continued to run satisfactorily, with no "conversion time" as such. Then again I have seen complex modules, developed by others, which just time-out, and in the end have to be completely re-designed.

Hi David,

You have mentioned frequently converting an Access backend database to SQL Server, SQL Server Express or SQL Azure as a solution to the ongoing, officially unresolved Access database corruption issue with Windows 10/Access 2016 or 2019. If you are required to use a company VPN to connect to their app/database, is there an improvement in performance after the SQL conversion ?

Citrix itself does not resolve the issue, unless maybe your Citrix environment virtualizes apps using an older version of Windows. From what I understand, the issue is caused by the way the Access database engine in a Windows 10 environment communicates with the backend on the file server.

In the case described, and at another site I know of, Citrix is used to run the front end on a Windows Server o/s, so the front end is no longer running on Windows 10, which works around the issue and resolves the problem. Similar to using Terminal Server or a hosted Access service.

That is a great option I have been thinking about, along with RDC'ing to a specific VM. Could also bypass our VPN altogether.

Thanks for the confirmation and encouragement, TQ.