Microsoft Access corruption with linked application to mdb file.

pabrann
pabrann used Ask the Experts™
on
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!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AlexSenior Infrastructure Analyst

Commented:
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.
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.
AlexSenior Infrastructure Analyst

Commented:
A.E. Veltstra,

Give it a year, they'll be back begging to have it put back onto SQL :-)
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

pabrannPresident

Author

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.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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:
pabrannPresident

Author

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
AlexSenior Infrastructure Analyst

Commented:
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.
AlexSenior Infrastructure Analyst

Commented:
Also, this clearly hasn't been designed properly, hence all the issues.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
pabrannPresident

Author

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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
pabrannPresident

Author

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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Distinguished Expert 2018

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 LambertConsulting
Distinguished Expert 2017

Commented:
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.
pabrannPresident

Author

Commented:
Each user has their own front-end on their local computer.
pabrannPresident

Author

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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
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.
pabrannPresident

Author

Commented:
Thank you very much Jim..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial