Microsoft Access corruption with linked application to mdb file.

I have a client with an Access application. The system has a front-end linked to a back-end running compatibility mdb files. There are approximately  10 users connected to the application using various versions of Microsoft Office.  The system is experiencing frequent issues which appears to be corruption in the database. When this happens they are receiving  “Disk I/O error trying to read” and sometimes “Unrecognized database format J:\Purchasing\Inventory Control\MICBE_NA_1.0.2.mdb”  error messages. Originally it appeared that these issues occurred when a single table was being read "Item_Master". However yesterday this happened when a user clicked a bound checkbox having now connection to the Item_Master table.  One time the primary key was destroyed in their PurchaseOrders table which we were able to fix after identifying and fixing duplicate key records.

We have continually executed compact&repair procedures and also decompliles.

I know we need to upgrade the back-end to SQL Server but my client is reluctant to do it because they like to connect directly to tables and queries for data entry and maintenance.

If they upgrade to the newer "accdb", is that likely to solve the corruption issue? Are there any procedures we should try to see if we can identify the cause and solution.

Thank you very much!!
pabrannPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex GreenProject Systems EngineerCommented:
What they like and what the best course of action is are two completely different things, given that the database is already struggling, a backend SQL Database seems like the right way to go. Essentially, if they don't, they will continue to suffer the consquences.

How big is this database? Not in size but in records.
A.E. VeltstraCommented:
No, migrating to an AccDb format won't fix the problem. Access databases are not intended fo multi-user access. The problem is that multi-user access.
 
If you wish to keep on using the same Access front-end, you can copy the table structure and data to MSSQL and import the tables into the Access front-end.
 
I implemented that architecture nearly 2 decades ago for a client who continued to use it until recently... when they came back to get the tables migrated back into Access due to licensing cost.
Alex GreenProject Systems EngineerCommented:
A.E. Veltstra,

Give it a year, they'll be back begging to have it put back onto SQL :-)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pabrannPresidentAuthor Commented:
Thanks for getting to me so quickly. The largest table is their Inventory Transactions table with 61,0000 records. Their PurchaseOrders table has about 35,000 records. I'm on the phone with my client again now and he said there are only about 6 front-end users. He also mentioned that he has 3 users connecting to the Item_Master table table with Excel using Oledb12. I'll be back with more info as the database is corrupted again now.
Dale FyeOwner, Developing Solutions LLCCommented:
Well, I would strongly disagree with A.E. Veltstra, Access database  applications, when designed properly work just fine with multiple users, and I've been doing this for over 25 years.

Microsoft recently announced that a Windows update is responsible for some of this. The workaround is to disable leasing on the server housing the backend database.  See this article:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pabrannPresidentAuthor Commented:
These are the connection strings to the Excel connection to the back end database. These connections are relatively new to this database so I'm wondering if this could be part of the problem.

(From Item Master)

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\Purchasing\Inventory Control\MICBE_NA_1.0.2.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False



(From Long Lead Time)

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\Purchasing\Inventory Control\MICBE_NA_1.0.2.mdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False
Alex GreenProject Systems EngineerCommented:
https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

Are you breaching any of the limits?

In all honesty, I'd still move it over to SQL, if they happen to have an SQL server already, dropping in another instance is easy enough.
Alex GreenProject Systems EngineerCommented:
Also, this clearly hasn't been designed properly, hence all the issues.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You have some type of networking/OS issue going on.

 I fully agree with Dale; Access apps when designed properly work just fine with multiple users.   In general, you can get up to 30-40 users with records in the hundreds of thousands before you have issues.   With six users and the table sizes you mention, you absolutely should not be having issues.

 Note that in terms of users, it's not that you can't have more, but that it's hard to keep that many clients stable.   With Access, all the DB processing is carried out on the client side....there is no central process to roll things back when something goes wrong.

 Also when you get to that point, you start looking for more advanced features, like on-line backups and roll forward restores.

 But the network needs to be rock solid.   Access will complain long before other apps because the clients are maintaining a db cache on a millisecond basis.

 In general JET/ACE (the database engine) wants at a minimum 40mb/sec in speed, and no more than 30 milliseconds in latency with the latency being the critical factor.  

 So a couple other points besides the recent problem with Windows 10 that Dale mentioned:

1. Do you have any of the clients on wireless connections?   If so, switch them to a hard connection.  Wireless can have drop outs and often will yield latency >50ms for a long stretch.

2. Are you running a virtualized server?   If so, be aware that vmware has issues with the settings on the vmxnet3 that will cause network dropouts.

3. Anti-virus / behavior monitoring software can cause issues.  Make sure you have exclusions in place for .Accdb's and .mdb's both on client and server.

Jim.
pabrannPresidentAuthor Commented:
Thanks Jim. I talked to the IT specialist on my client site and he said that we are good on your question 1 and 2 but he can't verify that we are good on question 3.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, could we have some more background on this?

Did this start recently, or has it been on-going since the app was put in place?     Any changes in servers or clients recently?  What exactly happens when you get a corruption?  Are users getting errors at any point?  Are you running Win 10, or a mix of OS's?  etc.

Just a general overview of the problem and what the environment is and any recent changes.

Jim.
pabrannPresidentAuthor Commented:
This application has been live for many years and the persistent corruption issue started a couple weeks ago.
On November 15 I received an email (screen shot attached).
FirstNotice.PNG
The most recent change is the connectivity from Excel that I referenced above, however I think that started a couple months ago. I think they are only using Windows 10 but I will verify.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This application has been live for many years and the persistent corruption issue started a couple weeks ago.

You need to disable leasing as Dale said in pointing to this article:

https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d?ui=en-US&rs=en-US&ad=US

I'm sure it's the Win 10 bug based on what you've said.  IT probably did some updates and picked up the latest release.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, this started with the May 2018 build 1803.   If your on anything higher then that, then you have the bug.  Microsoft has not released a fix for this as yet.

At some point they will, at which point you will want to enable leasing again.  

Jim.
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
As others have said, this could likely be due to a Windows Bug that surfaced with a recent update

Access – Bug – Database is in an Unrecognized Format

The question here is, was the system previously stable?  If so, and the issue is relatively new, then it is most probably related to the above,

We are all waiting for MS to actually fix the issue, but last I heard they haven't even managed to replicate it, so I doubt a fix is forthcoming, so the registry hack is the only available workaround beside rolling back to responsible update.


I also wanted to address A.E. Veltstra's statement:
...Access databases are not intended for multi-user access...
This is simply not true, so don't worry about it in the least.  Like many other people here, I've been developing multi-user Access databases for 2 decades now without issue.  It all comes down to knowing what you are doing.  Every multi-user database must be split and each user given their own copy of the FE to run locally.... If ever you wish to learn more regarding properly setting up multi-user Access databases see Setting Up an MS Access Database
Fabrice LambertConsultingCommented:
Question:
Are all your users running the same front-end ?
If yes, this is an error, each user should have his own local copy, for obvious reasons:
- Performances are better (less network traffic).
- If one user crash his front-end, no one else is impacted.
pabrannPresidentAuthor Commented:
Each user has their own front-end on their local computer.
pabrannPresidentAuthor Commented:
Dale and Jim, thank you so much for getting me and my client through this. My client finally had one full day of error free use of their inventory control system on Friday. It's hard for me to believe that Microsoft, the largest company in the world, could let this happen. I even encountered this error on my development machine yesterday. I'm sure that many users will give up on Access because of this and Microsoft will probably further ignore maintaining it in the future.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Glad to hear things are back to normal.   keep in mind that when Microsoft announces a fix, you'll want to re-enable leasing.

 I'll make that announcement as a post on the main feed on EE when that happens.


It's hard for me to believe that Microsoft, the largest company in the world, could let this happen.

 Unfortunately, it is the result of having a machine (a PC) that can do so many different things.

  The bug is actually in Windows and it effects more than just Access.   Any application that is multi-user with a db engine on each client would have the same problems (i.e. Quickbooks).

  Microsoft has been bitten by this more than once already, so you would think they would do a little more diligence here, but my guess is that it was something very subtle and totally unforeseen.  What's driving this is the demand for performance for the majority, who work with single user files.   Multi-user access to files represents a very small percentage of the applications that run on Windows.

  Of course we as developers understand more than most how tremendously complex things have gotten and given that, how easy it is for things to get broken.

 I know my code is never 100% error free and I've created some outright messes in my time.

Jim.
pabrannPresidentAuthor Commented:
Thank you very much Jim..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.