Setting Global Options Programmatically in Access 2000

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Acey James Bunch
Microsoft Corporation

March 2000

Applies To: Microsoft® Access 2000

Summary: This article covers setting the global options for Access in programming code. It includes discussion, sample code, and detailed tables of all appropriate options. It highlights those options that were documented incorrectly and points out some undocumented options. (17 printed pages)

Contents

IntroductionWhat Are Global Options?Setting Global Options in Programming CodeGlobal Options Summary   View Options   General Options   Web Options   Edit/Find Options   Keyboard Options   Datasheet Options   Forms/Reports Options   Advanced Options   Tables/Queries Options   Extra OptionsAdditional Resources

Introduction

As with previous versions of Microsoft® Access, Access 2000 allows you to set many options that affect various aspects of the Access environment. Sometimes known as environment settings, these options can alter the Access environment, and in some cases, provide information about the environment's current state. This article discusses the many different global options that you can use, and the different ways in which you can use them.

What Are Global Options?

Global options are those options that affect the Access environment as whole, that is, the settings are applied to the entire Access environment. They are stored in the registry at \HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Access\Settings\. Because they are stored in the registry, the settings will not persist if the database is run on a different machine. If user profiles are turned on, they will affect the Access environment for all databases used by a particular user on the same machine where they are set. The global options that are available in the user interface can be found by selecting the Tools menu, then selecting Options.

Figure 1. The Global Options dialog box

As you can see in the screen shot above, the Options dialog box has eight tabs on it, and each of these tabs relates to some aspect of the Access environment. The following list describes each of the eight tabs.

  • **View—**Objects that can be seen or viewed in the Access user interface.
  • **General—**Variety of general-purpose options such as margins, sort order, four-year formatting, plus Web options.
  • **Edit/Find—**Determines how records are found and that actions are confirmed.
  • **Keyboard—**Controls how Access responds to keyboard input.
  • **Datasheet—**Determines datasheet colors, fonts, and styles.
  • **Forms/Reports—**Sets the form and report templates.
  • **Advanced—**Determines a variety of advanced options such as command-line arguments, refresh rates, and default locking behavior.
  • **Tables/Queries—**Controls the default table and query design features.

Setting Global Options in Programming Code

Although the global options may be set in the Access user interface, you can also set them by using programming code. In general, there are three types of option values that you can set: true/false, alphanumeric, and predefined.

  • True/false values are those options that are turned on or off with a checkbox, and the value returned or set is either True (-1) or False (0).
  • Alphanumeric values are those options that can be set by entering a string or numeric value, and the values returned or set are strings, just as they appear in the Options dialog box.
  • Predefined values are those options that can be chosen from a list box, combo box, or option group, and the values returned or set corresponds to the option's position in the list or option group, with the indexing of those values beginning with zero.

When manipulating global options programmatically, you use the GetOption or SetOption methods of the Application object for both .mdb and .adp databases, and it is the same whether you are using Data Access Objects (DAO) or ActiveX® Data Objects (ADO) for data access. The GetOption method returns the value setting of the option, while the SetOption method allows you to set the option with a value that you provide. In most cases, you should use a Variant data type to hold the values returned from the GetOption method because the type of value returned can vary depending on the type of option.

The following example uses the GetOption method to retrieve the value of the default database directory, which in the Options dialog box is called "Default Database Folder".

Dim varSetting As Variant
varSetting = Application.GetOption("Default Database Directory")

To set the default database directory, use the SetOption method and pass the option name as the first argument, the value as the second:

Application.SetOption "Default Database Directory", "C:\MyDatabases"

It is important to spell the option names correctly, and to use proper spacing. However, the option names are not case-sensitive. If you are setting an option that uses a true/false value, use the True or False reserved words.

Application.SetOption "Show Status Bar", False

If you are setting an option that is a predefined value, simply pass the index value of the item in the list or option group, where 0 is the first item in the list or group, 1 is the second item, and so on. In this example, the index is that of the Number data type.

Application.SetOption "Default Field Type", 2

**Note   **If you use the SetOption method to change a user's global options settings, be sure to restore those settings when your code is finished executing or when your application ends. Otherwise, the settings you specify will be applied to any Access database the user opens.

For more information about using the GetOption or SetOption methods, type getoption or setoption in the Office Assistant or on the Answer Wizard tab in the Visual Basic for Applications Help window, and then click Search.

Global Options Summary

The following sections discuss all of the different options available from the various tabs in the Options dialog box, and they also cover other values you use can use to manipulate global options in programming code. The name values are the exact names that you should use in code. Where appropriate, a specific Access file type is noted if the option only applies to it. Note that all of them will work with either .mdb or .adp files unless otherwise indicated.

View Options

The following options are equivalent to the options found on the View tab of the Options dialog box.

Name Values Description
Show Status Bar True/False Hides or displays the status bar.
Show Startup Dialog Box True/False Hides or displays the startup dialog box that is displayed when launching Access.
Show New Object Shortcuts True/False Hides or displays the new object shortcut wizards.
Show Hidden Objects True/False Hides or displays objects marked as hidden.
Show System Objects True/False Hides or displays the system objects.
ShowWindowsInTaskbar True/False Hides or displays open database objects in the Windows taskbar.
Show Macro Names Column True/False Hides or displays the "Macro Name" column in macro design mode.
Show Conditions Column True/False Hides or displays the "Condition" column in macro design mode.
Database Explorer Click Behavior 0 = Single-click

1 = Double-click

Sets database objects to open with a single-click or a double-click.
Enable Font Switching True/False Enables dual font support for datasheets, forms, and reports.
Substitute Font Name String Sets the style of font used for dual font support. (E.g.: "Arial Black")

General Options

The following options are equivalent to the options found on the General tab of the Options dialog box.

Name Values Description
Left Margin Numeric Sets the default left margin for datasheets, modules, forms, reports, and data access pages.
Right Margin Numeric Sets the default right margin for datasheets, modules, forms, reports, and data access pages.
Top Margin Numeric Sets the default top margin for datasheets, modules, forms, reports, and data access pages.
Bottom Margin Numeric Sets the default bottom margin for datasheets, modules, forms, reports, and data access pages.
Default Database Directory String (a full path name) Sets the default database directory.
Enable MRU File List True/False Enables the use of a recently used file list on the File menu.
Size of MRU File List Numeric (1-9) The number of recently used files to display on the File menu.
Provide Feedback With Sound True/False Enables various tasks like print notifications and alert messages to be accompanied by sound.
Auto Compact True/False Sets the database to be automatically compacted when closed.
Track Name AutoCorrect Info True/False Enables Access to store information needed to perform a Name AutoCorrect. (.mdb only)
Perform Name AutoCorrect True/False Enables Access to repair naming errors. (.mdb only)
Log Name AutoCorrect Changes True/False Enables Access to log the name changes made into a table called AutoCorrectLog. (*.mdb only)
New Database Sort Order Numeric,

1033=General

2052=Chinese Pronunciation

133124=Chinese Stroke Count

1028=Chinese Stroke Count (Taiwan)

197636=Chinese Bopomofo (Taiwan)

1050=Croatian

1029=Czech

1061=Estonian

1036=French

66615=Georgian Modern

66567=German Phone Book

1038=Hungarian

66574=Hungarian Technical

1039=Icelandic

1041=Japanese

66577=Japanese Unicode

1042=Korean

66578=Korean Unicode

1062=Latvian

1063=Lithuanian

1071= Macedonian (FYROM)

1044=Norwegian/Danish

1045=Polish

1048=Romanian

1051=Slovak

1060=Slovenian

1034=Spanish (Traditional)

3082=Spanish (Spain)

1053=Swedish/ Finnish

1054=Thai

1055=Turkish

1058=Ukrainian

1066=Vietnamese

Sets the default database sort order for all new databases. (*.mdb only)
Four-Digit Year Formatting True/False Enables four-digit year formatting for the current database.
Four-Digit Year Formatting All True/False Enables four-digit year formatting for all databases.

Web Options

The following options are equivalent to the options found in the WebOptions dialog box that is accessed from the General tab of the Options dialog box.

Name Values Description
Hyperlink Color Numeric:

0=Black

1=Maroon

2=Green

3=Olive

4=Dark Blue

5=Violet

6=Teal

7=Gray

8=Silver

9=Red

10=Bright Green

11=Yellow

12=Blue

13=Fuchsia

14=Aqua

15=White

Sets the color for hyperlinks that have not been followed.
Followed Hyperlink Color Numeric, same as Hyperlink Color Sets the color for hyperlinks that have been followed.
Underline Hyperlinks True/False Enables hyperlinks to be underlined.

Edit/Find Options

The following options are equivalent to the options found on the Edit/Find tab of the Options dialog box.

Name Values Description
Default Find/Replace Behavior Numeric:

0=Fast search

1=General Search

2=Start of field search

Enables different methods of Find/Replace operations.
Confirm Record Changes True/False Enables confirmation messages when records are changed.
Confirm Document Deletions True/False Enables confirmation messages when a database object is deleted.
Confirm Action Queries True/False Enables confirmation messages when an action query is run. (.mdb only)
Show Values In Indexed True/False Enables displaying indexed fields in a value list. (.mdb only)
Show Values In Non-Indexed True/False Enables displaying non-indexed fields in a value list. (.mdb only)
Show Values In Remote True/False Enables displaying linked table fields in a value list. (.mdb only)
Show Values Limit Numeric The maximum number of records that will be used to build the value list.
Show Values In Snapshot True/False Enables displaying local snapshot fields in a value list. (.adp only)
Show Values In Server True/False Enables displaying server fields in a value list. (.adp only)

Keyboard Options

The following options are equivalent to the options found on the Keyboard tab of the Options dialog box.

Name Values Description
Move After Enter Numeric,

0=Don't move

1=Next field

2=Next record

Determines insertion point after pressing ENTER.
Behavior Entering Field Numeric,

0=Select entire field

1=Go to start of field

2=Go to end of field

Determines the default behavior of the ENTER, TAB, and arrow keys when moving from field to field in forms and datasheets.
Arrow Key Behavior Numeric,

0=Next field

1=Next character

Determines the default behavior of the left and right arrows keys.
Cursor Stops at First/Last Field True/False Determines if the cursor can move from record to record by using the left and right arrow keys.

Datasheet Options

The following options are equivalent to the options found on the Datasheet tab of the Options dialog box.

Name Values Description
Default Font Color Numeric

Same as Hyperlink Color

Sets the default color for text.
Default Background Color Numeric

Same as Hyperlink Color

Sets the default color for the background.
Default Gridlines Color Numeric

Same as Hyperlink Color

Sets the default color for the gridlines.
Default Font Name String Sets the style of font. (E.g.: "Arial Black")
Default Font Weight Numeric,

0=Thin

1=Extra Light

2=Light

3=Normal

4=Medium

5=Semi-bold

6=Bold

7=Extra Bold

8=Heavy

Sets the default weight of fonts.
Default Font Size Numeric (1-127) Sets the default size of fonts.
Default Font Underline True/False Sets the default font to be underlined.
Default Font Italic True/False Sets the default font to be italicized.
Default Gridlines Horizontal True/False Sets the default viewing of the horizontal gridlines.
Default Gridlines Vertical True/False Sets the default viewing of the vertical gridlines.
Default Column Width Numeric (.1-22.75) Sets the default width of the columns.
Default Cell Effect Numeric:

0=Flat

1=Raised

2=Sunken

Sets the default effect for all cells.
Show Animations True/False Enables animations to be shown when adding or deleting columns and records.

Forms/Reports Options

The following options are equivalent to the options found on the Forms/Reports tab of the Options dialog box.

Name Values Description
Selection Behavior Numeric:

0=Partially enclosed

1=Fully enclosed

Defines selection behavior when you drag a rectangle to select controls on forms and reports.
Form Template String The name of the form that is used as a template for newly created forms.
Report Template String The name of the report that is used as a template for newly created reports.
Always Use Event Procedures True/False Enables the default use of the code window when you click a Builder button on a property sheet or a context menu.

Advanced Options

The following options are equivalent to the options found on the Advanced tab of the Options dialog box.

Name Values Description
Ignore DDE Requests True/False Determines if DDE requests from other applications are ignored.
Enable DDE Refresh True/False Determines if DDE links are refreshed at a specified interval.
Command-Line Arguments String Sets the value returned by the Command function. This value overrides the value specified by the Access /cmd command-line option.
OLE/DDE Timeout (sec) Numeric (0-300) Sets the timeout interval in seconds for OLE and DDE operations.
Refresh Interval (sec) Numeric (0-32766) Sets the number of seconds before records are updated in datasheet or form view.
Number of Update Retries Numeric (0-10) Sets the number of times attempted to update a record locked by another user. (.mdb only)
ODBC Refresh Interval (sec) Numeric (1-32766) Sets the number of seconds before records are refreshed from ODBC data sources. (.mdb only)
Update Retry Interval (msec) Numeric (1-1000) Sets the number of milliseconds before an attempt is made to update a record locked by another user. (.mdb only)
Default Open Mode for Databases Numeric

0=Shared

1=Exclusive

Sets the default mode for opening all databases. (.mdb only)
Default Record Locking Numeric

0=No locks

1=All records

2=Edited record

Determines the default record-locking behavior in a multiuser environment. (.mdb only)
Use Row Level Locking True/False Enables row- (record-) level locking for access to data through a form, datasheet, or a results set in code. (.mdb only)
Row Limit Numeric (0-32767) Sets the maximum number of records cached from the database server. (.adp only)

