Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Created January 2007. Last updated January 2008

Prevent Access 2007 users modifying existing databases

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:
    DoCmd.LockNavigationPane True
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:
    DoCmd.LockNavigationPane False

The properties

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.

Assign the constants at the top of the module for the values you wish to set.

Usage examples

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:
    ? PrepareDbFor2007()

The code

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 (allen@allenbrowne.com), 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