Problems with Option Compare Database

[MWE]

I have posted twice about problems with macros suddenly not running in Access (first post, second post). I was able to get things working each time, but had no idea why the problem(s) started or what fixed them. I think I have discovered part of the problem, but do not understand the mechanism.

After exiting Access a few times and rebooting twice, the problems the 2nd time stopped. Then I inserted a code module (but did not add any code) and the problems returned. I removed the empty code module and the problems stopped. The only statement in the otherwise empty code module was Option Compare Database. So I looked through other code modules in this project and found Option Compare Database in several places. I commented out all of them and tested most of the macros. Everything ran fine. I removed the single quote from one example and no macros would run.

My testing is not exhaustive and will likely fail every statistical test. But there appears to be a pattern here. I think I know what Option Compare Database does (based on some poking around in the MS site). But that does not seem to relate to the problems I have been having.

Any thoughts?

EDIT: I somehow misposted this to the Excel area; should be Access. I have asked a forum moderator/approver/magician to move it to the rright area.

[MOS MASTER]

EDIT: I somehow misposted this to the Excel area; should be Access. I have asked a forum moderator/approver/magician to move it to the rright area.

Magician at Your Service buddy!

[chocobochick]

You know, I've never quite understood how Option Compare Database works, even despite the Help context. But with a little research, I did come across this interesting article.

Would this be relevant at all?

[MWE]

You know, I've never quite understood how Option Compare Database works, even despite the Help context. But with a little research, I did come across this interesting article.

Would this be relevant at all?

Thanks for the ferret work.

The article provides additional info on comparison methods (which I think I already understood). I can not understand why all macros in a given DB (and I mean every one) would suddenly "not be available" when this option is toggled ... UNLESS toggling this option fundamentally changes how Access interprets and compares information in its "native mode". If Access uses VBA to performs its internal functions (and I think it does for some things), then toggling this option could suddenly make Access think that VBA proc names were wrong, i.e., the proc name associated with the on-click event is not available. That could (almost) explain this strange situation. What it does not explain is why the problem sometimes goes away when Access is exited and reentered or when the system is rebooted (unless "something" internal is reset and Access is no longer is speaking Urdu and Flanglish internally)

I would appreciate further comments on this thought from people who really know how Access works.

[xCav8r]

Did you create a new files and import everything to it as geekgirl suggested?

[MWE]

Did you create a new files and import everything to it as geekgirl suggested?

Yes and no. I have created a new version, but I continue to explore the old one in an attempt to understand what might be wrong. I know that it may simply be AAU (Access Acting Up ... I seem to have 3 letter acronyms on my mind recently), but I am uncomfortable working with a program that does completely unexplainable things.

[TonyJollans]

Given your other problem with the VBE, I am suspicious about your installation. Yes, Access can be wierd, but not that wierd.

If Access uses VBA to performs its internal functions ...

I don't think it does.

What version of Access (and the rest of Office) are you using?

[MWE]

Given your other problem with the VBE, I am suspicious about your installation. Yes, Access can be wierd, but not that wierd.

...

does this mean that continuation characters do not disappear in your VBEs?

...
I don't think it does.
...

you do not think that Access uses VBA internally?

... What version of Access (and the rest of Office) are you using?

Office2K and Access2K. The specific build is 9.0.3821 SR-1.

Now before anyone says I should install the updates, let me explain why I am running a version that predates Bush II. I have installed Office from this CD/version on several computers. Each time I updated, I lost functionality, i.e., something in one or more Office programs did not work properly. MS Help was a good example. Each update reduced its utility until with all updates, MS Help did not work at all ... well, that is not true, if what MS Help (in any MS appl) is supposed to do at SR-3 is lock the system and put out endless windows asking if I want to contact Mr Gates (home phone number was not provided), then it was working fine.

[TonyJollans]

does this mean that continuation characters do not disappear in your VBEs?

Correct. They do not disappear. I have just double checked in both Excel ans Access 2000 and can't get rid of them, no no-how.

you do not think that Access uses VBA internally?

I have no direct knowledge on this, but I don't think they use VBA for several reasons. In particular there are some subtle differences. Also, since 2000, VBA 6 used by Access is the same VBA used by all the rest of Office and I just can't see all the hooks being there - and the new functions (e.g Replace) are not available in Access which you would expect if there was a connection. I repeat that I do not know this - it is only my (best) guess.

As an aside, I wish there were more commonality (and not just in Access) - the trivial differences between ostensibly similar functions in Office apps and VBA are a source of confusion for many.

Do you have a sample database exhibiting the problem(s) that you can post? Or does it happen to all your databases?