Access Database is getting corrupt again and again

I am the owner of a company that sells a software product based on Microsoft Access. We have been in business for 20 years and the current state of the support or attention to these problems from Microsoft is so alarming I am truly shocked. You just have to read some of these posts. The lost time and money, for developers and customers, if it could be added up would be astounding. On the other hand, I am glad Shane is at least listening and contributing which means some others at Microsoft are also. I just wish it translated into action and solutions in a timely manner.

I went through a few months of nightmares last year with the database corruption issue. Disable Leasing on the server if you can (not needed on clients).  Consider creating a separate share for the backend database and disable leasing on that share only (via Powershell) which will minimize the impact to the rest of the server. I have not been able to test that myself, but it makes a lot of sense to me. Can Microsoft at least tell us if this should work? If so, it isolates the change to just one share not the whole server.

Getting IT people to disable leasing on their server is a bit challenging and apparently has some unwanted side efftects. The ONLY thing I think that has saved my business so far is the change that someone suggested early on with this problem (look back through the thread). And that is the registry change below to EVERY Windows 10 PC that uses the back end database.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters]

"FileInfoCacheLifetime"=dword:00000000

"FileNotFoundCacheLifetime"=dword:00000000

"DirectoryCacheLifetime"=dword:00000000

I have incorporated this into our setup program that installs our program. I rarely get inconsistent database problems. If you do, you need to repair the database, not just make this change. Up until implementing this change I thought I was going to go out of business. Can Microsoft please tell us if this is a reasonable work around and endorse it?

If people are sayng that this change no longer works, I have yet to experience that (and hope I never will). Anyway, that is my two cents. Especially for those that are still dealing with this on a daily basis.

Mark Mauger

Quantum Software Solutions Inc.

I too rely on MS Access for my livelihood, with a number of clients in diverse industries. It seems that I am fortunate that all clients experiencing the issue so far have been able to apply the official MS work around and this has resolved the issue. I am not aware that the hack has caused significant adverse side effects, but perhaps the IT staff have resolved these without my involvement.

jondenney1, another alternative work around you might like to consider is to move the back end to a NAS device. I think one person has reported success with this, but from my understanding it seems pretty much guaranteed to resolve this corrupt database issue. A couple of my clients have done this in the past; the cheaper boxes do not perform as well as Windows Server, but it's a much easier solution than upgrading to SQL Server in most cases.

Having said that, upgrading to SQL Server is the ideal way to go, with numerous advantages over an Access back end.

David Nye

www.dnye.co.uk

...upgrading to SQL Server is the ideal way to go, with numerous advantages over an Access back end.

I've read this opinion and general advise many times. Sorry, but sometimes I feel obliged to add:

... and potentially numerous disadvantages.

There are many different situations in respect to clients, applications, distributions etc. especially in the Access business area. As consultant and developer I repeatedly have experienced how the change to SQL Server (mostly demanded by IT as principal not by exigency) has complicated if not deteriorated things for all parties.

Many of my clients are amateur Access developers (often at a rel. high level) who create and maintain databases for their needs or departments. SQL Server makes them much more dependent on the IT departement for which in turn they are unimportant. They are usually not part of IT and not integrated properly. Now they often get additional delays and friction losses with technical and organizational problems (wrong ODBC drivers, permissions etc.).

The independence (or being under the radar) with Access-only solutions isn't always and from every angle a problem but very often a big asset of the tool and for the users/developers.

Another wide spread and important fact is the lack of knowhow:

SQL Server demands an additional rel. high level of expertise that Access people often don't have or only partially, i.e. have to acquire themselves in a rough and ready way without a budget or training. Thereby with SQL Server they may get more problems and errors with performance, data types, drivers, backups etc. than what they get as advantages. I'm not talking about the great theoretical possibilites but about the real use I've often seen.

Even if you only deliver applications to clients it doesn't become necessarily easier to install, distribute, run and maintain them with SQL Server.

It very much depends on the possibilities you get to act and intervene and on the partners you have at the client.

I have migrated applications to SQL Server and do development work and training for clients who use SQL Server. It can be a really good thing. However, I don't subscribe to the general and "ideal" advise to migrate but to only do it if there is a real need and advantage, or let's say, to always consider the pros and cons as carefully as possible before you migrate.

Thanks Karl, you are absolutely right. I was referring to certain scenarios only, and should have said so.