asked on

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?

Mavreich

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

BevinManian

ASKER

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

Mavreich

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

BevinManian

ASKER

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

Arji

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

ASKER CERTIFIED SOLUTION

IceManEwok

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial