Lockout Admin privileges in MS Access 2007

Posted on 2011-05-06
Last Modified: 2012-05-11
We have an application in MS Access 2007.  There are no access user accounts.  Instead the application is on a protected network share.   What I need is a way to prevent end users from going into administrative mode in the production copy of the file.
Question by:leeroypitre
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    When you say the "application is on a protected network share" do you mean the data, the application, or both?

    Generally, in a multi-user environment it is beneficial to split the application into two parts (Front-End and Backend).  The Front-end is where all of the forms, reports, queries, and code reside.  The backend contains only data.  The front-end should exist on the users individual PCs and it is best to save the file as an mde or accde file to prevent users from messing with code.  

    The backend should sit on the server and the tables in the back-end should be linked to the front-end.  The backend must be sitting in a folder on the server that everyone useing the front-end has read/write privileges to.  But that doesn't mean you have to tell the users where it is located.  There are no foolproof ways to prevent a user from opening an Access backend directly, although there are a number of techniques for "hiding" the backend from users.  If you really need to lock-down your data, my recommendation would be SQL Server or SQL Server Express (free).  

    LVL 26

    Expert Comment

    There are a bazillion google posting and EE questions that use this code

    Function ReturnUserName() As String
    ' returns the NT Domain User Name
    Dim rString As String * 255, sLen As Long, tString As String
        tString = ""
        On Error Resume Next
        sLen = GetUserName(rString, 255)
        sLen = InStr(1, rString, Chr(0))
        If sLen > 0 Then
            tString = Left(rString, sLen - 1)
            tString = rString
        End If
        On Error GoTo 0
        ReturnUserName = UCase(Trim(tString))
    End Function

    It originally comes from the Access Web

    It is used very simply
    Put it in a module, then use it like below

    Private sub WhoAreYou
        msgbox ReturnUserName
    end sub

    Now, you have the ability to detect the NT login name, think of the possibilities.
    The app, each form, each report--they all have open events

    Private myform_Open(cancel as integer)
    select case returnusername
        case "leeropitre"
           'do nothing
        case else
            Msgbox "sorry, not authorized"
            Cancel = true
        end select
    end sub

    Catch my drift?
    LVL 77

    Accepted Solution

    If you are using an accdb file then there is no longer a way of achieving this if users have a full copy of Access installed.
    You can do various things to discourage users by hiding the Navigation pane and removing ribbons and menus, disabling the Shift-Bypass key and converting the app into an accde file.

    This will be quite enough for 99.9% of users but if you have a knowledgable and meddling user then they can still find a way through all this.

    If you can install the Access runtime version on users' machines instead of full Access then you immediately disable all design features.  
    If you have not already done so then you should use a split application and put the backend tables into a hidden folder so that users don't find it while browsing through folders.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now