424,303 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

Is It possible to set Access Startup Options using VBA?

twinnyfo
Expert Mod 2.5K+
P: 2,896
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!
Dec 7 '11 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
    2. On Error GoTo Err_ChangeProperty
    3. Dim MyDB As DAO.Database
    4. Dim MyProperty As DAO.Property
    5.  
    6. Set MyDB = CurrentDb()
    7.  
    8. 'Property exists, so set its Value
    9. MyDB.Properties(strPropertyName) = varPropertyValue
    10. ChangeProperty = True
    11.  
    12. Exit_ChangeProperty:
    13.   Exit Function
    14.  
    15. Err_ChangeProperty:
    16.   If Err.Number = 3270 Then       'Property not found
    17.     'Since the Property isn't found, create it!
    18.     Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
    19.     MyDB.Properties.Append MyProperty
    20.       Resume Next
    21.   Else
    22.    'Unknown Error
    23.    ChangeProperty = False
    24.      Resume Exit_ChangeProperty
    25.   End If
    26. End Function
  2. Function Call:
    Expand|Select|Wrap|Line Numbers
    1. ChangeProperty "AllowFullMenus", dbBoolean, False

Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,595
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
    2. On Error GoTo Err_ChangeProperty
    3. Dim MyDB As DAO.Database
    4. Dim MyProperty As DAO.Property
    5.  
    6. Set MyDB = CurrentDb()
    7.  
    8. 'Property exists, so set its Value
    9. MyDB.Properties(strPropertyName) = varPropertyValue
    10. ChangeProperty = True
    11.  
    12. Exit_ChangeProperty:
    13.   Exit Function
    14.  
    15. Err_ChangeProperty:
    16.   If Err.Number = 3270 Then       'Property not found
    17.     'Since the Property isn't found, create it!
    18.     Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
    19.     MyDB.Properties.Append MyProperty
    20.       Resume Next
    21.   Else
    22.    'Unknown Error
    23.    ChangeProperty = False
    24.      Resume Exit_ChangeProperty
    25.   End If
    26. End Function
  2. Function Call:
    Expand|Select|Wrap|Line Numbers
    1. ChangeProperty "AllowFullMenus", dbBoolean, False
Dec 8 '11 #2

twinnyfo
Expert Mod 2.5K+
P: 2,896
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!
Dec 8 '11 #3

ADezii
Expert 5K+
P: 8,595
All the answers to your questions are listed below:
Expand|Select|Wrap|Line Numbers
  1. Text in Startup Dialog Box         Property name
  2. -------------------------------------------------------------
  3. Application Title                  AppTitle
  4. Application Icon                   AppIcon
  5. Display Form/Page                  StartupForm
  6. Display Database Window            StartupShowDBWindow
  7. Display Status Bar                 StartupShowStatusBar
  8. Menu Bar                           StartupMenuBar
  9. Shortcut Menu Bar                  StartupShortcutMenuBar
  10. Allow Full Menus                   AllowFullMenus
  11. Allow Default Shortcut Menus       AllowShortcutMenus
  12. Allow Built-In Toolbars            AllowBuiltInToolbars
  13. Allow Toolbar/Menu Changes         AllowToolbarChanges
  14. Allow Viewing Code After Error     AllowBreakIntoCode
  15. Use Access Special Keys            AllowSpecialKeys
Dec 8 '11 #4

twinnyfo
Expert Mod 2.5K+
P: 2,896
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!
Dec 8 '11 #5

ADezii
Expert 5K+
P: 8,595
@twinnyfo:
It is not at all intuitive on how you can Set/Retrieve these Properties.
Dec 8 '11 #6

100+
P: 759
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) ?
Dec 8 '11 #7

ADezii
Expert 5K+
P: 8,595
@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.
Dec 8 '11 #8

NeoPa
Expert Mod 15k+
P: 31,186
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 :-)
Dec 8 '11 #9

