Setting the Startup Options for an Access 2007 database using VBA

Discussion in 'Microsoft Access VBA Modules' started by genegal, Jan 28, 2010.

  1. genegal

    genegal Guest

    Afternoon everyone,

    I'm trying to use the information from an MSDN page of roughly the same
    title (Setting Startup Options Programmatically in Access 2000,
    http://msdn.microsoft.com/en-us/library/aa140020(office.10).aspx) to setup
    the startup options of my database.

    Firstly, I don't know whether this option should be placed in a module or
    event (like Form_Load).

    Secondly, I'm not too sure that I have the code correctly written down:

    Private Sub Form_Load()

    Dim dbs As CurrentProject

    Set dbs = Application.CurrentProject

    dbs.Properties.Remove "AllowShortcutMenus"
    dbs.Properties.Remove "AllowBuiltInToolbars"
    dbs.Properties.Remove "AllowBypassKey"
    dbs.Properties.Remove "AllowFullMenus"

    Set dbs = Nothing

    End Sub

    Is there anything that I'm missing?

    Your help is much appreciated.

    Gene
     
    genegal, Jan 28, 2010
    #1
    1. Advertisements

  2. The startup options only need to be set once. I can't see any reason to put
    it a form event.

    --
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/DJSteele
    (no e-mails, please!)

    "genegal" <> wrote in message
    news:D...
    > Afternoon everyone,
    >
    > I'm trying to use the information from an MSDN page of roughly the same
    > title (Setting Startup Options Programmatically in Access 2000,
    > http://msdn.microsoft.com/en-us/library/aa140020(office.10).aspx) to setup
    > the startup options of my database.
    >
    > Firstly, I don't know whether this option should be placed in a module or
    > event (like Form_Load).
    >
    > Secondly, I'm not too sure that I have the code correctly written down:
    >
    > Private Sub Form_Load()
    >
    > Dim dbs As CurrentProject
    >
    > Set dbs = Application.CurrentProject
    >
    > dbs.Properties.Remove "AllowShortcutMenus"
    > dbs.Properties.Remove "AllowBuiltInToolbars"
    > dbs.Properties.Remove "AllowBypassKey"
    > dbs.Properties.Remove "AllowFullMenus"
    >
    > Set dbs = Nothing
    >
    > End Sub
    >
    > Is there anything that I'm missing?
    >
    > Your help is much appreciated.
    >
    > Gene
     
    Douglas J. Steele, Jan 28, 2010
    #2
  3. genegal

    genegal Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Maybe I didn't format my question properly:

    I don't know HOW I'm supposed to declare the actions through VBA. Whether
    it's supposed to be through a module, AutoExec macro, etc.

    Furthermore, when I used the above code, I was given as error saying that no
    property exists for such object, which clearly means that the code is wrong.
    Just to serve as a reminder, this code was taken from an MSDN page for Access
    2000 for .mdb database files, not Access 2007 (i.e. accdb) files.

    Does anyone know if things might have changed since then? MSDN doesn't point
    anything out in terms of changes or revisions for Access 2007. So there's
    clearly something I'm not understanding.
     
    genegal, Jan 28, 2010
    #3
  4. genegal

    genegal Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Furthermore, I found this page:

    http://msdn.microsoft.com/en-us/library/aa172335(office.11).aspx

    Which pretty much has the same information, but is presented for Access
    2003, but with no example. So it doesn't seem helpful at all.
     
    genegal, Jan 28, 2010
    #4
  5. Re: Setting the Startup Options for an Access 2007 database using

    Actually, given that there are no menus in Access 2007, I wouldn't expect
    AllowShortcutMenus, AllowBuiltInToolbars or AllowFullMenus to be valid
    properties anymore.

    I know that the AllowBypassKey property doesn't exist unless you've created
    it.


    --
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/DJSteele
    (no e-mails, please!)

    "genegal" <> wrote in message
    news:...
    > Maybe I didn't format my question properly:
    >
    > I don't know HOW I'm supposed to declare the actions through VBA. Whether
    > it's supposed to be through a module, AutoExec macro, etc.
    >
    > Furthermore, when I used the above code, I was given as error saying that
    > no
    > property exists for such object, which clearly means that the code is
    > wrong.
    > Just to serve as a reminder, this code was taken from an MSDN page for
    > Access
    > 2000 for .mdb database files, not Access 2007 (i.e. accdb) files.
    >
    > Does anyone know if things might have changed since then? MSDN doesn't
    > point
    > anything out in terms of changes or revisions for Access 2007. So there's
    > clearly something I'm not understanding.
     
    Douglas J. Steele, Jan 28, 2010
    #5
  6. genegal

    Jack Leach Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Some properties don't exist until they've been created. You'll notice that
    in some code examples, objects are tested to see if they have a property
    before setting it. You try to check the value of the object's property, trap
    the error if one occurs, create the property if the error is that the
    property doesn't exist, and then reroute your code back to the point where
    you set it.

    Is there some reason you can't set these startup properties once in a
    template db? Generally startup properties don't get changed after they're
    set.

    hth

    --
    Jack Leach
    www.tristatemachine.com

    "I haven''t failed, I''ve found ten thousand ways that don''t work."
    -Thomas Edison (1847-1931)



    "genegal" wrote:

    > Maybe I didn't format my question properly:
    >
    > I don't know HOW I'm supposed to declare the actions through VBA. Whether
    > it's supposed to be through a module, AutoExec macro, etc.
    >
    > Furthermore, when I used the above code, I was given as error saying that no
    > property exists for such object, which clearly means that the code is wrong.
    > Just to serve as a reminder, this code was taken from an MSDN page for Access
    > 2000 for .mdb database files, not Access 2007 (i.e. accdb) files.
    >
    > Does anyone know if things might have changed since then? MSDN doesn't point
    > anything out in terms of changes or revisions for Access 2007. So there's
    > clearly something I'm not understanding.
     
    Jack Leach, Jan 28, 2010
    #6
  7. genegal

    genegal Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Thanks JimBurke for your input, but I've already set those properties
    (Application.SetOption) without a problem. It's the database options that
    seem to be problematic.
     
    genegal, Jan 29, 2010
    #7
  8. genegal

    genegal Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Assuming what you say is the case, then could you possibly show me an example
    of where I have to declare properties and trap errors?

    I just don't see this as a possible solution, because if I have to go as far
    as trapping errors and declaring properties, it appears clear to me that VBA
    doesn't have an immediate reference... Is there no way to tell VBA to look
    for the Access options and disable them from there?

    For example, "Allow Full Menus" (or ribbon menus in ACC2007) is located in
    the Access options. So surely there must be some call that can be used to
    'locate' the option and change it's condition (true/false) in one line,
    without the need of error trapping?
     
    genegal, Jan 29, 2010
    #8
  9. Re: Setting the Startup Options for an Access 2007 database using

    "genegal" <> wrote in message
    news:...
    > Assuming what you say is the case, then could you possibly show me an
    > example
    > of where I have to declare properties and trap errors?
    >
    > I just don't see this as a possible solution, because if I have to go as
    > far
    > as trapping errors and declaring properties, it appears clear to me that
    > VBA
    > doesn't have an immediate reference... Is there no way to tell VBA to look
    > for the Access options and disable them from there?
    >
    > For example, "Allow Full Menus" (or ribbon menus in ACC2007) is located in
    > the Access options. So surely there must be some call that can be used to
    > 'locate' the option and change it's condition (true/false) in one line,
    > without the need of error trapping?


    This is the best page I've seen on the subject:

    http://msdn2.microsoft.com/en-us/library/aa172326(office.11).aspx
     
    Stuart McCall, Jan 29, 2010
    #9
  10. genegal

    genegal Guest

    Re: Setting the Startup Options for an Access 2007 database using

    Thanks for the page link, but the link you provided me shows the options that
    are set for the presentation of the database (what are called Global Options,
    http://msdn.microsoft.com/en-us/library/aa140014(office.10).aspx), rather
    than the security and integrity of the database (what are called Startup
    Options, http://msdn.microsoft.com/en-us/library/aa140020(office.10).aspx).

    I'm essentially looking for the Startup Options, which are basically the
    settings that were mentioned in the first post.
     
    genegal, Jan 29, 2010
    #10
  11. genegal

    Jack Leach Guest

    Re: Setting the Startup Options for an Access 2007 database using

    > Assuming what you say is the case,

    It is.

    > could you possibly show me an example
    > of where I have to declare properties and trap errors?


    No. I'm far to lazy. But you can go to mvps.org/access and look for
    disable shift bypass key and find it yourself.



    > I just don't see this as a possible solution


    Tough. Go tell that to MVP Douglas Steele who earlier in the thread stated
    that he knows of a particular property that doesn't exist until created.



    Just because you don't have experiance with these things doesn't mean that
    nobody else does either.


    --
    Jack Leach
    www.tristatemachine.com

    "I haven''t failed, I''ve found ten thousand ways that don''t work."
    -Thomas Edison (1847-1931)



    "genegal" wrote:

    > Assuming what you say is the case, then could you possibly show me an example
    > of where I have to declare properties and trap errors?
    >
    > I just don't see this as a possible solution, because if I have to go as far
    > as trapping errors and declaring properties, it appears clear to me that VBA
    > doesn't have an immediate reference... Is there no way to tell VBA to look
    > for the Access options and disable them from there?
    >
    > For example, "Allow Full Menus" (or ribbon menus in ACC2007) is located in
    > the Access options. So surely there must be some call that can be used to
    > 'locate' the option and change it's condition (true/false) in one line,
    > without the need of error trapping?
     
    Jack Leach, Jan 29, 2010
    #11
  12. genegal

    Jack Leach Guest

    Re: Setting the Startup Options for an Access 2007 database using

    > it appears clear to me that VBA
    > doesn't have an immediate reference...


    Depending on how you want to look at it, VBA does, or doesn't, haven't an
    "immediate reference" to these properties. VBA is not "part" of the
    database. Access and VBA are two different things... VBA is merely a tool
    that we use to access various parts of the Access application. VBA does not
    "have" these properties, but instead provides us a way to interact with
    Access, which *may* have these properties.

    So, VBA does not have an "immediate reference". In fact, it has no
    reference whatsoever, as these properties are not part of VBA.

    VBA does however, have a method to reference these properties:

    CurrentDb.Properties("propname") = Value



    The point that you are missing is that even though VBA gives us an interface
    to work with these database properties, that does not mean that the
    properties exist in Access. Some do, by default, other's don't, by default,
    and need to be created (such as AllowBypassKey). You can create your own
    custom properties and refer to them elsewhere.

    Some properties that you create are already "defined" somehow in Access,
    even if they do not yet exist. AllowBypassKey is a good example. It
    doesn't, by default, exist, but when created, Access will recognize it and
    handle accordingly. Other properties, custom ones, for instance say
    "ThisTestProp", you can create, and Access itself will do absolutely nothing
    with it. But you, as the developer, and use it elsewhere even though it has
    not internal meaning to Access.


    This may seem to be getting a bit off base with your original question, but
    really, it's not. From what I gather (though you haven't been very clear on
    it), you are using v2007, and yet referencing built-in properties of v2000
    and v2003. IMHO, this is not a good idea.

    Working with properties like this is pretty tricky, because of the fact that
    some may be part of Access, even though they may not exist yet. This is an
    area that people far more knowledgable than I tread lightly in. Who's to say
    that when you go creating/setting properties from previous versions what
    might happen? Who knows what properties are built-in but don't exist by
    default (such as AllowBypass)? Who knows what kind of effect messing up
    something like this may have? This isn't really something that you just jump
    in and play around with... could be a bit dangerous to your project.

    For example, from your oringal post...

    dbs.Properties.Remove "AllowShortcutMenus"

    you are not "setting" the property to False here, you are *completely
    removing the property from the project!!!*. Just my opinion, but not a good
    idea, I don't think.


    This is the reason that most people tend to set them once, manually, and
    then forget about it. Rarely do we find reasons to handle these things
    programmatically.

    Maybe this gives a little more light on why you can't use a one-liner to set
    these. If you really insist on a one-liner, you're going to need your own
    function to handle it...

    SetProperty "propname"
    SetProperty "someotherprop"
    SetProperty "thatone"


    Function Set Property(prop As String)
    'do all your validating/creating here
    'set your prop after you've validated it
    End Function



    To be insistent upon finding a one-liner of code to do what in fact requires
    many lines of code with no getting around it the easy way is akin to
    expecting your car to go gas itself after you park it if there's less than
    quarter tank. Some things you just can't get away from.


    --
    Jack Leach
    www.tristatemachine.com

    "I haven't failed, I've found ten thousand ways that don't work."
    -Thomas Edison (1847-1931)



    "genegal" wrote:

    > Assuming what you say is the case, then could you possibly show me an example
    > of where I have to declare properties and trap errors?
    >
    > I just don't see this as a possible solution, because if I have to go as far
    > as trapping errors and declaring properties, it appears clear to me that VBA
    > doesn't have an immediate reference... Is there no way to tell VBA to look
    > for the Access options and disable them from there?
    >
    > For example, "Allow Full Menus" (or ribbon menus in ACC2007) is located in
    > the Access options. So surely there must be some call that can be used to
    > 'locate' the option and change it's condition (true/false) in one line,
    > without the need of error trapping?
     
    Jack Leach, Jan 29, 2010
    #12
  13. Re: Setting the Startup Options for an Access 2007 database using

    =?Utf-8?B?Z2VuZWdhbA==?= <> wrote
    in news::

    > I've already set those properties
    > (Application.SetOption) without a problem. It's the database
    > options that seem to be problematic.


    Why do you need to do this more than once?

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Jan 29, 2010
    #13
  14. genegal

    Jack Leach Guest

    Re: Setting the Startup Options for an Access 2007 database using

    > could you possibly show me an example
    > of where I have to declare properties and trap errors?


    I happened across this a few moments ago...




    Source: http://www.databasedev.co.uk/disable_shift_bypass.html

    '***************** Code Start ***************
    'Copy this function into a new public module.

    Option Compare Database
    Option Explicit

    Public Function SetProperties(strPropName As String, _
    varPropType As Variant, varPropValue As Variant) As Integer

    On Error GoTo Err_SetProperties

    Dim db As DAO.Database, prp As DAO.Property

    Set db = CurrentDb
    db.Properties(strPropName) = varPropValue
    SetProperties = True
    Set db = Nothing

    Exit_SetProperties:
    Exit Function

    Err_SetProperties:
    If Err = 3270 Then 'Property not found
    Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
    db.Properties.Append prp
    Resume Next
    Else
    SetProperties = False
    MsgBox "SetProperties", Err.Number, Err.Description
    Resume Exit_SetProperties
    End If
    End Function
    '***************** Code End ***************





    --
    Jack Leach
    www.tristatemachine.com

    "I haven''t failed, I''ve found ten thousand ways that don''t work."
    -Thomas Edison (1847-1931)



    "genegal" wrote:

    > Assuming what you say is the case, then could you possibly show me an example
    > of where I have to declare properties and trap errors?
    >
    > I just don't see this as a possible solution, because if I have to go as far
    > as trapping errors and declaring properties, it appears clear to me that VBA
    > doesn't have an immediate reference... Is there no way to tell VBA to look
    > for the Access options and disable them from there?
    >
    > For example, "Allow Full Menus" (or ribbon menus in ACC2007) is located in
    > the Access options. So surely there must be some call that can be used to
    > 'locate' the option and change it's condition (true/false) in one line,
    > without the need of error trapping?
     
    Jack Leach, Feb 2, 2010
    #14

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    0
    Views:
    240
    Guest
    Nov 9, 2004
  2. Guest

    Startup Options/Database Password

    Guest, Jul 28, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    9
    Views:
    187
    Guest
    Sep 28, 2006
  3. Guest

    Setting Printing Options via VBA

    Guest, Oct 11, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    2,227
    Guest
    Oct 11, 2006
  4. Sarki

    Access 2003 Database VBA not running in 2007 Access

    Sarki, Jun 17, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    265
    BeWyched
    Jun 18, 2008
  5. dch3

    Setting VBA Options Via Code

    dch3, Jul 10, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    5
    Views:
    218
Loading...

Share This Page