Access 2002 Options settings thru Code/Macros

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?

Who is Participating?
   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")
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
BevinManianAuthor Commented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.


BevinManianAuthor Commented:
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...

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.