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
Introduction What Are Global Options? Setting Global Options in Programming Code Global Options Summary View Options General Options Web Options Edit/Find Options Keyboard Options Datasheet Options Forms/Reports Options Advanced Options Tables/Queries Options Extra Options Additional 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.