How to create a login system in Access?

Posted on 2007-10-05
Medium Priority
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
  • 3
  • 2
LVL 58
ID: 20022085
<<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 58
ID: 20022101
<<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

ID: 20022131
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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 20022261
<<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

ID: 20022320
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

750 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