Username and password protecting a database 2

[Paulsly]

Hi all
I have just designed a database for my company. but Im having a problem setting up a login screen for users
I don't want to use the normal USER and GROUP permissions
That Access offers. I acctualy want to setup my own permissions and rights for users.
I have created a table with the usernames, passwords and level of security.
Then I created a form with to text boxes. I called them Name and Password and 2 command buttons 1 to open the main menu the other to close access
I need some code to allow the main menu to open only if the user name and password is correct or give a message if it is wrong
Can someone please help me as I am getting fustrated with this
Regards Paul

 

[KenReay]

Hi

Leaving aside the question of why you are re-inventing the wheel:

Not sure which bits you are having difficulty with, or which version of access you are using, or if you are using ADO or DAO, but the following untested code should be something like you need, assuming you are using ADO

Assuming your form has two text boxes txtUser and txtPassword

Have a button on the form with code so:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'
If IsNull(txtUser) Then
MsgBox "Please Enter UserId"
txtUser.SetFocus
exit sub
end If
'
If IsNull(txtPassword) Then
msgBox "Please enter Password"
txtPAssword.setfocus
exit sub
end If
'
set cn = currentproject.connection
set rs = new adodb.connection
rs.cursorlocation = adUSeClient
rs.open "SELECT * FROM tblUSers WHERE UserId = '" & txtUserId & "';"
If Rs.RecordCount = 0 then
msgBox "Invalid User Id"
Rs.Close
set rs = nothing
set cn = nothing
Application.quit
Else
If Rs!PAssword <> txtPassword THen
MsgBox &quot;Invalid PAssword&quot;
Rs.Close
Set Rs = Nothing
Set Cn = NOthing
Application.Quit
Else
Rs.Close
Set Rs = Nothing
set cn = nothing
docmd.openform ...etc
End If
End If
DoCmd.OPenForm ....etc form name etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Paulsly]

Thanks for replying
I copy your code and changed all the names to suit my field names and it came up with a compile error and high lighted the first line (cn As adodb.Connection) maybe theres an easyer way.
On my form there is a section for normal staff to fill in and a section for branch managers to fill in .The reason I was doing it this way, was that on my security table that I have created I gave levels for all the stall so Admin would be level 1. Branch Managers Level 2 and Normal Staff level 3 then The section on the form for Branch Managers I would insert a bit of code saying that if current users level is <= to level 2 then set those fields visible to true so any normal staff would not beable to insert or see those fields when logged on If this could be done with the Access security options then Great It would sve me alot of time
Do you have any Ideas
Thanks Again Paul
PS Im using Access 97

 

[KenReay]

Hi

If you are using Access97, you are using DAO

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

should be

Dim Db As Database
Dim Rs as Recordset

set cn = currentproject.connection
set rs = new adodb.connection
rs.cursorlocation = adUSeClient
rs.open &quot;SELECT * FROM tblUSers WHERE UserId = '&quot; & txtUserId & &quot;';&quot;

should be

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset(&quot;SELECT * FROM tblUSers WHERE UserId = '&quot; & txtUserId & &quot;';&quot

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Paulsly]

It works when I have the table linked to the form
but the problem is then users can view each others usernames and passwords
Is there a way just to have 2 text boxes and then link those to the form
Thanks so much for all your help
Regards Paul

 

[KenReay]

Hi

That is what I described doing Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Paulsly]

Hi Ken Reay
I played around with a few things and got it to work fine
Thanks so much
on the normal access security theres a option to use current user but the way Im doing it these not
I know I'm starting to pick your brain abit to much here but my vba skills are not that good
so if you dont mind helping me I would be greatly thankful
I have heard of a way by using a global variable the uses the peasons name that logged on in the forms and also could be saved onto the table for that record they were working on
Do you know much about this as I searched the Internet last night and I could not find much on this
Thanks again
Paul

 

[KenReay]

HI

You are talking about CurrentUser() function, which returns the current user name as logged in under Access security, since you are not using Access Security, this will return &quot;Admin&quot;.

It is possible to retrieve the Network User Id using the following Function

Private Declare Function apiGetUserName Lib &quot;advapi32.dll&quot; Alias _
&quot;GetUserNameA&quot; (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = &quot;&quot;
End If
End Function

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK