Access Database is getting corrupt again and again

...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.

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

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.

Sorry but we have paid for MS Access, we have developed everything in MS Acces and we want to continue using this product, that used to be a revolution when it was commercialy deployed and now it is a deatroyed by the company that has created it. 

I am a pro developer in MS Access environment for about 15 years. And we all know that we were satisfied up to the version of 2003. This is why most of the devs are still using it. Was faster, more dev friendly environment and even more reliable. 

Now we experience bugs and corruption, because we did something wrong. And it is wrong to still stay with MS, that is not supporting our business and our customers. 

It has been 1.5 years with this bug. If you cannot reproduce the error, then for God's sake make a phone call or send an email to a couple of these people here in this thread that are having the problem. Get out of the cage and give solutions to people around that still believe in you. 

And please develop access again. I feel that you believe it is somehow an"internal competitor" to other products of ms. So my opinion is that you are doing this on purpose. 

My suggestion to all you around that you are hitted by this kind od MS practices. Instead of moving forward to another ms product for resolving the issue, I suggest to move away from MS. The time that you will need for migrating to another ms product, would be wiser to spend it to other sophisticated products that are out there. 

PS: Why there arent such bugs in SQL server? Because they pay more attention to that kimd of product and left access to suffer...