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!!
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.
A.E. Veltstra,
Give it a year, they'll be back begging to have it put back onto SQL :-)
Give it a year, they'll be back begging to have it put back onto SQL :-)
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Also, this clearly hasn't been designed properly, hence all the issues.
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.
ASKER
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.
ASKER
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.
ASKER
Each user has their own front-end on their local computer.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much Jim..
How big is this database? Not in size but in records.