How to create a login system in Access?

Posted on 2007-10-05
Last Modified: 2012-05-05

I don't actutally want to know details of how do make this Login, I just want to understand in my mind how to go about it.  i.e. a list of instructions like a flow chart.

I have a database I have written for work, it is on the network and soon it will require a user name login/password system.

At the minute it only needs user name & password but in the future I would like to add levels of access. i.e. some users can only read orthers can read and write.

I am aware of the inbuilt security system in access, but I have never used it.

Please can somebody give me a brief idea of how to setup this kind of system.

Am I best using the inbuilt access login system, is it better to have 1 written in VB code?

Where I start?

Do I make a table for user names and passwords?
Question by:squidlings
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Please can somebody give me a brief idea of how to setup this kind of system.>>

      User table and then a permissions table.  User table has name, password, accout disabled types of things.

     As for the permissions table, that can vary widely.  Generally folks go for a table that specifies the user, a tag (usually the module, such as "A/R") and then a security level from 0 - 9.

      In your menu system, each menu item should be in a group (ie. A/R module) and have a required security level (0-9).  

      When the user double clicks on the item, you check their security level for that group vs what's required.

      That's just one example.

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Am I best using the inbuilt access login system, is it better to have 1 written in VB code?>>

      FYI, for the type of thing you want, there really is nothing built into Access to do that.  And as of A2007, user level security has been dicontinued.  Better to build your own at this point.


    Author Comment

    Thanks for your advice, 2 more quick question.

    How secure is this kind of login system?  Or does that depend on your code?

    Is there a way to disable the "hold down shift when you open the application" feature?
    LVL 56

    Accepted Solution

    <<How secure is this kind of login system?  Or does that depend on your code?>>

      Not very because Access is not very secure.  That's one of the reasons they got rid of user level security.  Too complex and too easy to break.

      You'll be fine for the casual user and you can protect your code by distributing in the MDE format (all source code is stripped out), but someone will still be able to break the database and get at the underlying data.

    <<Is there a way to disable the "hold down shift when you open the application" feature?>>

        Yes.  You must set the AllowBypassShiftKey property.  Below is the code to set it.

        and as for the permissions setup, there are all kinds of schemes.  Sky's the limit.  One that I've used myself is to have a string of permissions something like this in the user table:

    '3623 456'

       each character representing one "slot" of permissions.  Then in my menu system, I have a menu group.  So AR might be group 1.  I then check the security level in the group by using:

      If Mid$(strSecurity,[GroupID],1) > rsmenu![RequiredLvl] then

      That's a little inflexable though if you want to control things down to the field level.  Then it really pays to have a permissions table with tags.  ie. might look like this:

    Tag                   Level
    frmCustomer     6
    frmVendor         4
    frmAddVoucher  9

      which means basically that you can set a security level on an object by object basis or represent a process (ie. a month end close):

    Tag                   Level
    PRMonthEnd     9
    APMonthEnd     0
    ARMonthEnd     9

      So this user could run PR and AR month ends, but not the AP.  Like I said, lots of ways to do this.  Really depends a lot on the app and how sophisticated you want to get.


    Sub SetBypassProperty()
    Const DB_Boolean As Long = 1
        ChangeProperty "AllowBypassKey", DB_Boolean, False
    End Sub

    Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
        Dim dbs As Object, prp As Variant
        Const conPropNotFoundError = 3270

        Set dbs = CurrentDb
        On Error GoTo Change_Err
        dbs.Properties(strPropName) = varPropValue
        ChangeProperty = True

        Exit Function

        If Err = conPropNotFoundError Then    ' Property not found.
            Set prp = dbs.CreateProperty(strPropName, _
                varPropType, varPropValue)
            dbs.Properties.Append prp
            Resume Next
            ' Unknown error.
            ChangeProperty = False
            Resume Change_Bye
        End If
    End Function

    Author Comment

    Thanks, you've been more than helpful.

    At least I know a little more about the strenghts and weaknesses of access now.

    Thanks again.

    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

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    728 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