Current Database
I would like to have a cmd button I can use to change the 'Current Database' settings in my Access DB. Every time I replace the production version of the DB with the developement version I have to open Access' options and change the following settings to block the users from accessing things I do not want them to. Use Access Special Keys - unchecked.
Enable Layout View for this Database - unchecked. Enable design changes for tables in Database view (for this database) - unchecked. Allow Full Menues - unchecked. Allow Default Shortcut Menues - unchecked. Allow Built-in Toolbars - unchecked Thanks, Jon.
You'd need to do this BEFORE you deploy your new updates; while you can change these properties when you database starts, those changes don't come into effect until the NEXT time the db is run. I just have a simple codeblock that sets these things, and I run it right before I build the.mde for deployment. Something like this: Function SetupForDeploy As Boolean ChangePropertyDDL 'AllowFullMenus', dbBoolean,False ChangePropertyDDL 'AllowToolbarChanges', dbBoolean, False ChangePropertyDDL 'AllowSpecialKeys', dbBoolean, False etc etc End Function So I just call SetupForDeploy from the Immediate window (along with a few other things) and then build the mde.
The database properties are: AppTitle, AppIcon, StartupShowDBWindow, StartupShowStatusBar, AllowShortcutMenus, AllowFullMenus, AllowBuiltInToolbars, AllowToolbarChanges, AllowBreakIntoCode, AllowSpecialKeys, Replicable, ReplicationConflictFunctio n Along with any custom Properties you have set. Function ChangePropertyDdl(stPropName As StringPropType As DAO.DataTypeEnum, vPropVal As Variant, Optional dbs As DAO.Database) As Boolean ' Uses the DDL argument to create a property ' that only Admins can change.
' ' Current CreateProperty listing in Access help ' is flawed in that anyone who can open the db ' can reset properties, such as AllowBypassKey ' On Error GoTo ChangePropertyDdlErr Dim db As DAO.Database Dim prp As DAO.Property Const conPropNotFoundError = 3270 Const conItemNotFoundError = 3265 If dbs Is Nothing Then Set db = CurrentDb Else Set db = dbs End If 'If dbs Is Nothing ' Assuming the current property was created without ' using the DDL argument. Delete it so we can ' recreate it properly db.Properties.Delete stPropName Set prp = db.CreateProperty(stPropNamePropType, vPropVal, True) db.Properties.Append prp ' If we made it this far, it worked! ChangePropertyDdl = True ChangePropertyDdlExit: Set db = Nothing Set prp = Nothing Exit Function ChangePropertyDdlErr: If Err.Number = conPropNotFoundError Or Err.Number = conItemNotFoundError Then ' We can ignore when the prop does not exist Resume Next Else Select Case Err.Number Case 3385 GoTo ChangePropertyDdlExit Case Else MsgBox Err.Description & vbCrLf & vbCrLf & Err.Number End Select End If 'If Err.Number = conPropNotFoundError Or Err.Number = conItemNotFoundError Resume ChangePropertyDdlExit End Function Select all. Setting most of the properties you meentioned are pretty sraight forward. Turning the ones I could idntify on and off are detailed below. The two properties that were not so clear were as follows.
Enable Layout View for this Database - unchecked and. Enable design changes for tables in Database view (for this database) - unchecked The first appears to be a property that belongs to every form.
If correct then you would need some code to open every form in design view, change this property and then save the changes. The 2nd appears to be a securities and permissions request that affects the.mdw of your database.
Postgresql Change Current Database
While doable this adds a significant layer of complexity to your application. So much so that you may want to consider how important this feature is and whether a work around might satisfy your needs equally well.
As for the rest it's simply a matter of adding the code to a module below and then running them from anywhere within the application. Since TurnOnSpecialKeys and TurnOffSpecialKeys are both functions they can be run equally well from any form (splash screen if you'd like) or other button within the application you'd like to run them from. Simply putting. =TurnOffSpecialKeys into the OnClick event of a command button for example would run that code. Hope that helps. Assuming you'll have some form for doing this.
Just add a Command button, then place the code in the Click Event. Basically, just copy everything between the 'Function' and 'End Function' tags, and drop it into your sub: Sub MyButtonClick ChangePropertyDDL 'AllowFullMenus', dbBoolean,False ChangePropertyDDL 'AllowToolbarChanges', dbBoolean, False ChangePropertyDDL 'AllowSpecialKeys', dbBoolean, False End Sub Also, add the ChangePropertyDDL function to a Standard Module.
From your db window, click MOdules, then New. Copy/paste the ENTIRE ChangePropertyDDL function into the new window in your VB Editor. Name it something (anything other than ChangePropertyDDL, use something like basRoutines to name the module). Adding this code to a Standard Module allows you to call it from anywhere in your project.
Sorry, sometimes I'm a little slow:) so you'll have to expand more on what you want me to do. I tried different combinations of the below, and nothing seems to work. I created a Cmd button in my form, and put this in the below code LockDBChanges in the event. I also created a module named modLockDBForEditing with the below code in it. When i click on the button I get the following error: - - - - - - - - - - - Compile error: Sub or Function not defined Then it highlights (ChangePropertyDDL) - - - - - - - - - - - Thanks for the help. Sorry, I see where I was getting confused; I wasn't paying attention to the differences in Rick's and LSMConsulting's code. However, I'm still getting the same Compile error: 'Sub or Function not defined' The module I created is named modLockDatabaseEditing The code I have in the event procedure of my command button is named cmdLockDatabaseEditing What am I doing wrong?
Sorry for dragging this out; I promise to reply if I receive feedback today. No, name of your module is fine.
You should never name a module the same as a Procedure in that module (or any other module) else you'll get an error. Do you actually have a function named ChangePropertyDDL in your module? If not, you need to copy the code snippet I included in my first comment into your modLockDatabaseEditing module. Try this for the Click event of your button: Private Sub cmdLockDatabaseEditingCl ick SetupForDeploy End Sub Also, you mention this: 'Everything in my DB and what I have seen before starts with 'Private Sub' not 'Function'.' A Sub and Function are pretty similiar; the only difference is a Function returns a value.
In many cases (as in mine) developers use Function exclusively. Prefacing either of these with 'Private' means that you can run those procedures ONLY in the scope of their host container. For example, your Click procedure for cmdLockDatabaseEditing is marked as Private; this is typical of object events like Click, since these are only run when your button is clicked (and thus your Form must be open); if you attempted to call a Private Sub or Function from outside the host container, you'd get a 'Not Found' error.
By default, procedures are Public, so technically prefacing with Public is not necessary. Also, I'd STRONGLY recommend that you NOT directly call Functions in the Event Procedure, as recommended by Rick: '=TurnOffSpecialKeys into the OnClick event of a command button for example would run that code.' This will get you into trouble with deployments, since whether this will run or not depends on the security level of Office/Access. Besides, it's just not that much trouble to put the code directly in the Click event. And it saves you a ton of headache down the road.
Thank you very much for all you help LSMConsulting, and for the nice explanation:) I guess what was/is confusing me is that you have two different functions in your first post. You gave me two example of functions for the module; the first example is in the text of your answer, and the second example is in the attached code snippet portion of your answer, and I get two different errors when I try them. One of the functions is named SetupForDeploy and the other one is named ChangePropertyDdl. Do I need to use both of them in the modLockDatabaseEditing module, or just one of them. I'm really sorry this is taking me so long to catch on, but I am thankful for your help.
If you can, please just post the exact code you want me to put in the modLockDatabaseEditing module, and the exact code I should put in the form for the cmdLockDatabaseEditing button, as I have listed below. Thanks again, Jon. Form Button - Private Sub cmdLockDatabaseEditingClick SetupForDeploy End Sub -. Module - Function SetupForDeploy As Boolean ChangePropertyDdl 'AllowFullMenus', dbBoolean, False ChangePropertyDdl 'AllowToolbarChanges', dbBoolean, False ChangePropertyDdl 'AllowSpecialKeys', dbBoolean, False End Function - Select all.
Here is the code all in one place for the next person that may need to know how to do this. I'll put the code for the command button in this box, and the code for the module in the code snippet below. Thanks again to all of you for your help. As you can see, I added a second button to unlock the DB options.