Is It possible to set Access Startup Options using VBA?

[twinnyfo]

I am using MS Access 2007.

I have a database that I do all my editing and programming in and have certain Access options (under the Current Database tab) set one way during the editing. However, when I compile the database into the .accde file, I reopen the database and set certain options, such as the initial display form and navigation pane options. I keep these enabled during programming for ease of use, but disable them whem compiled to prevent others from possibly using them (or at least making it more difficult).

Is it possible to use VBA to set these options to automate this process after the compile? I already have code re-mapping all my linked tables and modifying/deleting unused Switchboard items.

Does anyone have any experience with this?Thanks!

[ADezii]

I think you are referring to the StartUp Properties of a Database and yes, they can be modified but it is a little tricky since they are User Defined Properties and cannot be Set/Retrieved in the conventional manner. If the Property exists, you can directly modify it, if not you must create it then Append it to the proper Collection. The Code below will set the Allow Full Menus Start Up DB Option to False:

1. Function Definition:
   

   Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
   On Error GoTo Err_ChangeProperty
   Dim MyDB As DAO.Database
   Dim MyProperty As DAO.Property
   
   Set MyDB = CurrentDb()
   
   'Property exists, so set its Value
   MyDB.Properties(strPropertyName) = varPropertyValue
   ChangeProperty = True
   
   Exit_ChangeProperty:
     Exit Function
   
   Err_ChangeProperty:
     If Err.Number = 3270 Then       'Property not found
       'Since the Property isn't found, create it!
       Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
       MyDB.Properties.Append MyProperty
         Resume Next
     Else
      'Unknown Error
      ChangeProperty = False
        Resume Exit_ChangeProperty
     End If
   End Function

2. Function Call:
   

   ChangeProperty "AllowFullMenus", dbBoolean, False

[twinnyfo]

Thanks ADezii,

BRILLIANT! That worked perfectly for turning off full menus. However, where does one find the names of these other properties, such as Allow Default Shortcut Menus and the like? I've tried some variations and nothing seems to work.

Also, how does one use VBA to tell the Database to open a specific form when it opens? Doing it manually is easy, but I have no clue where to start doing this in VBA....

Thanks for your help so far!

[ADezii]

All the answers to your questions are listed below:

Text in Startup Dialog Box         Property name
-------------------------------------------------------------
Application Title                  AppTitle
Application Icon                   AppIcon
Display Form/Page                  StartupForm
Display Database Window            StartupShowDBWindow
Display Status Bar                 StartupShowStatusBar
Menu Bar                           StartupMenuBar
Shortcut Menu Bar                  StartupShortcutMenuBar
Allow Full Menus                   AllowFullMenus
Allow Default Shortcut Menus       AllowShortcutMenus
Allow Built-In Toolbars            AllowBuiltInToolbars
Allow Toolbar/Menu Changes         AllowToolbarChanges
Allow Viewing Code After Error     AllowBreakIntoCode
Use Access Special Keys            AllowSpecialKeys

[twinnyfo]

ADezii,

Thank ou again! I wish MS made this stuff more readily available. I was certain I had already tried some of those properties but they didn't work. This time, works like a charm!

Warmest regards!

[ADezii]

@twinnyfo:
It is not at all intuitive on how you can Set/Retrieve these Properties.

[Mihail]

Thank you too, ADezii.
This post is just to subscribe.

By the way: Can I subscribe to a thread without posting something in that thread (and disturb the others) ?

[ADezii]

@Mihail:

Can I subscribe to a thread without posting something in that thread (and disturb the others)?

I actually not 100% sure, but the person to direct this Question to would be NeoPa. Send him a Private Message with this same Question, and I'm sure he will reply to you.

[NeoPa]

Not currently, but there used to be such a facility and we expect there to be such again in a future version of the site :-)

[NeoPa]

That was so useful that I decided to create a module specifically to support working with Properties in Access. Reading, writing, adding and deleting are all supported, and even though the VarType() function returns values in a similar but incompatible set from the dbXXX type values required by CreateProperty( ), I thought it was worthwhile to handle that with a little conversion function that handles the most common and those likely to be used with property values.

The code all seems to work :

Option Compare Database
Option Explicit

Private Const conNoProp As Integer = 3270
Private Const conVBToDB As String = "\2|3\3|4\4|6\5|7\6|5" & _
                                    "\7|8\8|10\11|1\14|20\17|2"

'SetProperty() requires that either intPType is set explicitly OR that
'              varPVal has a valid value if a new property is to be created.
Public Sub SetProperty(strPName As String, varPVal As Variant, _
                       Optional ByVal db As DAO.Database, _
                       Optional intPType As Integer = -1)
    Dim prp As DAO.Property

    If db Is Nothing Then Set db = CurrentDb
    If PropertyExists(strPName, db) Then
        db.Properties(strPName) = varPVal
    Else
        If intPType = -1 Then intPType = DBVal(VarType(varPVal))
        Set prp = db.CreateProperty(strPName, intPType, varPVal)
        Call db.Properties.Append(prp)
    End If
End Sub

Public Function GetProperty(ByRef strPName As String, _
                            Optional ByVal db As DAO.Database) As Variant
    If db Is Nothing Then Set db = CurrentDb
    If PropertyExists(strPName, db) Then GetProperty = db.Properties(strPName)
End Function

Public Function PropertyExists(ByRef strPName As String, _
                               Optional ByVal db As DAO.Database) As Boolean
    Dim varTest As Variant

    On Error GoTo Err_PropertyExists
    If db Is Nothing Then Set db = CurrentDb
    PropertyExists = True
    varTest = db.Properties(strPName)
    Exit Function

Err_PropertyExists:
    If Err <> conNoProp Then
        On Error GoTo 0
        Resume
    End If
    PropertyExists = False
End Function

Public Sub DelProperty(ByRef strPName As String, _
                       Optional ByVal db As DAO.Database)
    If db Is Nothing Then Set db = CurrentDb
    If Not PropertyExists(strPName, db) Then Exit Sub
    Call db.Properties.Delete(strPName)
End Sub

Private Function DBVal(intVBVal) As Integer
    Dim intX As Integer

    intX = InStr(1, conVBToDB, "\" & intVBVal & "|")
    DBVal = Val(Mid(conVBToDB, intX + Len(intVBVal) + 2))
End Function

PS. Congrats to ADezii for helping to make such a complicated process into one that's now quite straightforward . With all this information such work should be a doddle in future.

[ADezii]

Nice adaptation, NeoPa.

[NeoPa]

Thank you ADezii. My aim was not as limited as simply answering this thread (although that was certainly the starting point). I saw this was something that could be more generally helpful (to me as well as others) with just a little more meat on the bones. I expect this module to be available for my use in all future projects.

[NeoPa]

I'm so glad I found this again. It made creating just such a module again so much easier :-D

[NeoPa]

A hijack question has now been moved to a new thread called Set Application Title using VBA.

Please do not post new questions in existing threads.