Unable to fix access corruption

Question

Serious issues have recently appeared in my Access database. The errors that we receive show that they indicate corruption - here are the most common:

• Error accessing the file. The network connection may have been lost.
• Error compiling this function.
• There are no errors, access just ends completely.

I noticed that these errors only happen with the compiled database. If I decompile it, it works fine. If I take an uncompiled database and compile it, it will work fine - until the next time I try to open it. It seems that compiling the database into a .ACCDE file solves the problem, and that is what I did, but one person said that the problem returned for her, and I am very nervous.

I tried to export all the objects in the database to text, starting from the new database, and import them all again, but this does not solve the problem. As soon as I import all the objects into a clean database, the problem will return.

One last point that seems related, but I don’t understand how to do it. The problem arose almost immediately after I added some basic modules to the database. These class modules use the VBA Implements keyword to clear my code by introducing some polymorphism. I don't know why this will cause a problem, but time seems to indicate a connection.

I was looking for an explanation, but have not yet found it. Anyone have any suggestions?

EDIT: the database includes several links in addition to the standard ones:

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

Answer 1

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

• Check out my application on multiple virtual machines. You can use HyperV on Win8, VMWare or VirtualBox to configure various controlled test environments, such as testing on WinXP, Win7, Win8, 32 bits or 64 bits, just for anything that matches the OS range and the bits of your users.

• I use vbWatchDog , a smart utility that adds only a few classes to your application (without external dependency) and allows you to catch high-level errors and show you where they occur. It is priceless to catch and record strange errors, especially in the field.

• If the problem seems to be isolated only for one or more users, I will try to find out what is special in their configuration. If nothing was inappropriate, I would completely cancel the entire Office component and reinstall it after cleaning the registry to freeze keys and remove all traces of folders from the old installation.

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

• Make sure that you use consistent versions of Access in everything: if you use Access 2007, make sure that your development machine also uses this version and that all other users also use only this version and that there are no components from Access 2010/2013.

• Try to find out if a crash always occurs around the same user actions. I use a simple log file that I write when the debug flag is set. A log file is a simple text file that I open / write / close every time I write something (I do not keep it open to make sure that the data turns red in the file, otherwise when Access fails, you may only the old data in the log file, since the new one may still be in the buffer). Things that I register are, for example, a sensitive input / output function, SQL queries that I execute from code, open / close, etc.

• Generally, make sure your application compiles without problems (I mean when running Debug> Compile from the IDE). Any problem at this stage should be resolved.

• Make sure you close all open recordsets, preferably after they set their variables to Nothing . VBA is not as sensitive as before when it came to dangling links, but I found it good practice, especially if you cannot be absolutely sure that these links will be released (especially when you do things at the module or class level, for example where the scope may be more durable than expected).

• Similarly, make sure you correctly destroy any COM object created in your classes (and subs / functions). The Class_Terminate destructor must explicitly clear everything. This also works when closing forms if you created COM objects (you mentioned using ADOX script objects and regular expression). In general, tracking created objects is paramount: make sure you explicitly free all your objects by reloading them (for example, using RemoveAll in the dictionary, and then assigning them a reference to Nothing .

• Do not overdo On Error Resume or On Error Goto . I almost never use them, except when it is absolutely necessary to recover from other undetectable errors. Using these error capture constructs can hide a lot of errors that would otherwise show that something is wrong with your code. I prefer programming defensively than handling exceptions.
  For testing, disable error capture to make sure that it does not hide the cause of your failures.

• Ensure that the external interface is local to the user machine. You mentioned that they get their individual interface from the network, but I'm not sure if they start it from there or if it is copied to their local machine. In any case, it must be local not in the remote folder.

• You mention using SQL Server as a backend. Try to track all running queries. Perhaps the problem is related to communication with SQL Server, a corrupt driver, a security problem that prevents some queries from starting, a query that returns unexpected data, etc. Keep track of the log files and event logs on the server, especially if they are security related.

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

• If you use custom actions with the tape, make sure that you are not causing problems. Over time, I had weird tape problems. Record all function calls made by the tape.