none
Back-end DBs keep becoming corrupt RRS feed

  • Question

  • I have only seen Access file corruption maybe two or three times in all the 20+ years I have been developing and deploying Access applications. All my apps include a front end application & back end data file. About a month ago, one of my clients had a backend DB go corrupt. I repaired it, and they were off and running.

    But then about three days ago, they started having it happen multiple times per day. I repaired successfully each time and began investigating (something that is almost impossible with Access corruption). Oddly enough, the users that are already in there, even after getting the "unrecognized format" message, will be able to continue working (although I am not certain whether they can do any updates or not). When they close out and try to reconnect, they get the same message. When I open the back end directly, I get the same message but can simply run a repair.

    And then it happened to a different back-end file on the same server, but this back-end has a different front-end application. I think that eliminates the particular front or back end application as a suspect. (Caveat: I wrote both of them, so it is always possible that there is some design deficiency in both, but why would it pop up in both 11 MB & 52 MB back-end files on the same day).

    To eliminate data connection/flow problems as suspects, I went into the office on the weekend, shut down the entire network, unplugged most of the network cables, shut down all the computers, ran Windows updates on all workstations, and brought everything back up again.

    And today, the first day for them after I did that, it is back to becoming corrupt, now every few minutes and even with just two users connected. I had the users, one at a time, switch to running the front-end application within an RD session on a different computer that was not running the app when it went corrupt. That made no difference, which eliminates either of those two computers--the only two connected the last time the file went corrupt. It went corrupt even when on Windows 10 with Office 2013 runtime. (I may be able to test upgrading Access runtime to 2016, assuming that none of the computers are Click-To-Run versions of Office). All computers run the front-end applications as .accdr (runtime) files.

    But besides perhaps tinkering with the Access runtime versions, it seems that leaves network equipment and a Windows 2012 R2 host server storing the back ends but that is showing no other signs of any problems with any other files.

    Am I missing something here, or do I just have to start replacing network equipment to see if that fixes it? Oh, how I wish I had written the whole thing using a SQL back end 15 year ago. Easy to say now. But that is not so simple to do now.

    Monday, September 23, 2019 6:15 PM

Answers

