user level secrity using vb code in an access application

[jolly_83]

step1:- add a table named users with 3 fields named:- username,password,accesstype
fill username ,password of your choice and in access type fill 1 and 2.
for example:-

UserName Password AccessType
admin admin 1

regular regular 2

step 2:- create a module named intialization in modules, then open it and paste this code

Public Function InitApp()
AllowSpecialKeys = False
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
DoCmd.ShowToolbar "Database", acToolbarNo

Application.CommandBars("SALES SYSTEM MENU").Enabled = False

Application.SetOption "Enable MRU File List", False
Application.SetOption "Built-In Toolbars Available", False

End Function

step 3 :- add a form named login form, then in autoexe macro first write an action runcode, give function name initapp(). now write action open form
and give the login form name.

step 4 :- in login form's design view add two text boxes , one for username and another for password and add one command button named login

step 5:- right clk on login button and clk properties, now move to event tab, there choose on clk event and write event procedure in it. now clk it vb code window will open , paste this code in it.


Private Sub cmdLogin_Click()

If IsNull(Me.UserName) Or Me.UserName = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.UserName.SetFocus
Exit Sub
End If

If IsNull(Me.password) Or Me.password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.password.SetFocus
Exit Sub
End If

Dim access


access = DLookup("AccessType", "Users", "[UserName]='" & Me.UserName.Value & "' And [Password]='" & Me.password.Value & "'")
If access = 1 Then
Application.SetOption "Built-In Toolbars Available", True
DoCmd.RunMacro "ADMIN LOGIN", , ""
DoCmd.ShowToolbar "Database", acToolbarYes
DoCmd.SelectObject acTable, , True

Application.CommandBars("SALES SYSTEM MENU").Enabled = True
Application.SetOption "Can Customize Toolbars", True
Application.SetOption "Control Wizards", True


ElseIf access = 2 Then
DoCmd.RunMacro "ADMIN LOGIN", , ""
DoCmd.ShowToolbar "Database", acToolbarYes
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
Application.CommandBars("SALES SYSTEM MENU").Enabled = True
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.password.SetFocus
End If

End Sub

step 6:- only thing that u have to do is just how to merge these macro in your application. be carefull regarding sequence of execution starting from autoexe macro.

This code is 100% working in my application.
for any more query you can e-mail me on akhil.chopra@live.com.

 

[KernelK]

Why wouldn't you just use the built in MS User Level Security for Access and use ADOX to get permission information that is used by code? Your code requires that the code runs before the security works, that can be bypassed. The built in Security for access requires a username/password before the DB is even opened, much harder to get around if done properly.

 

[naungsai]

Why wouldn't you just use the built in MS User Level Security for Access and use ADOX to get permission information that is used by code? Your code requires that the code runs before the security works, that can be bypassed. The built in Security for access requires a username/password before the DB is even opened, much harder to get around if done properly.

Do you have an example file? I am a newbie. I do want to secure my Access File with user level. Thanks in advance.

 

[naungsai]

step1:- add a table named users with 3 fields named:- username,password,accesstype
fill username ,password of your choice and in access type fill 1 and 2.
for example:-

UserName Password AccessType
admin admin 1

regular regular 2

step 2:- create a module named intialization in modules, then open it and paste this code

Public Function InitApp()
AllowSpecialKeys = False
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
DoCmd.ShowToolbar "Database", acToolbarNo

Application.CommandBars("SALES SYSTEM MENU").Enabled = False

Application.SetOption "Enable MRU File List", False
Application.SetOption "Built-In Toolbars Available", False

End Function

step 3 :- add a form named login form, then in autoexe macro first write an action runcode, give function name initapp(). now write action open form
and give the login form name.

step 4 :- in login form's design view add two text boxes , one for username and another for password and add one command button named login

step 5:- right clk on login button and clk properties, now move to event tab, there choose on clk event and write event procedure in it. now clk it vb code window will open , paste this code in it.


Private Sub cmdLogin_Click()

If IsNull(Me.UserName) Or Me.UserName = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.UserName.SetFocus
Exit Sub
End If

If IsNull(Me.password) Or Me.password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.password.SetFocus
Exit Sub
End If

Dim access


access = DLookup("AccessType", "Users", "[UserName]='" & Me.UserName.Value & "' And [Password]='" & Me.password.Value & "'")
If access = 1 Then
Application.SetOption "Built-In Toolbars Available", True
DoCmd.RunMacro "ADMIN LOGIN", , ""
DoCmd.ShowToolbar "Database", acToolbarYes
DoCmd.SelectObject acTable, , True

Application.CommandBars("SALES SYSTEM MENU").Enabled = True
Application.SetOption "Can Customize Toolbars", True
Application.SetOption "Control Wizards", True


ElseIf access = 2 Then
DoCmd.RunMacro "ADMIN LOGIN", , ""
DoCmd.ShowToolbar "Database", acToolbarYes
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
Application.CommandBars("SALES SYSTEM MENU").Enabled = True
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.password.SetFocus
End If

End Sub

step 6:- only thing that u have to do is just how to merge these macro in your application. be carefull regarding sequence of execution starting from autoexe macro.

This code is 100% working in my application.
for any more query you can e-mail me on akhil.chopra@live.com.

I think I need to replace ("SALES SYSTEM MENU") with my own file. Can you please share your file.

 

[jolly_83]

Why wouldn't you just use the built in MS User Level Security for Access and use ADOX to get permission information that is used by code? Your code requires that the code runs before the security works, that can be bypassed. The built in Security for access requires a username/password before the DB is even opened, much harder to get around if done properly.

anybody can break ms-access's build in secrity.
another reason is i can customize a user's access rights, this can also be done in build in security but not that much.

 

[jolly_83]

I think I need to replace ("SALES SYSTEM MENU") with my own file. Can you please share your file.

yes u need to do that, sorry cant share my file. becoz of some personal stuff

 

[naungsai]

yes u need to do that, sorry cant share my file. becoz of some personal stuff

Can you review my file and fix it?

 

[jolly_83]

Can you review my file and fix it?

do you have yahoo or gmail id... come there

mine is akhilchopra07@yahoo.com

akhilchopra07@gmail.com

 

[garyholc]

Problem is, could your security be bypassed by a user holding down the shift key when loading the database?

 

[jolly_83]

use this attachment to avoid bypass key problem.

i can solve it using vb code too

 

[jolly_83]

Do you have an example file? I am a newbie. I do want to secure my Access File with user level. Thanks in advance.

yes i do have,,, but its a big project,,
no worries i will prepare one for you give me ur mail id.

 

[naungsai]

My mail is naungsai@gmail.com. Thanks in advance.

 

[KernelK]

anybody can break ms-access's build in secrity.
another reason is i can customize a user's access rights, this can also be done in build in security but not that much.

I must disagree that "anybody" can break the built in User-Level security in Access. It is definitely a large step above the average users knowledge level and can be very difficult even for the professional if implemented properly.

Also, even if you lock down the shift bypass, if you are not using an workgroup file to administer rights what stops someone from just creating a blank DB and then importing all of the objects, thus circumventing all of your security measures?

 

[SocratesF]

I must disagree that "anybody" can break the built in User-Level security in Access. It is definitely a large step above the average users knowledge level and can be very difficult even for the professional if implemented properly.

Also, even if you lock down the shift bypass, if you are not using an workgroup file to administer rights what stops someone from just creating a blank DB and then importing all of the objects, thus circumventing all of your security measures?

Well, I recently found a program that finds out in no time the usernames and passwords from a mdw file no matter how strong the passwords are . Needless to say that I am completely dissapointed with Access security...