MSAccess MDB corruption

Posted on 2012-08-24
Last Modified: 2013-12-23
Migrated VB6 (MS Access 2003) based application from Windows Server 2008 Enterprise to Windows Server 2008 R2.  The application is accessed via network share from Citrix XenApp.
 Since the move, we have had an almost daily corruption issue in one of the mdb files.  Does WIN2K8 R2 behave differently than WIN2K8 Enterprise in terms of handling open sessions?  The Citrix servers reboot nightly – some end-users are bad about logging out correctly and leaving sessions idle.
Is there a file monitoring tool, script or some other method to monitor the file for corruption and send an e-mail notification?
Question by:McGruber
    LVL 6

    Expert Comment

    Corruption can happen for any number of reasons and is VERY difficult to fully diagnose.

    Is your code compiled?  Have you tried a compact and repair?

    Author Comment

    It was written by an outside vendor.  The code is compiled.  The file repairs when you open it.
    LVL 6

    Expert Comment

    "The Citrix servers reboot nightly – some end-users are bad about logging out correctly and leaving sessions idle."

    This is DEFINITELY an issue.  This COULD BE the cause.  As an Access Developer, I would build something in that requires users to be kicked out prior to rebooting sessions.  If there are too many broken sessions, this could do it.

    Again, there are simply too many variables to answer this simply.
    LVL 56

    Accepted Solution

    <<The Citrix servers reboot nightly – some end-users are bad about logging out correctly and leaving sessions idle>>

     As has been said, this is most likley your main problem.  If a user is disconnected abnormally from a DB with pending writes, then it's flagged as corrupted and will repair at the next open.  Most often nothing bad will happen, but there are times when it will.

     Also note that a sucessfull repair is NOT a guarantee that the DB is solid; it may still have corruption.  

     You need to get the users trained to log out when their done and as fhlio_admin suggested, build in a idle timer, which might be done like this:

    ACC: How to Detect User Idle Time or Inactivity

      Beyond that, since the server is changed, is there any possibility of a network problem (assuming the backend is on another server)?

     Is this a VB6 app using JET as a datastore or is it an Access app?

    If the latter, where is the corruption occuring, back end or front end?   And are all the Citrix users sharing the same front end?  Is the database even split?


    Author Comment

    Network appears to be solid.
    It is a VB6 app running as an Access app.
    The database is not split so Citrix users are sharing the same db.

    Could it be oplocks?
    LVL 6

    Expert Comment

    Corruption can happen for any number of reasons and is VERY difficult to fully diagnose.

    I suppose it is possible.  I am hesitant to say it ISN'T something.  I think that it is most likely the user not canceling and the server rebooting.  If I had an Access database on my computer and kept Killing processes while I was in it, then the likelihood of corruption would escalate.  If you compound that on the number of users that you have it isn't good.

    The hardest thing to say about corruption is that we never know what the proverbial straw that broke the camel's back was.  We don't know "how close it is" to corruption.  Lastly, it is, unless there is a specific thing that is triggering it always (I have seen it happen with a lack of compiling the code and compacting and repairing), it is hard to duplicate on command.

    The answer to your question is simple, but hard to hear and hard to give.  I don't think anyone can definitively answer it without a doubt.  The best is speculation upon years of experience.
    LVL 23

    Expert Comment

    I would add a pre-reboot script that looks for disconnected sessions and logs them out.  (If you let the timers do it, they perform a reset, and not a logout).  If the users not logging out is the problem, then this should help.

    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<It is a VB6 app running as an Access app.>>

     So it's a VB6 application and the data is being stored in a JET MDB file, correct?

    <<Could it be oplocks?>>

      No, but you won't hurt anything JET wise with turning them off.  Usually a problem with OPLOCKS only shows up when users are experiencing delays in connecting to a database and not so much with corruption.  OPLOCKS allows client side caching of a file and it takes time to check and resolve OPLOCKs if their enabled when users connect and disconnect from the database.

     Also, JET already maintains it's own cache unlike other applications, so OPLOCKs are just extra overhead.

     So turning them off will help with JET, but not other applications and you might not want to leave them off depending on what the server does.

     There was a problem with SMB 2.0 a few years back when it first came out.  You might want to try forcing everything to SMB 1.0 protocol to see if that changes anything, but really, your problem is most likely the users that are not logging out.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    After several days of searching and hunting for limited documentation, I wanted to share this guide to hopefully save someone the hassle of trying to figure this out on their own. I have tested this on Xendesktop 7.1 and PS 4.5 running simultaneous…
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now