Setting the Startup Options for an Access 2007 database using VBA

[genegal]

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]

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

 

[genegal]

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]

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.

 

[Douglas J. Steele]

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.

 

[Jack Leach]

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]

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]

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?

 

[Stuart McCall]

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

 

[genegal]

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.

 

[Jack Leach]

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)

 

[Jack Leach]

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)

 

[David W. Fenton]

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?

 

[Jack Leach]

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)