Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Access 2002 Options settings thru Code/Macros

Hi!
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?

0
BevinManian
Asked:
BevinManian
1 Solution
 
MavreichCommented:
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
Mavreich
0
 
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.

Bev
0
 
MavreichCommented:
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

Change_Bye:
    Exit Function

Change_Err:
    If Err = conPropNotFoundError Then  ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' 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.

Regards
Mavreich





0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Bev
0
 
ArjiCommented:
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"

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

0
 
IceManEwokCommented:
   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")
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now