Access 2002 Options settings thru Code/Macros

Posted on 2005-04-29
Last Modified: 2008-02-01
I need to set certain Options in Access 2002 without having to drop down the menu and choose them.

For instance, if dont want Access to CONFIRM record changes during a query, I would usually open the mdb file, drop down the OPTIONS menu under TOOLS, open the EDIT/FIND tab and uncheck ACTION QUERIES.

Now the question is, how do I achieve the above without having to go through the above steps?  In other words, is there any code/macro I can use to achieve my goal?

Question by:BevinManian
    LVL 2

    Expert Comment

    Hi BevinManian,

    If running the Query from a Macro insert this line above the open query action

    Action:                    Action Argument:
    SetWarnings            No

    If running the Query from Code insert this line above the open query action

    DoCmd.SetWarnings False

    These methods will turn off those messages you are receiving.

    Hope this helps
    LVL 5

    Author Comment

    Thanks Mavreich,
    What I want to achieve is not suppressing warnings.

    I actually want to set the options when the mdb (or mde) file is open, ie, at run time.

    And this applies not only to ACTION QUERIES.  It may include other general database options too.

    LVL 2

    Expert Comment

    Hi Bev,

    Perhaps this is what you are after.  In a module place this code.

    Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
    Dim dbs As Database, prp As Property
    Const conPropNotFoundError = 3270
    Set dbs = CurrentDb
    On Error GoTo Change_Err
        dbs.Properties(strPropName) = varPropValue
        ChangeProperty = True

        Exit Function

        If Err = conPropNotFoundError Then  ' Property not found.
            Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
            dbs.Properties.Append prp
            Resume Next
            ' Unknown error.
            ChangeProperty = False
            Resume Change_Bye
        End If
    End Function

    Function SetStartupProperties()
       ChangeProperty "StartupForm", dbText, "FormName"
       ChangeProperty "StartupShowDBWindow", dbBoolean, False
       ChangeProperty "StartupShowStatusBar", dbBoolean, True
       ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
       ChangeProperty "AllowFullMenus", dbBoolean, False
       ChangeProperty "AllowBreakIntoCode", dbBoolean, False
       ChangeProperty "AllowSpecialKeys", dbBoolean, False
       ChangeProperty "AllowBypassKey", dbBoolean, True
    End Function

     '-----------------------------------End Code----------------------------------------

    Create a macro called autoexec

    Action:                    Action Argument:
    RunCode                  SetStartupProperties()

    The autoexec macro runs automatically when the db opens.  Simply set True or False on the options.

    PLEASE NOTE:  create an UnSetProperties routine that you can access when in the db so that you do not lock yourself out of the db.  AND always remember to make backups.


    LVL 5

    Author Comment

    The above code is for the database startup options, not MS Access options.

    I want to change the Access environment variables not the startup variables.  There is a difference, I think.

    Anyways, thanks for your efforts.

    Will wait a while for someone to come up with a solution to this...

    LVL 17

    Expert Comment

    The only way to do what you are asking is by way of a command line switch.  Unfortunately, there are no switches that will accomplish what you are asking.  You can, however, sort of customize a command line switch and then based on that, programmatically do what you need to do.

    msaccess.exe /cmd "Suppress Messages"

        If Command = "Suppress Messages" Then
            DoCmd.SetWarnings False
        end if

    LVL 1

    Accepted Solution

       Application.SetOption "Confirm Record Changes", False
        Application.SetOption "Confirm Document Deletions", False
        Application.SetOption "Confirm Action Queries", False

    Insert the above into a code module and run on startup

    On exit you might want to revert to it's previous settting. You would need to record this using the GetOption

    Dim ChangeValue as Variant
    ChangeValue = Application.GetOption("Confirm Record Changes")

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now