Tables/Queries Options

The following options are equivalent to the options found on the Tables/Queries tab of the Options dialog box.

Name Values Description
Default Text Field Size Numeric (0-255) The default size of newly created text fields. (.mdb only)
Default Number Field Size Numeric:

0=Byte

1=Integer

2=Long Integer

3=Single

4=Double

5=Decimal

6=ReplicationID

The default size of newly created number fields. (.mdb only)
Default Field Type Numeric:

0=Text

1=Memo

2=Number

3=Date/Time

4=Currency

5=AutoNumber

6=Yes/No

7=OLE Object

8=Hyperlink

The default data type of newly created fields. (.mdb only)
AutoIndex on Import/Create String Establishes the beginning and ending characters used to automatically create indexes on imported or newly created fields. (.mdb only)
Show Table Names True/False Determines if table names are shown in the query design grid. (.mdb only)
Output All Fields True/False Determines if all the fields in a query's underlying table or query are displayed. (.mdb only)
Enable AutoJoin True/False Enables an INNER JOIN to be automatically created for two tables added to the query design window. (.mdb only)
Run Permissions Numeric:

0=Owner's

1=User's

Determines the default privileges for running queries. (.mdb only)

Extra Options

The following options are not available from the Options dialog box or any other Access user interface component, they are only available in programming code.

Name Values Description
Built-In Toolbars Available True/False Determines if the built-in toolbars are displayed.
Can Customize Toolbars True/False Determines if the Toolbars option on the View menu is available.
Control Wizards True/False Turns the Control Wizard in the toolbox on or off.
FDCategory Numeric:

-1=All

1=Format

2=Data

3=Event

8=Other

Determines which tab is active in the properties box at design time. This value is only checked the first time you open a form for design.
Key Assignment Macro String Determines the name of the macro used for key assignments. The default is "AutoKeys".
Large Toolbar Buttons True/False Determines if large buttons are used on the toolbars.
Maximized True/False Determines if the main Access window was maximized at last close. If false, check the Windows coordinate values (Window Height, etc.).
Move Enclosed Controls True/False Determines if a control is moved in design view, all of the controls it contains will also be moved.
MRU1 through MRU9 String Returns the path and file name of the first (or second through ninth) file listed in the most-recently-used list. Setting this value has no effect.
Objects Snap to Grid True/False Determines if objects in design time will snap to the design grid.
Show Grid True/False Determines if the design-time grid is displayed.
Show Ruler True/False Determined if the design-time ruler is displayed.
Use Default Printer Setup True/False Forces Access to print all documents to the default printer. Must quit and restart to take effect.
Warn Width True/False Enables the warning for when a report is too wide to print without tiling. (Read-only)
Window Height Numeric (0-32766) If the Maximized option is false, contains the height of the main Access window, in pixels, when it was last closed. (Read-only)
Window Left Numeric (0-32766) If the Maximized option is false, contains the left position of the main Access window, in pixels, when it was last closed. (Read-only)
Window Top Numeric (0-32766) If the Maximized option is false, contains the top position of the main Access window, in pixels, when it was last closed. (Read-only)
Window Width Numeric (0-32766) If the Maximized option is false, contains the width of the main Access window, in pixels, when it was last closed. (Read-only)
ECLST String The coordinate values for the form/report design fields list. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a form/report for design.
FormPopPropST String The coordinate values for the form design property sheet. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a form for design.
IndexesST String The coordinate values for the table design indexes list. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a form/report for design. (.mdb only)
QueryPopPropST String The coordinate values for the query design property sheet. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a query for design. (.mdb only)
ReportPopPropST String The coordinate values for the report design property sheet. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a report for design.
SGST String The coordinate values for the report sorting/grouping dialog box. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a report for design.
TDPopPropST String The coordinate values for the table design property sheet. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a table for design. (.mdb only)
DPPopPropST String The coordinate values for the data pages design property sheet. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a data page for design.
DatapageSortingGroupingState String The coordinate values for the data page sorting/grouping dialog box. Format is "Open;Left;Top;Width;Height;". This value is only checked the first time you open a data page for design.
Last User String The name of the last user to open the database. Setting this value has no effect.

Additional Resources

Microsoft Office 2000/Visual Basic Programmer's Guide. This comprehensive book covers Office programming with the Visual Basic for Applications programming language.

MSDN. This Web site always has the latest information for developing solutions with Microsoft platforms and languages.

Microsoft Office Developer. Look here for the latest information on developing applications with Microsoft Office.