Provided by Allen Browne. Created January 2007. Last updated January 2008
If you develop databases for others, you don't want them renaming fields, deleting fields, and adding new ones. But if a user opens your database using Access 2007, they can do exactly that. Access 2007 lets them make schema changes in Datasheet view unless you disable this property.
You probably don't want users modifying your forms and reports either. Again, the new Layout View - on by default - lets them to do that.
The code below creates properties in your database so that users with Access 2007 cannot wreck your database like that. You can use the code in your existing version, i.e. you can protect your databases even if you do not have the new version yet.
December 2007 update: You may also want to add the line:
This prevents users deleting or renaming objects in the Navigation Pane. We do not include this by default, as it could confuse and frustrate existing developers. To unlock the Navigation Pane again, open the Immediate Window (Ctrl+G) and enter:
The crucial properties to set are AllowDatasheetSchema and DesignWithData. While you are at it, you can also define other properties as well, such as whether you want Access 2007 users to see your forms, reports, etc using tabbed window, or if you prefer to stay with the overlapping windows (like current versions.) You can also set your preferences for the Navigation Pane (replacement for the Database Window), compatibility, and existing issues.
|Crucial||AllowDatasheetSchema||Boolean||False||Use False to prevent schema changes in Datasheet view. (Default is True.)|
|DesignWithData||Long||0||Use 0 to block Layout View in forms and reports. (Default is 1.)|
|Child windows||UseMDIMode||Byte||0||Use 0 for tabbed windows, or 1 for overlapping windows. (Default is 1.)|
|ShowDocumentTabs||Boolean||True||Use 1 to show the tabs in the tabbed window view. (Default is True.)|
|Navigation pane||NavPane Category||Long||0||Use 0 to group the Navigation Pane by object type.|
|NavPane View By||Long||0||Use 0 to view the Navigation Pane as a list. (Default is 0.)|
|NavPane Sort By||Long||0||Use 0 to sort items in the Navigation Pane by name. (Default is 1, i.e. by Type.)|
|Show Navigation Pane Search Bar||Long||1||Use 1 to show the Search bar in the Navigation Pane (Default is 0.)|
|Compatibility||CheckTruncatedNumFields||Long||0||Use 0 to show that part of the number that fits, or 1 to show #### as Excel does. (Default is 0 for converted databases, 1 for new databases.)|
|Picture Property Storage Format||Long||1||Use 1 so graphics inserted by Access 2007 users are converted to bitmap and are readable by users with earlier versions. (Default it 1 for converted databases, 0 for new databases.)|
|Existing issues||Perform Name AutoCorrect||Long||0||If you are not aware of this issue, see Failures caused by Name Auto-Correct. (Default is 1 in all databases from Access 2000 on.)|
|Track Name AutoCorrect Info||Long||0|
|Auto Compact||Long||0||You probably want this off, and there may be an Access 2007 bug here.|
Assign the constants at the top of the module for the values you wish to set.
To use the code, copy it into a module in a database, make sure you have a reference to Microsoft DAO 3.6, and check the code compiles (Compile on Debug menu.) Then open the Immediate Window (Ctrl+G), and apply to all databases in the \Dev folder like this:
? PrepareAllFor2007("C:\Dev", "*.mdb")
To set the properties of just the current database, copy the code into the database, and enter this in the Immediate Window:
Option Compare Database Option Explicit 'Purpose: Modify existing Access database files to prevent bad modifications if clients open them with Access 2007. 'Usage: To modify one file, import the code, and type this in the Immediate window: ' ? PrepareDbFor2007) ' To modify all MDB files in C:\MyFolder: ' ? PrepareAllFor2007("C:\MyFolder", "*.MDB") 'Notes: When a database is opened in Access 2007, users can add, delete, and rename fields in Datasheet view, ' They can also modify forms and reports in Layout view. ' This code sets the properties of the database so these new features are disabled. ' It illustrates how to set other optional preferences as well. 'Version: Run the code in Access 2000 or later. ' (It works in Access 2007, but you do not need the new version to set these properties.) 'Limits: Not designed for secured or replicated databases. 'Author: Allen Browne (firstname.lastname@example.org), January 2007. '******************************************* 'Set these constants to the values you want: '******************************************* 'Don't allow fields to be added, deleted or renamed in Datasheet view.(Default is Allow.) Private Const mbcAllowSchemaChanges As Boolean = False 'Don't allow users to modify design of forms and reports using the new Layout view. (Default is Allow.) Private Const mlngcAllowLayoutView As Long = 0& 'Disable Name AutoCorrect. Avoid bugs listed at http://allenbrowne.com/bug-03.html Private Const mlngcPerformNameAutoCorrect As Long = 0& Private Const mlngcTrackNameAutoCorrectInfo As Long = 0& 'Disable Auto Compact Private Const mlngcAutoCompact As Long = 0& 'Use the new tabbed interface for open windows. (Default is to use over-lapping windows.) Private Const mbtcUseOverlappingWindows As Byte = 0 Private Const mbcShowDocumentTabs As Boolean = True 'Show the Nav Pane by Object Type, viewed as a list, sorted by name, with the Search bar. Private Const mlngcNavPaneCategory As Long = 0& Private Const mlngcNavPaneViewBy As Long = 0& Private Const mlngcNavPaneSortBy As Long = 0& Private Const mlngcShowNavPaneSearchBar As Long = 1& 'Don't show #### for truncated numbers. (Default for converted databases, not new ones.) Private Const mlngcCheckTruncatedNumFields As Long = 0& 'Store as bitmaps for compatibility. (Default for converted databases, not new ones.) Private Const mlngcPictureStorageCompatibility As Long = 1& Public Function PrepareDbFor2007(Optional db As DAO.Database) As String 'Purpose: Set the properties of the database ready for Access 2007. 'Argument: Database to set. Currentdb if not database passed in. 'Return: Any warning messages if properties were not set. ' Zero-length string if no errors. Dim strMsg As String 'String to append error messages to. Dim bDbWasNothing As Boolean If db Is Nothing Then bDbWasNothing = True Set db = CurrentDb End If 'Essential changes. Call SetPropertyDAO(db, "AllowDatasheetSchema", dbBoolean, mbcAllowSchemaChanges, strMsg) Call SetPropertyDAO(db, "DesignWithData", dbLong, mlngcAllowLayoutView, strMsg) 'Existing properties that should be set anyway. Call SetPropertyDAO(db, "Perform Name AutoCorrect", dbLong, mlngcPerformNameAutoCorrect, strMsg) Call SetPropertyDAO(db, "Track Name AutoCorrect Info", dbLong, mlngcTrackNameAutoCorrectInfo, strMsg) Call SetPropertyDAO(db, "Auto Compact", dbLong, mlngcAutoCompact, strMsg) 'Preferences for child windows. Call SetPropertyDAO(db, "UseMDIMode", dbByte, mbtcUseOverlappingWindows, strMsg) Call SetPropertyDAO(db, "ShowDocumentTabs", dbBoolean, mbcShowDocumentTabs, strMsg) 'Preferences for the Navigation Pane. Call SetPropertyDAO(db, "Show Navigation Pane Search Bar", dbLong, mlngcShowNavPaneSearchBar, strMsg) Call SetPropertyDAO(db, "NavPane Category", dbLong, mlngcNavPaneCategory, strMsg) Call SetPropertyDAO(db, "NavPane View By", dbLong, mlngcNavPaneViewBy, strMsg) Call SetPropertyDAO(db, "NavPane Sort By", dbLong, mlngcNavPaneSortBy, strMsg) 'Settings that default correctly if you convert a database, but are different from new ones in A2007. Call SetPropertyDAO(db, "CheckTruncatedNumFields", dbLong, mlngcCheckTruncatedNumFields, strMsg) Call SetPropertyDAO(db, "Picture Property Storage Format", dbLong, mlngcPictureStorageCompatibility, strMsg) 'Clean up If bDbWasNothing Then Set db = Nothing End If 'Return any messages PrepareDbFor2007 = strMsg End Function Public Function PrepareAllFor2007(Optional ByVal strPath As String, Optional strFileSpec As String = "*.mdb") As Long 'Purpose: Set the properties for ALL databases matching the filespec. 'Argument: File specification such as "C:\MyFolder\*.mdb" ' You must include the extension. 'Return: Number of files modified. 'Note: There's no error handling. (Designed for developers.) Dim db As DAO.Database Dim strFile As String Dim strMsg As String Dim lngKt As Long If strPath = vbNullString Then strPath = CurDir$ End If strPath = TrailingSlash(strPath) strFile = Dir(strPath & strFileSpec) If strFile <> vbNullString Then strMsg = "You are about to modify the properties ALL files matching:" & vbCrLf & strPath & strFileSpec If MsgBox(strMsg, vbOKCancel + vbDefaultButton2 + vbQuestion, "PrepareAll()") = vbOK Then Do While strFile <> vbNullString Debug.Print strPath & strFile Set db = OpenDatabase(strPath & strFile) Call PrepareDbFor2007(db) db.Close Set db = Nothing lngKt = lngKt + 1& strFile = Dir Loop End If End If PrepareAllFor2007 = lngKt End Function Public Function ShowProps(obj As Object) On Error GoTo Err_Handler 'Purpose: Display the properties of the object in the immediate window. 'Example: In the Immediate Window: ' ? ShowProps(Currentdb) Dim prp As DAO.Property For Each prp In obj.Properties Debug.Print prp.Type, Debug.Print prp.Name, Debug.Print prp.Value; Debug.Print Next Set prp = Nothing Exit_Handler: Exit Function Err_Handler: Select Case Err.Number Case 3219, 3267, 3251 Resume Next Case Else MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "ShowProps()" Resume Exit_Handler End Select End Function Private Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As Integer, _ varValue As Variant, Optional strErrMsg As String) As Boolean On Error GoTo ErrHandler 'Purpose: Set a property for an object, creating if necessary. 'Arguments: obj = the object whose property should be set. ' strPropertyName = the name of the property to set. ' intType = the type of property (needed for creating) ' varValue = the value to set this property to. ' strErrMsg = string to append any error message to. If HasProperty(obj, strPropertyName) Then obj.Properties(strPropertyName) = varValue Else obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue) End If SetPropertyDAO = True ExitHandler: Exit Function ErrHandler: strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _ ". Error " & Err.Number & " - " & Err.Description & vbCrLf Resume ExitHandler End Function Private Function HasProperty(obj As Object, strPropName As String) As Boolean 'Purpose: Return true if the object has the property. Dim varDummy As Variant On Error Resume Next varDummy = obj.Properties(strPropName) HasProperty = (Err.Number = 0) End Function Private Function TrailingSlash(varIn As Variant) As String If Len(varIn) > 0 Then If Right(varIn, 1) = "\" Then TrailingSlash = varIn Else TrailingSlash = varIn & "\" End If End If End Function
|Home||Index of tips||Top|