Randomly getting error VBA modules in this database appear to have been saved with errors

[BallunarCrew]

I have a rather large database (560 mb) in Access 2013 and get the error message "The VBA modules in this database appear to have been saved with errors...." seemingly randomly. After a compact and repair the VBA code is gone from the database. The module names are there but when I try to view one it says the module name is misspelled or refers to a module that does not exist. The users were in the database earlier in the day with no problem and then this started with no changes having been made to the database. Prior to this the last time I made any programming changes was three days ago and it compiled just fine so I don't believe this is a VBA module being saved with errors problem. At some point during the day the users also received the message "Doctor database has detected corruption to this file. To repair - backup and do a compact and repair." I am not sure which message they received first.

My question is why this seemingly random, maybe once a month, corruption occurs and what I can do to prevent it? It does not appear to be connected to when I have been programming anything. I am in Norway and these users are in Texas so I am not able to be there to witness it happen.
Thanks for any help provided.
Karen

 

[PHV]

The users were in the database
So, the database isn't split ?
In a multi-user environment you should have a shared BackEnd and each user a local copy of the FrontEnd.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BallunarCrew]

I know, I know - and no, the database is not split. We have tried that a couple of times over the years and performance was absolutely dreadful so I was told to un-split it. This current problem seems to have become more frequent since moving to Access 2010 and now 2013.

 

[missinglinq]

Having multiple users sharing a single, non-split database is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior, including the problem you're having, now! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past eight years have seen literally dozens and dozens of reports of non-split databases using links to a single Front End, causing these kinds of problems as well as massive data lose! The really insidious thing is that a non-split app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

You need to tell the powers that be that if they're happy with the app acting up like this, over and over and over again, requiring a fix each time, and if the real possibility of massive data-loss doesn't concern them, then you can leave the app non-split; otherwise splitting it has to be done. You can then look into why the app runs so slowly when split; there's really no reason that you should experience such a slowdown.

Linq ;0)>


The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[lamarw]

I was going to suggest creating a new blank database and importing everything from the old one into a new one. A lot of hidden data can be corrected doing this. However, I agree with all the comment's above pointing to the 'unsplit' database. Even if you recreated the database you would definitely need to split it. The recreation would only be a starting point.

 

[kjv1611]

This may take care of all your issues at once, assuming you can get permission:
[ol 1]
[li]Create a copy for backup - always backup.[/li]
[li]Create a new blank database[/li]
[li]In the new database, make sure to uncheck the options for name autocorrect (under "Current Database")[/li]
[li]In the new database, ONLY copy in table objects - nothing else.[/li]
[li]Create a 2nd new database, and do the same thing with the autocorrect options[/li]
[li]Copy everything except the tables from original database into the new database.[/li]
[li]In your first new database (with tables), name it something like _backend or DbName_be, and save it to a hidden subfolder under your project folder (much safer that way from accidental opening by other users)[/li]
[li]Now in your 2nd new database (no table objects in #2), link in all the objects from the 1st new database (only table objects in #1)[/li]
[li]NOW, first create a backup of what you have so far, in case something goes wrong in the next steps, which generally it shouldn't, short of a networking issue.[/li]
[li]Open database #2 of the new databases, and run through the decompile and recompile process as detailed in following links.[/li]
[li]Open database #1, and do a compact and repair - do not bother with the decompile, since it will have zero effect where no code or forms are, at least that's been my experience, as well as what I've read.[/li]
[li]Once all that is done, in your new "front end" where you have forms, etc, make sure you have the startup options set correctly - to open your form, or maybe if you're still using an autoexec macro, whatever you chose.. make sure the startup is working to your liking.[/li]
[li]Now, for giving out copies of the database as front-end copies. Do not give out your only copy of the front-end. Make sure when you give a copy to anyone, you keep the original, or an unused copy of the original. That way, if a front-end does get corrupted, it's easy enough to delete that one, and give another copy of your original front-end.[/li]
[li]Once you have all this setup, create another backup copy, this one of your "production" setup.[/li]
[li]If you don't have any performance issues at this point, but start getting performance issues later, then I suggest making sure everyone is out of the database at the same point in time, then do a compact and repair on the backend, and just hand out new copies of the original front end. If that fixes the issue, you'll know it has something to do with data built up in temp objects/tables.[/li]
[li]If the latter doesn't fix it, then you should try to diagnose what is causing the performance issues. Is it a design flaw? Is it user error? Is it network sluggishness (which I would not suspect, since you are saying you do not have the same issues with a single shared database).[/li]
[/ol]

Links for decompile method:

http://rogersaccessblog.blogspot.com/2009/04/how-do-i-decompile-database.html

- I really like reading his tips, noets, etc - lots of good info overall, besides this one item.

http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

http://www.datagnostics.com/dtips/decompile.html

http://www.allenbrowne.com/ser-47.html

- this is my absolute favorite single source for Access tips, templates, information, etc. Well, this is the page about data corruption recovery, which goes over the decompile method, but his entire site can be very useful.

One final note on my list order. I just typed as I thought. It's possible that moving around a couple of items might be easier or make more sense, but I believe the steps given in the order given will get you from point A to point B.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Also.. if you suspect that the VBA code has errors, then really, that's where you should start with your diagnosis of the issues, but first getting this to a split database design will at least protect the data from possible issues even during your development.

3 things to make sure to always do with Access database projects:
1. Back-up
2. Split design, always, even if you think you don't need it.
3. Decompile/Compile routine on any databases that contain code/forms.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57