Friday, February 12, 2016

Database Startup Properties

A recent question in the forums asked how to set certain options for the Current Database through code. Presumably, the purpose is to restrict the user from changing these settings manually when trying to bypass any security measures the developer designed into the database application. Doing a search through the Internet didn't produce a complete list of all possible database option settings, so I decided to put all the pieces of the puzzle together in one place.

So, what exactly are we talking about? Basically, we are interested in all the settings you can find in the Current Database tab when you open the Access Options window (see image below).
Access Options

Application.SetOption

The following options can be set using the Application.SetOption command:
OptionPropertyValue
Compact on CloseAuto CompactFalse
Remove personal information from file properties on saveRemove Personal InformationTrue
Use Windows-themed Controls on FormsThemed Form ControlsTrue
Enable Layout ViewDesignWithDataTrue
Check for truncated number fieldsCheckTruncatedNumFieldsTrue
Preserve source image formatPicture Property Storage Format0
Convert all picture data to bitmapsPicture Property Storage Format1
Track name AutoCorrect infoTrack Name AutoCorrect InfoFalse
Perform name AutoCorrectPerform Name AutoCorrectFalse
Log name AutoCorrect changesLog Name AutoCorrect ChangesTrue
Show list of values in: Local indexed fieldsShow Values In IndexedTrue
Show list of values in: Local nonindexed fieldsShow Values In Non-IndexedTrue
Show list of values in: ODBC fieldsShow Values In RemoteFalse

CurrentDB.Properties

The following options can be set using the CurrentDB.Properties collection:
OptionPropertyValue
Application TitleAppTitleText
Application IconAppIconText
Display FormStartupFormText
Display Status BarStartupShowStatusBarTrue
Overlapping WindowsUseMDIMode1
Tabbed DocumentsUseMDIMode0
Display Document TabsShowDocumentTabsTrue
Display Navigation PaneStartupShowDBWindowTrue
Ribbon NameCustomRibbonIDText
Shortcut Menu BarStartupShortcutMenuBarText
Allow Full MenusAllowFullMenusTrue
Allow Default Shortcut MenusAllowShortcutMenusTrue

Examples:

Application.SetOption "Auto Compact", True
CurrentDB.Properties("AllowFullMenus") = False
CurrentDB.Properties.Append CurrentDB.CreateProperty("CustomRibbonID", dbText, "MyRibbon")

Original post date: February 27, 2013

5 comments:

  1. Thanks DBGuy .... Good to have this info in one place

    ReplyDelete
  2. Hello. Great compilation!

    Another property. To force the AutoKeys macro, you must use Application.SetOption "Key Assignment Macro", "Autokeys"

    (from a question on www.mvp-access.com)

    ReplyDelete
  3. If you go to Access Options, under Display Navigation Pane is a Navigation Options button. Clicking that and then selecting Object Type in left pane gives Forms, Queries, Tables, etc., each with a checkbox. Only those enabled checkboxes will be displayed.

    I want to programmatically control who gets access to what Object Type. Is there any way that you're aware of that would allow some people access do different levels like that? I already have UserName() capability, so the identification of users is easy. I just want to to turn those options on and off as needed.

    Appreciate a direct email at reallygoodquotes@yahoo.com.

    ReplyDelete
    Replies
    1. Hmm, not sure. Normally, as a developer, I just turn the whole thing off before deploying the app to the user. It's not considered good practice to allow users direct access to the tables.

      Delete
  4. Here's a useful link on this topic:

    https://docs.microsoft.com/en-us/office/vba/access/Concepts/Settings/set-options-from-visual-basic

    ReplyDelete