Can't eliminate Access corruption

Question

My firm's Access database has been having some serious problems recently. The errors we're getting seem like they indicate corruption -- here are the most common:

• Error accessing file. Network connection may have been lost.
• There was an error compiling this function.
• No error, Access just crashes completely.

I've noticed that these errors only happen with a compiled database. If I decompile it, it works fine. If I take an uncompiled database and compile it, it works fine -- until the next time I try to open it. It appears that compiling the database into a .ACCDE file solves the problem, which is what I've been doing, but one person has reported that the issue returned for her, which has me very nervous.

I've tried exporting all of the objects in the database to text, starting with a brand new database, and importing them all again, but that doesn't solve the problem. Once I import all of the objects into the clean database, the problem comes back.

One last point that seems be related, but I don't understand how. The problem started right around the time that I added some class modules to the database. These class modules use the VBA Implements keyword, in an effort to clean up my code by introducing some polymorphism. I don't know why this would cause the problem, but the timing seems to indicate a relationship.

I've been searching for an explanation, but haven't found one yet. Does anyone have any suggestions?

EDIT: The database includes a few references in addition to the standard ones:

• Microsoft ActiveX Data Objects 2.8
• Microsoft Office 12.0
• Microsoft Scripting Runtime
• Microsoft VBScript Regular Expressions 5.5

Mavismavra

@HansUp Yes, I do. Good point -- I'll edit the post. – Mavismavra

@HansUp everyone uses Access 2007. Users get their own copy of the db from the network, and the data is in linked tables pointed at SQL Server. – Mavismavra

I assume the db is split, the front end is copied to every client and the back end is on a shared folder ? – Jareb

@Jareb that's right. – Mavismavra

@HansUp missing references aren't the issue. I can replicate the problem on my own computer, where references definitely aren't a problem. – Mavismavra

Does it become corrupted simply through opening it then closing it or only when it is 'used' (forms/queries/macros etc)? If the former then it might suggest something to do with the things that happen when Access is opened -> VBA compiling and so on. Do you have any code that runs on open? (connection code or whatever)? – Munroe

Answer 1

Some of the things I do and use when debugging Access:

• Test my app in a number of VM. You can use HyperV on Win8, VMWare or VirtualBox to set up various controlled test environments, like testing on WinXP, Win7, Win8, 32bit or 64 bits, just anything that matches the range of OS and bitness of your users.

• I use vbWatchDog, a clever utility that only adds a few classes to your application (no external dependency) and allows you to trap errors at high level, and show you exactly where they happen. This is invaluable to catch and record strange errors, especially in the field.

• If the issue appears isolated to one or a few users only, I would try to find out what is special about their config. If nothing seems out of place, I would completely unsintall all Office component and re-install it after scrubbing the registry for dangling keys and removing all traces of folders from the old install.

• If your users do not need a complete version of Access, just use the free Access Runtime on their machine.

• Make sure that you are using consistent versions of Access throughout: if you are using Access 2007, make sure your dev machine is also using that version and that all other users are also only using that version and that no components from Access 2010/2013 are present.

• Try to ascertain if the crash is always happening around the same user-actions. I use a simple log file that I write to when a debugging flag is set. The log file is a simple text file that I open/write to/close everytime I log something (I don't keep it open to make sure the data is flushed to the file, otherwise when Access crashes, you may only have old data in the log file as the new one may still be in the buffer). Things I log are, for instance, sensitive function entry/exit, SQL queries that I execute from code, form open/close, etc.

• As a generality, make sure your app compiles without issue (I mean when doing Debug > Compile from the IDE). Any issue at this stage must be solved.

• Make absolutely sure you close all open recordsets, preferrably followed by setting their variables to Nothing. VBA is not as sensitive as it used to be about dangling references, but I found it good practice, especially when you cannot be absolutely sure that these references will be freed (especially when doing stuff at Module-level or Class-level for instance, where the scope may be longer-lived than expected).

• Similarly, make sure you properly destroy any COM object you create in your classes (and subs/functions. The Class_Terminate destructor must explicitly clean up all. This is also valid when closing forms if you created COM objects (you mentioned using ADOX, scripting objects and regex). In general keeping track of created objects is paramount: make sure you explicitly free all your objects by resetting them (for instance using RemoveAll on a dictionary, then assigning their reference to Nothing.

• Do not over-use On Error Resume or On Error Goto. I almost never use these except when absolutely necessary to recover from otherwise undetectable errors. Using these error trapping constructs can hide a lot of errors that would otherwise show you that something is wrong with your code. I prefer to program defensively than having to handle exceptions.
  For testing, disable your error trapping to see if it isn't hiding the cause of your crashes.

• Make sure that the front-end is local to the user machine, You mention they get their individual front-end from the network but I'm not sure if they run it from there or if it it copied on their local machine. At any rate, it should be local not on a remote folder.

• You mention using SQL Server as a backend. Try to trace all the queries being executed. It's possible that the issue comes from communication with SQL Server, a corrupt driver, a security issue that prevents some queries from being run, a query returning unexpected data, etc. Watch the log files and event log on the server closely for strange errors, especially if they involve security.

• Speaking of event log, look for the trace of the crash in the event log of your users. There may be information there, however cryptic.

• If you use custom ribbon actions, make sure thy are not causing issues. I had strange problems over time with the ribbon. Log all all function calls made by the ribbon.

Phenocryst