How can I investigate and resolve an (apparent) Access database corruption?

Question

I have an Microsoft Access 2010 database application with split front end and backend which has started to behave oddly, and I've exhausted all the options I know for investigating and resolving the problem.

32-bit Access 2010 running on Windows 8.1... I have both Access 2010 and Access 2013 installed, but the problem also manifests itself on a Windows 8.1 system with a completely fresh install of Access 2010 and no Office 2013 present. The issue also exists if the application is run using Access 2010 Runtime. The front-end is running on my hard disk, not in a Dropbox or similar environment. The back-end is in Dropbox.

There are a couple of third-party elements in the application -- references are as shown -- example 1 on the system with both Access 2010 and 2013 present, example 2 on the system with just Access 2010 present.

There hasn't been a software update to the Treeview control since December 2013. I've checked that the versions of the third-party controls I'm using are compatible with Windows 8.1.

Symptoms:

The application (an unreleased development version) initially works perfectly, but if closed and reopened, one specific operation (right-click on a third party treeview ActiveX control on the main form) misbehaves -- the right-click event is triggered multiple times instead of just once (the number of times is unpredictable). There are two treeviews on the main form with identical settings (populated dynamically with different data sets). One treeview behaves, one doesn't. Even if I remove all code from the right-click event, it fires twice.

This main form configuration and code hasn't been changed in over one year, not has the treeview config or code. I don't use Compact on Close. The application isn't logging any errors.

What I've tried:

If I restore a previous version of the application, it works... and when reopened, doesn't work. (I've tried this with several previous versions of the database.)

I've tried importing a copy of the main form from an old working version of the database -- same problem.

I've tried deleting the malfunctioning treeview and creating a new one (copying the one that is working) -- same problem.

I've tried creating a new blank database and importing all the objects from the old one. Once I've restored the references manually, the same problem.

I've reviewed all the possibilities mentioned in Can't eliminate Access corruption -- one commonality I have with this question is that I've (last three months) started using the VBA Implements keyword, but I hadn't made any changes to this code immediately before the problem showed up, and neither the main form nor the treeview control utilise it.

I've emailed the support team for the treeview control, but they haven't anything to suggest that I haven't already tried.

I've repaired the installation of both Access 2010 and Office 365 in case the references were somehow messed up.

I've un-installed Office 365 and Access 2010, rebooted the machine and reinstalled Access 2010. The references are all Office 14 references and the problem still exists (in a compiled accde). As soon as I reinstall Office 365, the references become mixed 14 and 15. (This is also true for the working version which is two years old).

What I haven't tried yet:

Rolling back a two months' worth of Windows updates to see if it's a Windows issue (this system has only been in use since early September, so this wouldn't be hugely onerous to try).

Rolling back to a version of the app from December 2012 (the last production release) which doesn't seem to have suffered the corruption and manually reapply almost two years worth of development changes. This would be a mega undertaking....

Are there any other options for investigation or resolution that I can try?

Edited to add: What finally worked

I created a new empty database, imported everything from the old database except the main form, which I recreated from scratch to look identical and have the same code as the old one... And the problem has gone away. It not very satisfactory as a resolution, but it seems to confirm that there was a corruption somewhere.

Workhouse

Your posted screen shot ALREADY shows messed up references. You cannot possible be doing all this troubleshooting and not notice in your references that you have office 14 and 15 references. I would FIRST fix this issue. So in control panel programs and features, you need to select the office 14 install. Then do a repair. (don’t’ launch or use office 15). It really is “beyond” competence that you doing tons of troubleshooting and yet not even bothering to read or point out or address this issue of mixed up 14 and 15 office references that can be clearly seen in your screen shot. – Pistoia

@AlbertD.Kallal Thanks for the kind words. The references did look odd, which is why I ran the app (no changes) on the system which has never had Office 2013 installed. Only the Office 14 references exist/show up -- and the problem still exists. – Workhouse

@AlbertD.Kallal I've edited the question to add an image of the references on the second system. – Workhouse

I would try to isolate components. First remove dropbox from the equation - place the backend on YOUR drive and relink the tables. You said you tried a previous version and it doesn't work, so I'm inclined to focus on anything external - hardware/software/dropbox/ActiveX controls/ etc. I think you only mention that you have version 7.0 of SftTree/OCX, but have you looked at the Hot Fixes and point-releases for thet control (see softelvdm.com/Support/ActiveXControls/…) - i.e. Windows 8.1 support is in 7.08 – Alliance

@WayneG.Dunn Thanks for the suggestions. I've tried moving the backend away from Dropbox -- no change. SftTree/OCX is at release 7.08 and there are no hotfixes available. – Workhouse

It is not possible to use Dropbox for the back end data file if front ends are linked on the client side computer to that back end. DropBox is NOT windows networking and windows networking is required. However the issue of DropBox is SEPARATE from the other issues here. The simple matter is you want to ensure that your references are correct, and then you best like compile your app to a accDE and use that for testing on other machines. The source of broken references likely point to sftTree not being installed or registered on those target computers and that will case a reference breakage – Pistoia

@AlbertD.Kallal Yes, let's set Dropbox aside as it makes no difference to the issue at hand. As per my edit (and a comment which somebody deleted) I've followed your advice to repair both the Access 2010 and Office 365 (2013) installations but the references remain unchanged -- I wonder if Microsoft have made a change as part of allowing older versions of Office to run alongside Office 365? I'd be grateful if you could let me know if your default references look the same in a similar environment? If I create a new blank database in Access 2010 I get the same mix of 15 and 14 references. – Workhouse

@AlbertD.Kallal I have tested the app as a compiled adddr on the test machine and the issue still exists although the sfttree control is installed and registered (and one instance of it on the same form works perfectly). – Workhouse

My advice was specific and I stated to repair office 2010 and SPECIFIC said to NOT launch or run 2013. And my advice was NOT to try an accDR, but compile to an accDE and try that on the machines. So you have to do all that testing over again. At no time did I state or suggest you mess with 2013. You are using 2010, and compiling to a 2010 accDE. You need to ensure your references to that 2010 application are 2010 ONLY and ensure that the application compiles. You REALLY (but really really) do not want to mess with two versions of Access here. – Pistoia

@AlbertD.Kallal I cannot avoid having 2 versions of Access on the main machine -- I require Office 2013 for other work I do. I do not run Access 2013 but cannot remove it. However on the test machine I only have Access 2010 (a fresh installation) and the references there are 2010 only. My accdr file is a renamed accde file, so there is no functional difference in the testing I have done. – Workhouse

@AlbertD.Kallal I'd like to add that I'm grateful you're still taking time to help on this. I'm trying to follow your suggestions as I understand them within the constraints of the environment I have (and the environment my users will have -- some of them will almost certainly run the app on systems that have multiple versions of Office present. I supply the Access 2010 runtime as part of my installation package but can't predict what else they will already have ) – Workhouse

@AlbertD.Kallal I have un-installed Office 365 and Access 2010, rebooted the machine and reinstalled Access 2010. The references are all Office 14 references and the problem still exists (in a compiled accde). As soon as I reinstall Office 365, the references become mixed 14 and 15. (This is also true for the working version which is two years old). – Workhouse

AT NO TIME did I suggest to remove 2013. At NO TIME did I state that you have to remove 2010 either. However I MOST CERTAINLY stated to run a repair on 2010 and DO NOT launch or use 2013 (I VERY carefully stated things this way as to avoid you having to un-install and re-install). You having much difficulty following advice given here. The simple issue is the LAST version you repair (or in fact install) will be the version that has “preference” for registered libraries. The instant you launch or install or repair or use 2013 is the instant you create a mess and have those mixed up references. – Pistoia

So now take that restored app (the one that will only run one time), and check the references in VBA. YOU MUST NOT allow ANY VBA CODE to run on startup when you check those references. That means holding down shift key and not allowing ANY of your startup code to run. Now having used shift-bypass, then check all of the VBA references, and make sure they are all 14. – Pistoia

@AlbertD.Kallal As I've already (1) proven the problem exists on a 2010 system that has never had 2013 installed and (2) repaired Office 2010, checked the references (without running code on startup -- that's pretty basic), proven the problem still existed with unmixed references, repaired Office 365, checked the references (now mixed) and the problem (still there), the only option left was (3) to uninstall all office on the main system and rebuild - it only took 30 minutes total to do the uninstall/install/check references/test process,so not much time wasted. – Workhouse

I'm confident that point (1) illustrates the problem isn't the references and points (2) and (3) reinforce that conclusion. Yes, they're odd, and I haven't been able to find anything that confirms (or otherwise) that they're normal for Office 2010 and 2013 coexisting but the problem manifests itself irrespective of whether I have pure 2010 references or mixed 2010/2013 references. – Workhouse

@ColeValleyGirl, great job with this post and congratulations on solving the issue. I would urge you to take your final update, use it as an answer, then Accept the answer (which you may have to wait 1-3 days for that step). This will help on all sides. – Chordophone

Answer 1

Usually the Access databases corruptions affect the VBA modules, less likely the table data. So hopefully you should be able to copy the data to a blank database, get the VBA code from a older backup (since the last source code update) and merging the two together. It should work!

Antiproton

can easily go in comment – Buttercup

You'll see from the bottom of the Q that was essentially what I did. – Workhouse

Answer 2

One of the best ways to remove corruption in an Access database is to save the forms and reports to text using the undocumented SaveAsText function, delete the form and report objects, close the database, use the undocumented /decompile switch to decompile the database, compact/repair the database, then re-import all the objects using the undocumented LoadFromText function.

Dunson

Answer 3

It won't stay fixed unless you disable updates. And you can't disable updates because you will be compromising security.

Haimes

Sad that someone is sufficiently uninformed to vote this down. – Haimes