How to create a login system in Access?

Hi,

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?
squidlingsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

HTH,
JimD
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

JimD
0
 
squidlingsAuthor Commented:
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?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

HTH,
JimD

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

Change_Bye:
    Exit Function

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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
squidlingsAuthor Commented:
Thanks, you've been more than helpful.

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

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.