R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.
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
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!!
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!!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
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:
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:
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.OLE
(From Long Lead Time)
Provider=Microsoft.ACE.OLE
(From Item Master)
Provider=Microsoft.ACE.OLE
(From Long Lead Time)
Provider=Microsoft.ACE.OLE
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.
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.
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.
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.
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.
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.
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.
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).
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.
On November 15 I received an email (screen shot attached).
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.
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.
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.
At some point they will, at which point you will want to enable leasing again.
Jim.
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 – 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
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.
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.
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial