Change the Current User

[mswilson16]

I need to know if it is possible to change the CurrentUser by having a form where the users type in there username and password and then that will then make them the CurrentUser().

I can currently retrieve information about the current user but to my knowledge there is no way of changing the user without making them log out and then back in again.

At the minute everyone just logs in under a staff or management username but management says this is not good enough as they need to be able to tell which member of staff/management has made amendments.

My main problem is that many people can use the same machine so I need something quick and easy so that they can quickly log in.

Any help is greatly appreciated.

Mswilson

 

[RickSpr]

Practically speaking, changing CurrentUser can't be done. CurrentUser returns the user name in the "default workspace". When Access starts up, it uses the user logon and password to create the default workspace, which is where all data access occurs for table and query datasheets, Form and Report recordsets, and macro actions. It is also the where accesses using DAO in code occur, unless you create and use a private workspace for the purpose. Access (actually, I think it's DAO) doesn't provide any way to change the user name and password in a workspace, and you can't alter the user name and password before the default workspace is created because that happens at logon time, long before your code has a chance to run. In fact, the default workspace has to exist before Access can even load your modules, forms, or any other database object.

Perhaps you could add an internal logon/logoff modal form that saves the user name in a global variable. Instead of using CurrentUser, your audit trail would get the user name from the global variable. In the logon form, you can't look up the password to see if it's correct, but you can test it with this code:

    Public Function CheckLogon(UserName As String, Password As String) As Boolean
    ' Returns True if user and password combination is valid
        Dim wsp As DAO.Workspace

        On Error Resume Next
        Set wsp = dbEngine.CreateWorkspace("Logon", UserName, Password)
        If Err = 0 Then CheckLogon = True
        Set wsp = Nothing
    End Sub

What this does is attempt to create a workspace for the given user and password. The workspace isn't used for anything, but if the user name/password combination isn't valid, the attempt will cause a runtime error. That tells you that either the user name or password is incorrect.

Note that for this function to work, you must set up User accounts in the workgroup file for all your users. You've probably already done that, since you were considering having them exit Access and log on as their own name.

You may also need to give the users a Change Password form. They can't use the one on the Tools|Security menu, because that gets the user name from CurrentUser. Alternatively, you can tell them to restart Access and log on the normal way, then use Tools|Security to change their passwords. Rick Sprague

 

[mswilson16]

Thanks Rick that will be great. The only problem with this is that the permissions that are set for the initial log in will still apply.

If this is correct, is there a way to change the permissions as well as the user name?

Thanks for the advice I have already received and thanks in advance.

 

[RickSpr]

I didn't expect that permissions were an issue, since you only said management was interested in authentication.

I'm afraid that messes everything up. Since you want permissions based control, you'll either have to accept Access' logon-based permissions, or implement the whole security system in your own code. There's no way to change permissions on the fly, because they're based on user name, and you can't change that on the fly.

There's one little loophole: Owneraccess queries (the "owner's permission" check box in the query properties). It allows the user to borrow the owner's permissions when running a query. That may help, but to use it you must base every record source on such queries, including forms, reports, combo and list box Row Sources, and DAO recordsets in code. If it will work for you, you'll have to use your own code to authenticate the user, as explained above, and then use your own permissions system to control access to the forms and reports. You might have to block them from using table and query datasheets to edit data, since you can't interpose any code to control their permissions on these.

It's like layering your own permissions system on top of Access'. I'm not sure, but by the time you got through with sealing all the leaks, you may have been better off implementing your own permissions system in the first place. And that's pretty hard. You have to think of every single thing the users can do, and evaluate whether you're maintaining control if they do that.

Could you maybe work around the problem by showing them a logon box, then doing a Shell function to restart Access with the database, username, and password on the command line? (You'd then shut down the instance in which the Shell command occurred.) It wouldn't be as fast, but it would be more convenient for them than doing it manually. Rick Sprague

 

[MichaelRed]

In my opinion, all of the above doesn't really resolve anything. If the workstation is running the app when the user starts to work, thery have no need to logon at all, and whatever the 'currentuser' is - is available to the user.

The only real process which more or less 'forces' the user to log on is to assure that the workstation is NOT running the app when the user starts their session. This can only be accomplished by making (reasonably) sure that the app is shutdown whenever a user stops working. Since the average workstation's syesight is not good enough to recognize the different users, the next best alternative is an inactivity time-out for the app. Setting the time out to a relatively small value and encouraging users to log out (thus forcing the next user to log in) is the better aproach. If management wants accountability abd tracking, just llet them know the schedual and cost toimplement what they have asked for. Let them decide / know what it takes. If they then alter the direction, that is their (informed) choice.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over