Option Compare vs Option Explicit

[Michael Abrams]

As I get closer to retirement, I am preparing my successor with updated databases.
For whatever reason, I have never asked about these two situations, so here goes.

I see that I have some databases with either or both of the following:
Option Compare Database
Option Explicit

Are both necessary?

Also, I have always used DoCmd.SetWarnings False and DoCmd.SetWarnings True in addition to error handling.

DoCmd.SetWarnings False

On Error GoTo Err_cmdLeaderReview_Click

<code>

DoCmd.SetWarnings True

Exit_cmdLeaderReview_Click:
Exit Sub
Err_cmdLeaderReview_Click:
MsgBox Err.Description
Resume Exit_cmdLeaderReview_Click

Are both necessary or is error handling enough?

Thank you once again.

[SpeakEasy]

Option Compare and Option Explicit do two very, very different , unrelated things

Option Explicit forces explicit declaration of all variables in a module. Personally, I always recommend it's use as it helps avoid all sorts of pain further down the road


Option Compare sets the method used for string comparison, The three options are Database, Binary and Text (the default if Option Compare is not explicitly set is Binary).

Binary - derived from the internal binary representations of the characters
Text - a case-insensitive text sort order determined by your system's locale
Database - only used in Access, based on the sort order determined by the locale ID of the database

There are two other Option statements - Option Base and Option Private Module

Base - declare the lower bound for array subscripts; default is 0
Private Module - prevents a module's contents from being referenced outside its project.

DoCmd.SetWarnings allows you switch system messages on and off (and a system message may not be an error), whilst On Error allows you to build your own error handling (unhandled errors may or may not raise system messages).

So whilst they may partially work hand-in-hand, you may want neither, either or both ...

[Michael Abrams]

OK - so it sounds like I need to use Option Explicit - it does seem like Option Compare is the default though.

And it sounds like SetWarnings and Error Handling are exclusive so I can use both.

Thank you SpeakEasy.

Michael

[Michael Abrams]

OK - now that read some more and understand this (LOL) it looks like Option Explicit IS the way to go.

I found that I can default to Option Explicit based on this:

Picture1.png

Problem is - I cannot find this

I am using Office 365 32 bit

[Michael Abrams]

Found it

[HansV]

An Access database always has Option Compare Database in every new module, to ensure that strings are compared according to the locale of the database.

About Option Explicit, see The importance of 'Option Explicit'

[Michael Abrams]

Excellent explanation. Thanks again !

[Gasman]

Found it

Be aware that setting that option now, only affects new code.
Your existing code will not be touched.
You need to go through all modules and add it manually.

[Michael Abrams]

Found it

Be aware that setting that option now, only affects new code.
Your existing code will not be touched.
You need to go through all modules and add it manually.

Thank you Gasman.
This are only for new databases that my replacement will create and maintain when I retire.
Michael