NeoPa
Expert Mod 15k+
P: 31,186
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 :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conNoProp As Integer = 3270
  5. Private Const conVBToDB As String = "\2|3\3|4\4|6\5|7\6|5" & _
  6.                                     "\7|8\8|10\11|1\14|20\17|2"
  7.  
  8. 'SetProperty() requires that either intPType is set explicitly OR that
  9. '              varPVal has a valid value if a new property is to be created.
  10. Public Sub SetProperty(strPName As String, varPVal As Variant, _
  11.                        Optional ByVal db As DAO.Database, _
  12.                        Optional intPType As Integer = -1)
  13.     Dim prp As DAO.Property
  14.  
  15.     If db Is Nothing Then Set db = CurrentDb
  16.     If PropertyExists(strPName, db) Then
  17.         db.Properties(strPName) = varPVal
  18.     Else
  19.         If intPType = -1 Then intPType = DBVal(VarType(varPVal))
  20.         Set prp = db.CreateProperty(strPName, intPType, varPVal)
  21.         Call db.Properties.Append(prp)
  22.     End If
  23. End Sub
  24.  
  25. Public Function GetProperty(ByRef strPName As String, _
  26.                             Optional ByVal db As DAO.Database) As Variant
  27.     If db Is Nothing Then Set db = CurrentDb
  28.     If PropertyExists(strPName, db) Then GetProperty = db.Properties(strPName)
  29. End Function
  30.  
  31. Public Function PropertyExists(ByRef strPName As String, _
  32.                                Optional ByVal db As DAO.Database) As Boolean
  33.     Dim varTest As Variant
  34.  
  35.     On Error GoTo Err_PropertyExists
  36.     If db Is Nothing Then Set db = CurrentDb
  37.     PropertyExists = True
  38.     varTest = db.Properties(strPName)
  39.     Exit Function
  40.  
  41. Err_PropertyExists:
  42.     If Err <> conNoProp Then
  43.         On Error GoTo 0
  44.         Resume
  45.     End If
  46.     PropertyExists = False
  47. End Function
  48.  
  49. Public Sub DelProperty(ByRef strPName As String, _
  50.                        Optional ByVal db As DAO.Database)
  51.     If db Is Nothing Then Set db = CurrentDb
  52.     If Not PropertyExists(strPName, db) Then Exit Sub
  53.     Call db.Properties.Delete(strPName)
  54. End Sub
  55.  
  56. Private Function DBVal(intVBVal) As Integer
  57.     Dim intX As Integer
  58.  
  59.     intX = InStr(1, conVBToDB, "\" & intVBVal & "|")
  60.     DBVal = Val(Mid(conVBToDB, intX + Len(intVBVal) + 2))
  61. 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.
Dec 8 '11 #10

ADezii
Expert 5K+
P: 8,595
Nice adaptation, NeoPa.
Dec 8 '11 #11

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 8 '11 #12

NeoPa
Expert Mod 15k+
P: 31,186
I'm so glad I found this again. It made creating just such a module again so much easier :-D
Nov 3 '13 #13

P: 11
@ADezii
Using this, how would I set the Application Title? I got the boolean stuff working but I run into a data type conversion error when I try setting the app title using dbString.
Jan 15 '19 #14

twinnyfo
Expert Mod 2.5K+
P: 2,896
Mjoyce1962,

Exactly what have you tried? Your description is is not very clear at all.

You should be able to use:
Expand|Select|Wrap|Line Numbers
  1. ChangeProperty "AppTitle", dbText, "Your Application Title"
Jan 15 '19 #15

P: 11
Thanks for responding. I used ADezils code to change the DB properties such as allow shortcut menus etc and it works fine as long as the property is a Boolean but when I tried to set the application title using dbString instead of dbBoolean I get a data type conversion error. What is strange is that it worked the first time I ran it but when I copied that database and tried it again, it failed.

I think I have resolved the issuing using dbText as the property type.
Jan 15 '19 #16

twinnyfo
Expert Mod 2.5K+
P: 2,896
Glad I could hepp! Let us know if you need anything else!
Jan 15 '19 #17

Post your reply

Sign in to post your reply or Sign up for a free account.