All replies

  • Give this a try even though "Compact and Repair" supposedly does the same thing.

    Try creating a new blank Access database "Database.accdb" using your latest version of Access.

    Import all your back end tables into the new database. 

    Rename your old back end database by adding OLD somewhere in the name.

    Rename your new database using the same name as the old one without the OLD.

    Put the new database in the same folder the old one is in. 


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Monday, September 23, 2019 6:44 PM
  • Well, I am now trying the "official" Microsoft workaround here:

    https://www.devhut.net/2018/06/13/access-bug-database-is-in-an-unrecognized-format/

    ...which ultimately points here for a registry hack on the server storing the back end:

    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

    We shall see:

    1. If this solves the problem, and

    2. What other chaos this may introduce into my network--now or later:(

    Monday, September 23, 2019 6:55 PM
  • Welcome to Windows 10 and Office365, continuous improvement and continuous bugs!

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, September 23, 2019 7:08 PM
  • Brian -

    This sounds to me like a disk drive going down in flames.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, September 24, 2019 6:32 PM
  • Peter: you should read the response above where I found the MS bug--originally dug out and pressed into Microsoft's face by Daniel. Tinkering with the server-side lease settings did, indeed, solve the problem immediately.
    Tuesday, September 24, 2019 6:49 PM
  • No, sadly not.  It is a Windows 10 bug which is now more than a year old and Microsoft still has not fixed!  It doesn't cause any real damage to the database, but makes it unusable until you do a Compact and Repair.  The problem is the issue occurs over and over and over.  Some days it can happen a dozen time, or more making Access simply unusable.

    At least there is a Reg Hack fix by changing the server's networking properties, but it in turn can have other implications on overall networking performance.

    Just another headache to get around.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, September 24, 2019 6:50 PM
  • This did solve the problem. Not that I like the idea of having to change the file lease settings on the server. But at least I can get some other work done now instead of spending all day repair corrupted back-end databases.

    I hope MS comes up with a better answer. But for now, the solution, even though it is clearly a workaround, is here: 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.

    • Marked as answer by Brian D. Hart Tuesday, September 24, 2019 6:51 PM
    Tuesday, September 24, 2019 6:51 PM
  • I don’t understand what the fix IS. We used to call this stuff POTM (Phase of the Moon).

    The last big update of windows 10 on my machine was a replacement of the whole OS, as far as I know. How can Microsoft fix a thing if the context changed? Changes? Where are we headed …


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, September 25, 2019 12:41 AM
  • The problem was never with the Access application; it was a problem with how the file server housing the shared Access data file manages file access among multiple users.

    Technically, the problem relates to much more than just Access databases, although I manage several Windows-based networks that all have Windows 2012 R2 servers acting as file servers and have seen this problem only in its manifestation corrupting Access database files.

    But whatever "file leasing" is, disabling it solved this problem for me immediately.

    For the record, although I have Access databases in use on many discrete but similar networks, I never saw this problem until once sometime a few weeks ago, when I thought (and hoped!) it was an anomaly. Then, a few days ago, that same client began calling multiple times per day needing to have the same Access database repaired. Then it happened to a second database on the same network. That was when I knew it was something in the system.

    For a few days, I cast about with Access runtime versions, resetting all network components (based on the old suspicions that one culprit could be incomplete writes across faulty network connections), etc, but all to no avail.

    The problem was that I was search by "corruption". As soon as I saw a message specific to "disk I/O", I followed a MS Access forum thread over to Daniel's thread over on DevHut, and as soon as I saw it, I figured I must have the answer. https://www.devhut.net/2018/06/13/access-bug-database-is-in-an-unrecognized-format/

    Even though that thread originated in June 2018, the problem never arose for me until the last few weeks. So I am not sure what triggered it; perhaps it was some long-"overdue" server update that I applied.

    Wednesday, September 25, 2019 12:55 AM
  • Hi Brian,

    Had the same early this year. Had to move all shares into Win7. Win10 had full of troubles.

    Found out Win10 updates was the cause, not any hardware settings or any codes in Access or bugs.

    Win 10 Share - failed. 

    Win7 Share - no problems.

    Win7 Share - Win10 User - so far no issues as long as they are using it. Win10 User will disconnect itself but no major issues to Back-end database.

    Wednesday, September 25, 2019 8:34 AM
  • Most of the networks I manager are more substantial an have their own domain controllers and Win2012R2 file servers, so in my case it is Win2012R2 file server where I had to make the registry hack.

    Wednesday, September 25, 2019 12:48 PM
  • thanks for sharing.  this is indeed disturbing.  my thoughts:

    a. hard to understand how it would affect a non split Access file without also affecting all excel, Word files the same way....which would make it a super big deal for Microsoft

    b. if it affects multi user split Access applications - - one would think it would affect other multi user Windows applications besides Access i.e. SQL Server, IIS, etc

    …..so perhaps it is not just the OS - - but must be the OS in conjunction with the specific Access Jet engine design if it only is affecting Access... 

    c. one would think that turning off Leasing (whatever that is...) might have impact on other processes that should be highlighted before doing that to solve an Access issue....


    Friday, September 27, 2019 2:45 PM
  • Am I safe to assume that leaving the applications open when the switch was restarted as being the likely reason for the BE corruption?

     Hard to say.   Corruption occurs during write operations from a normal use standpoint.  

     Of course corruption can be caused by many things, but the apps should have been closed.

    Part of me thinks that it may not be related due to the corruption occurring 4 hours after it was back up...?

     Would depend if a spot in the database was hit that wasn't written correctly, but I would side with you and guess no.

    There used to be JETCOMP.EXE, but I'm not sure if it would work on an .addcb (were talking JET 4.0 - last version before ACE in 2007).

     You could try it on a backup copy, but my guess would be no.
    Tuesday, October 22, 2019 10:13 AM
  • Nope. It had nothing to do with the switch or any other network component, and it is not hard to say what happened, since I found and posted the answer. Look at the post here that was already marked as the answer. This is a bug in the way Windows manages file leasing.

    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.

    Tuesday, October 22, 2019 2:09 PM