Logging in only once

[Griff389]

I have a database and would like the users to only login once.

I have a module to get the user name already, and it is referenced as NTDomainUserName.
I'd like my startup form to look into my "tblUserlist" table which has only one field called "Username", and see if the username exists already. If it doesn't, then add the username to it and do whatever is next. If it does already exist, then bring up a message box and go no further.

(Naturally i'll have another section of code to check if the name exists and remove it when a user closes the database, but that's another question for later)

Can anyone give me a hand on writing this. I'm ok with forms and editing/adding code, but don't know the code or solution to do what i'm after unfortunately.

Cheers

 

[hneal98]

Why or how would a user log in more than once?

 

[Griff389]

I have a user that uses two different PC's in different parts of the building. But doesn't close the first copy of the DB down when they run it on the second machine, and I think it is doing weird things in the lock files causing some data issues occasionally. So I would like to rule this being a possible problem by seeing if they are already running a copy of the DB.

 

[hneal98]

It sounds like the user needs some educating.

There is code that detects the user who is currenlty logged in. You can use that code to get the user in the first attempt and then write that to the database and then when the user attempts to log in again, they would receive an error message stating they are already logged in and need to log out of the other one before continuing.

I am assuming that you have taken precausions to eliminate the Admin user name and replace it with a different name that is the new admin? If not, that would be an important step in your security. I know it is a little off topic, but important still.

The following code I had in an old app I created several years ago. You might be able to use part of it here. It was in the Form_Load event. You can see where I am getting the CurrentUser. That info you can take and write to your table. Let me know if something does not make sense or if I am missing the point...

    Dim Criteria As String, DB As Database, RecSet As Recordset
    Dim DocName As String
    Dim LinkCriteria As String

    Criteria = "[USERNAME] =" & (Chr(34) & CurrentUser() & Chr(34)) ' Define search criteria.
    

    Set DB = CurrentDb
    Set RecSet = DB!tblUserName.OpenRecordset(dbOpenDynaset)   ' Create Recordset.
    RecSet.FindFirst Criteria    ' Locate first occurrence.
    Region = RecSet!Region
    Dept = RecSet!Department

Select Case Dept  ' Test input.
    Case Is = "IS", "MG", "PR"
        Me![cmdMM].Enabled = True
        Me![cmdFN].Enabled = True
        Me![cmdMSCL].Enabled = True
        Me![cmdCR].Enabled = True
        Me![cmdNS].Enabled = True
        Me![cmdMSCL].Enabled = True
        Me![cmdPPCSum].Enabled = True
    Case Is = "MM"
        Me![cmdMM].Enabled = True
    Case Is = "FN"
        Me![cmdFN].Enabled = True
    Case Is = "CR", "PR", "NS", "CS"
        Me![cmdCR].Enabled = True
    Case Is = "NS", "PR", "CR", "CS"
        Me![cmdNS].Enabled = True
    Case Is = "MS", "CL"
        Me![cmdMSCL].Enabled = True
    Case Is = "NS"
        Me![cmdPPCSum].Enabled = True
End Select

Me.Refresh

You won't use the select case portion. I am just including it to give you an idea of how I handled the user being set up in groups. I created my own groups and put them in a table that was stored in a back-end database and refered to it when the user logged in and based on that group, they would have access to only certain parts of the application.

 

[easyit]

This will not work, because your code is executed after the login. The locking file already has the same user logged in twice.


EasyIT

 

[Griff389]

Easyit, They will use the shared lock file on the network for a brief spell while it checks the relevent table to see if they already exist, but they won't be using anything creating locks in my main tables, and it should only be for a brief spell til it closes the database down for them, so I should be ok hopefully.

hneal98, Cheers for that.
I think I see where you are coming from, and that should help me see if they already exist in the table, just need to work out how to add them into the table if they don't exist, and show a message if they do.

 

[hneal98]

Yes, but it can also close the data base with some code change. The user would need to log in to check the database in the first place.

 

[Griff389]

Admin won't be a problem as there's no db security set so all use the same user, I think that's how it works. I've got a function to give their network login, and that's what i'll be adding the table.

If I have problems where I can't get in, I will just open the backend MDB file and remove the rows from the userlist table, as it won't then run the check on opening.

 

[hneal98]

So, it sounds like you have things in place. All you have to do then when the user logs in is check the table first. I am not sure where you are having the problem.

 

[easyit]

They will use the shared lock file

Can I assume you have a network mdw? Or a split db (front end & back end?

If not a single network db (single db with network mdw) will keep the user logged in until she/he leaves the DB.
if split, the BE ldb will be opened as long as there is a connection to it from the FE. You could make this as short as possible, but there is an advantage in keeping the connection open an entire session: performance. When closing the connection access tries to delete the ldb file, which will not succeed if there are other users in it. Access will attempt to delete more than once (I believe 15 times) introducing a performance loss.

EasyIT

 

[Griff389]

It's a front end with the forms and queries, and a back end on a network drive with the tables in it.

The problem i'm having is i'm guessing the record set code above checks to see if the user is listed in the table, but I don't know what to do with it. I've not used recordsets in my db's yet.

I was hopeing for:
if NTDomainUserName isn't listed, add it to the list,
else msgbox and close.


I tried your code with this:

Dim Criteria As String
Dim DB As Database
Dim RecSet As Recordset
Dim DocName As String
Dim LinkCriteria As String

Set DB = CurrentDb
Set RecSet = DB!tblUserName.OpenRecordset(dbOpenDynaset)
RecSet.FindFirst NTDomainUserName

but I get an error with .FindFirst "method or data member not found

 

[hneal98]

I am assuming that NTDomainUserName is the name of the table.

Did you note that in my code, I used

Criteria = "[USERNAME] =" & (Chr(34) & CurrentUser() & Chr(34))

The Chr(34) is a quote. So I am putting a quote around the item I am looking for.

 

[Griff389]

No NTDomainUserName is a function in a module that gives what the Active Directory login name is. This is what I was checking for in the "tblUserlist" table.

I did note your Criteria code, but I presumed I could remove that and just tell it to use NTDomainUserName instead.

Didn't realise the quotes in there, i'll put it back to 'criteria=', but change CurrentUser for my NTDomainUserName and see if that's ok.

Thanks for the help so far guys.

 

[hneal98]

That should work.

 

[easyit]

First,

To avoid conflicts, name the type of database/recordset:

Dim DB As DAO.Database
Dim RecSet As DAO.Recordset 'could als be a ADO recordset

But beside this you could also use a query to check:

SELECT tblUserName.username, *
FROM tblUserName
WHERE (((tblUserName.username)=CurrentUser()));

With ADO you can use:

Private Sub btn1_Click()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
    .Open ("q_check_if_logged_in"), CurrentProject.Connection, adOpenStatic, adLockOptimistic
    If Not .EOF Then
        Application.Quit
    Else
        'Write code to adde the user to the tabel (a query is a simple solution)
    End If
    .Close
End With

End Sub

But stil, you have two shared ldb's, from the BE and from the MDW....

EasyIT