Random VBA errors

[cdmonroe]

I've been having trouble with several of my databases getting errors on VBA codes about missing references or invalid call procedures that I can't ever find and eliminate... but if I import the entire database (all tables, forms, macros, reports, modules) into a new file, the errors go away. Is this a known issue? Is there an update or patch I'm missing? or is this just some kind of file corruption issue? Any hints about this would be appreciated. It's quite an annoyance to have these errors that make no sense that disappear in a new file.

Thanks!
Cheryl

[Denburt]

When you import everything into a new file do you make sure to compile the VBA portion? This could help you locate the culprit. If it compiles O.K. then you definitely have an issue with corruption. Although I have compiled mine a few times and it has missed things. A patch may work if the previous fails but I haven't heard of anything.

[cdmonroe]

When I get these errors, compiling sometimes shows a problem, but it doesn't always highlight a specific line even before I import to the new file. I usually don't compile after import because I don't get the errors again.

When you import everything into a new file do you make sure to compile the VBA portion? This could help you locate the culprit. If it compiles O.K. then you definitely have an issue with corruption. Although I have compiled mine a few times and it has missed things. A patch may work if the previous fails but I haven't heard of anything.

[Denburt]

I have had similar issues it usually highlights a line that isn't visible sometimes if I scroll I can see it sometimes not. Try to compile after you import into a new DB and just for hecks sake try creating an MDE file that will tend to find errors you may never have known you had.

[nscorreo]

Here my experiences with random VBA errors and mdb file corruption:

The corruptions of files come from Access 2000 and were corrected with SP and later, in Access 2002.
However, some symptoms appear to be similar and could also cause corruption in Access 2003.

The symptoms are:
- Forms that worked well, don’t run any more.
- Estrange errors in relation to forms/controls/variables. (Access can’t find).
- Limitations to develop complex forms in the screen (Ex: main tab form with forms and sub forms into).
- The most irritant, the VBA editor act erratic, difficult for write.
(Ex: Press space bar and the cursor go fwrd and back to previous position).
(Automatic completation flashed, open and close immediate)
- Problems are traslatate to MDE file.

When happen:
- When try to modify the VBA code while the program is running.
- When try to modify the VBA code belongs to inner sub-forms, while forms are open.
- In some circumstances, when copy complex forms.

How to avoid:
- Never make any changes while the program is running
- For changes in a form or sub-form, close all the forms first, just open the form or sub-form to change
- Don’t make Changes to query by opening from forms properties.
Close the form and open the query alone to modify.
- If you see any indication of corruption, don't save your work. Close and re-open.


What to do:
Before make a new file and copy all the elements to the new, I take flw alternative

- Follow these steps:
1. Open database file using /DECOMPILE flag, in the command line icon, like:
"C:\Program Files\Mic..\OF. .11\MSACCESS.EX E" /decompile "C:\Sis...\ my db file.mdb" /WRKGRP "\\Sis...\m y WG file.MDW"
2. Open any of your VBA modules.
3. In the VBA editor, Compile, save file and then, close the editor.
4. In main database, Repair-Compact db and after, close your file.
5. Enter again to db and verify if the problem is out.

Hope above comments/steps can help.

[Scott Price]

I agree with the last post, and particularly recommend the Decompile/Recompile approach!

Decompiling will also rid your db of lots of wasted space that comes during the development process from testing code, rewriting code, recompiling, etc. (as an example one db I had was a little less than 4mb, after decompile/recompile the same db was a little less than 3mb :-)

And ALWAYS compile!! If you click Debug menu in the VBA editor window and the Compile option is enabled, click it!!!!! I cannot stress this enough as a preventive measure to avoid corruption problems before they start, rather than try to fix them after they crop up in annoying and possibly db-fatal ways later on.

Read Allen Browne's page about preventing corruption here: http://allenbrowne.com/ser-25.html
And recovering from corruption here: http://allenbrowne.com/ser-47.html

Regards,
Scott

[Denburt]

There was a recent article in here about corrupt forms. I have this issue a LOT and thinking about it now it could be because I make changes all the time while the forms code is running. I have found that exporting it to text (using VBA SaveAsText acForm,strName) then reimporting it works fine for that. I also notice that when the code is running I make the change and HAVE to SAVE IMMEDIATELY or changes will be removed. MS Access has many such issues I know of a dozen ways to crash it without even thinking twice.

[Denburt]

FYI Although Allen Browns site has some interesting info there is a a bit left out in both of the articles you linked to. To help prevent corruption you should always close the database before leaving work or your desk for long periods as he did mention that not closing MS Access before shutting down windows I didn't see where he mentioned avoiding front end Back ends across WANS it is almost as bad as doing that over WIFI. You will almost always end up with corruption if you have a front end open on a client PC. and the server where the back end is located reboots. He also didn't mention trying to save a corrupted form/Report using the saveastext method (works like a champ)..

I agree with you both on most everything that was said and particularly would like to mention that Scott has a point. First make sure you ALWAYS use Option Explicit (should go without saying) and I compile (alt. D L) almost as much as I hit ctrl S. :) Yeah keyboard nut..