I am using MS Access 2007.
I have a database that I do all my editing and programming in and have certain Access options (under the Current Database tab) set one way during the editing. However, when I compile the database into the .accde file, I reopen the database and set certain options, such as the initial display form and navigation pane options. I keep these enabled during programming for ease of use, but disable them whem compiled to prevent others from possibly using them (or at least making it more difficult).
Is it possible to use VBA to set these options to automate this process after the compile? I already have code re-mapping all my linked tables and modifying/deleting unused Switchboard items.
Does anyone have any experience with this?Thanks!
I think you are referring to the StartUp Properties of a Database and yes, they can be modified but it is a little tricky since they are User Defined Properties and cannot be Set/Retrieved in the conventional manner. If the Property exists, you can directly modify it, if not you must create it then Append it to the proper Collection. The Code below will set the Allow Full Menus Start Up DB Option to False: - Function Definition:
- Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
-
On Error GoTo Err_ChangeProperty
-
Dim MyDB As DAO.Database
-
Dim MyProperty As DAO.Property
-
-
Set MyDB = CurrentDb()
-
-
'Property exists, so set its Value
-
MyDB.Properties(strPropertyName) = varPropertyValue
-
ChangeProperty = True
-
-
Exit_ChangeProperty:
-
Exit Function
-
-
Err_ChangeProperty:
-
If Err.Number = 3270 Then 'Property not found
-
'Since the Property isn't found, create it!
-
Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
-
MyDB.Properties.Append MyProperty
-
Resume Next
-
Else
-
'Unknown Error
-
ChangeProperty = False
-
Resume Exit_ChangeProperty
-
End If
-
End Function
- Function Call:
- ChangeProperty "AllowFullMenus", dbBoolean, False
13 14612
I think you are referring to the StartUp Properties of a Database and yes, they can be modified but it is a little tricky since they are User Defined Properties and cannot be Set/Retrieved in the conventional manner. If the Property exists, you can directly modify it, if not you must create it then Append it to the proper Collection. The Code below will set the Allow Full Menus Start Up DB Option to False: - Function Definition:
- Private Function ChangeProperty(strPropertyName As String, varPropertyType As Variant, varPropertyValue As Variant) As Integer
-
On Error GoTo Err_ChangeProperty
-
Dim MyDB As DAO.Database
-
Dim MyProperty As DAO.Property
-
-
Set MyDB = CurrentDb()
-
-
'Property exists, so set its Value
-
MyDB.Properties(strPropertyName) = varPropertyValue
-
ChangeProperty = True
-
-
Exit_ChangeProperty:
-
Exit Function
-
-
Err_ChangeProperty:
-
If Err.Number = 3270 Then 'Property not found
-
'Since the Property isn't found, create it!
-
Set MyProperty = MyDB.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
-
MyDB.Properties.Append MyProperty
-
Resume Next
-
Else
-
'Unknown Error
-
ChangeProperty = False
-
Resume Exit_ChangeProperty
-
End If
-
End Function
- Function Call:
- ChangeProperty "AllowFullMenus", dbBoolean, False
Thanks ADezii,
BRILLIANT! That worked perfectly for turning off full menus. However, where does one find the names of these other properties, such as Allow Default Shortcut Menus and the like? I've tried some variations and nothing seems to work.
Also, how does one use VBA to tell the Database to open a specific form when it opens? Doing it manually is easy, but I have no clue where to start doing this in VBA....
Thanks for your help so far!
All the answers to your questions are listed below: -
Text in Startup Dialog Box Property name
-
-------------------------------------------------------------
-
Application Title AppTitle
-
Application Icon AppIcon
-
Display Form/Page StartupForm
-
Display Database Window StartupShowDBWindow
-
Display Status Bar StartupShowStatusBar
-
Menu Bar StartupMenuBar
-
Shortcut Menu Bar StartupShortcutMenuBar
-
Allow Full Menus AllowFullMenus
-
Allow Default Shortcut Menus AllowShortcutMenus
-
Allow Built-In Toolbars AllowBuiltInToolbars
-
Allow Toolbar/Menu Changes AllowToolbarChanges
-
Allow Viewing Code After Error AllowBreakIntoCode
-
Use Access Special Keys AllowSpecialKeys
ADezii,
Thank ou again! I wish MS made this stuff more readily available. I was certain I had already tried some of those properties but they didn't work. This time, works like a charm!
Warmest regards!
@twinnyfo:
It is not at all intuitive on how you can Set/Retrieve these Properties.
Thank you too, ADezii.
This post is just to subscribe.
By the way: Can I subscribe to a thread without posting something in that thread (and disturb the others) ?
@Mihail:
Can I subscribe to a thread without posting something in that thread (and disturb the others)?
I actually not 100% sure, but the person to direct this Question to would be NeoPa. Send him a Private Message with this same Question, and I'm sure he will reply to you.
NeoPa 32,556
Expert Mod 16PB
Not currently, but there used to be such a facility and we expect there to be such again in a future version of the site :-)
NeoPa 32,556
Expert Mod 16PB
That was so useful that I decided to create a module specifically to support working with Properties in Access. Reading, writing, adding and deleting are all supported, and even though the VarType() function returns values in a similar but incompatible set from the dbXXX type values required by CreateProperty(), I thought it was worthwhile to handle that with a little conversion function that handles the most common and those likely to be used with property values.
The code all seems to work : - Option Compare Database
-
Option Explicit
-
-
Private Const conNoProp As Integer = 3270
-
Private Const conVBToDB As String = "\2|3\3|4\4|6\5|7\6|5" & _
-
"\7|8\8|10\11|1\14|20\17|2"
-
-
'SetProperty() requires that either intPType is set explicitly OR that
-
' varPVal has a valid value if a new property is to be created.
-
Public Sub SetProperty(strPName As String, varPVal As Variant, _
-
Optional ByVal db As DAO.Database, _
-
Optional intPType As Integer = -1)
-
Dim prp As DAO.Property
-
-
If db Is Nothing Then Set db = CurrentDb
-
If PropertyExists(strPName, db) Then
-
db.Properties(strPName) = varPVal
-
Else
-
If intPType = -1 Then intPType = DBVal(VarType(varPVal))
-
Set prp = db.CreateProperty(strPName, intPType, varPVal)
-
Call db.Properties.Append(prp)
-
End If
-
End Sub
-
-
Public Function GetProperty(ByRef strPName As String, _
-
Optional ByVal db As DAO.Database) As Variant
-
If db Is Nothing Then Set db = CurrentDb
-
If PropertyExists(strPName, db) Then GetProperty = db.Properties(strPName)
-
End Function
-
-
Public Function PropertyExists(ByRef strPName As String, _
-
Optional ByVal db As DAO.Database) As Boolean
-
Dim varTest As Variant
-
-
On Error GoTo Err_PropertyExists
-
If db Is Nothing Then Set db = CurrentDb
-
PropertyExists = True
-
varTest = db.Properties(strPName)
-
Exit Function
-
-
Err_PropertyExists:
-
If Err <> conNoProp Then
-
On Error GoTo 0
-
Resume
-
End If
-
PropertyExists = False
-
End Function
-
-
Public Sub DelProperty(ByRef strPName As String, _
-
Optional ByVal db As DAO.Database)
-
If db Is Nothing Then Set db = CurrentDb
-
If Not PropertyExists(strPName, db) Then Exit Sub
-
Call db.Properties.Delete(strPName)
-
End Sub
-
-
Private Function DBVal(intVBVal) As Integer
-
Dim intX As Integer
-
-
intX = InStr(1, conVBToDB, "\" & intVBVal & "|")
-
DBVal = Val(Mid(conVBToDB, intX + Len(intVBVal) + 2))
-
End Function
PS. Congrats to ADezii for helping to make such a complicated process into one that's now quite straightforward. With all this information such work should be a doddle in future.
NeoPa 32,556
Expert Mod 16PB
Thank you ADezii. My aim was not as limited as simply answering this thread (although that was certainly the starting point). I saw this was something that could be more generally helpful (to me as well as others) with just a little more meat on the bones. I expect this module to be available for my use in all future projects.
NeoPa 32,556
Expert Mod 16PB
I'm so glad I found this again. It made creating just such a module again so much easier :-D
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sateesh |
last post by:
Hi,
Is it possible to access Lotus notes using Python? Can anyone provide me
some pointers?
Thanks
Sateesh
|
by: TechBoy |
last post by:
Hello.
Re: Access 2002-SP3
I have some options turned off under "Tools/Startup" area on my
workstation. When I deploy my app to 5 users (who will all have a full
blown version of Access 2002...
|
by: stueyh |
last post by:
When attempting to connect to an Access 2000 DB using ADO.Net from within
ASP.Net running under an impersonated local user account receiving the
following error.
ERROR Disk or network
error....
|
by: silverburgh.meryl |
last post by:
I am currently access this newsgroup thru Google group web interface.
Is it possible to access this newsgroup using Thunderbird?
Thank you.
|
by: Gerrit Snel |
last post by:
We have a question but we're in doubt if it's even possible or not.
The question is as follows:
Is it possible to access a form using a string literal, and if so yes?
We want to use...
|
by: Andy |
last post by:
Hi, I was wondering if someone could advise on if the following is
possible. I have one website that is available to the public using
Forms authentication and the Membership class, it...
|
by: pratcp |
last post by:
I have a remote server (a shared hosting webserver) where I have few
users ftp some flat files. I need to access these files using a windows
app/windows service written using c# and run it on a...
|
by: DebbyS |
last post by:
Hi there,
I am currently in the situation where I have a database secured with startup options and passwords, but need to edit the startup options from time to time.(These have been hidden). ...
|
by: JodiPhillips |
last post by:
MS Access2000
Hi everyone,
I've searched the forums for an answer to this question and nothing jumps out at me.
When a database is opened I want to automatically run code (at start-up) that...
|
by: Bhuwan Bhaskar |
last post by:
Hi,
Can we access database (SQL) using AJAX ?
Thanks,
Bhuwan
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |