Login and auto-signing

[DanT]

Hello there, it's my first post here and so I'd like to take this chance and greet everyone. My name is Dan and I'm quite a newbie, I'm learning Access and VBA for the last several months. The company I work for has no database-oriented system so I thought 'how hard could that be?' and the rest is history...

I try to design database from scratch now using Access as front end and Microsoft SQL server for data storage. I'd like users to log in before entering the main menu, and I'd like to use this for automatically signing orders and other documents they create, or even track history of changes they make (if not too difficult to implement). Obviously, I need a login form and a table to store all the usernames and passwords, but I don't know how to store data about currently logged user as this would have to be saved somewhere temporarily, right?

[Micron]

You can use TempVars, custom object or user table to store and retrieve user data. There is also a widely used function named fosUserName, which I prefer. If you use that, make sure you get a version that was written for 64 and 32 bit. If you're sharing a Windows login (i.e. if there is a generic login or I log in, do my thing and leave the pc for someone else to use in that state) then that function is of no use to you. The idea of it is to grab user Windows login and pc id if you want that too, and do away with login forms, passwords and all that maintenance. Then you match table Windows login against what code retrieves and you know who it is. If that login isn't found, they don't get in.

To simply answer your last question, the data about the currently logged in user is in the table, no? FName, LName, EmplID, etc?

EDIT - forgot to say welcome to the forum!

[isladogs]

Agree with Micron about checking against Windows Active Directory to determine which users can access your database and also to log who is using it at any time

However, if that's not practical its not too difficult to setup your own password login system ... but make sure you make it secure.
In case its useful, see my example app: Password Login (isladogs.co.uk)

[DanT]

There is also a widely used function named fosUserName, which I prefer. If you use that, make sure you get a version that was written for 64 and 32 bit. If you're sharing a Windows login (i.e. if there is a generic login or I log in, do my thing and leave the pc for someone else to use in that state) then that function is of no use to you. The idea of it is to grab user Windows login and pc id if you want that too, and do away with login forms, passwords and all that maintenance. Then you match table Windows login against what code retrieves and you know who it is. If that login isn't found, they don't get in.
EDIT - forgot to say welcome to the forum!

Some people work on multiple computers on everyday basis (office desktop + remote laptop) but occasionally we use someone else's computers and so this might not work.

You can use TempVars, custom object or user table to store and retrieve user data. (...)
To simply answer your last question, the data about the currently logged in user is in the table, no? FName, LName, EmplID, etc?

EDIT - forgot to say welcome to the forum!

As far as I understand, the data in the table is used only to select username from the list and compare password. After that I need to somehow store the information, that certain user is logged in is is using the application. TempVar seems to be the answer for auto-filling field like 'Order created by'. I'll take a deeper look into it, thank you

Agree with Micron about checking against Windows Active Directory to determine which users can access your database and also to log who is using it at any time

However, if that's not practical its not too difficult to setup your own password login system ... but make sure you make it secure.
In case its useful, see my example app: Password Login (isladogs.co.uk)

your example seems to be quite advanced, looks great though. If I wanted to extract currently logged user for purpose of auto-sign documents - how would I do it?

[DanT]

@isladogs your example seems to be quite advanced, looks great though. If I wanted to extract currently logged user for purpose of auto-sign documents - how would I do it?

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[isladogs]

My example can be imported directly into your database if that helps
There are 3 main ways of getting user name.
See Get User Name (isladogs.co.uk)

For reasons explained in my article, my preferred method is CreateObject("WScript.Network").UserName

[DanT]

That's great, I'll look into it. Thanks a lot!

[Micron]

occasionally we use someone else's computers and so this might not work.

That does not matter IF they have to log in before they can use it. What matters is the Windows login credentials of the user. The fact that the function also can provide the computer id that user logged into is a bonus that you cannot get using conventional password management. So you'd not only know who, you'd know where.

EDIT - I'd say TempVars is for whatever you want it to be. Personally, I'd lean towards creating my own user object and creating/setting properties in it. However, that is a bit more advanced coding.

[DanT]

ahh, ok, I thought it shows computer name only, something like environ username. Looks like a way to go then!

[isladogs]

@Micron
You can just as easily get the workstation name using VBA as you can the user name: Get Computer Name (isladogs.co.uk)

[Micron]

@Micron
You can just as easily get the workstation name using VBA as you can the user name: Get Computer Name (isladogs.co.uk)

Not sure what you're saying. Is your comment related to item 2, or item 3 there, or something else?

[NoellaG]

You're working with a SQL database which has a very fine 2-level security system using logins on server level and user rights on the database level. You can use them to set the rights per object, even to the row (using views) /column level. You have there standard built-in functions and variables to get the current user for example: select system_user gets the logged in windows user.
And tracking is standard available on the database, just activate it:

[isladogs]

Not sure what you're saying. Is your comment related to item 2, or item 3 there, or something else?

It was a response to this comment:

That does not matter IF they have to log in before they can use it. What matters is the Windows login credentials of the user. The fact that the function also can provide the computer id that user logged into is a bonus that you cannot get using conventional password management. So you'd not only know who, you'd know where.

I may be wrong but that seemed to imply (at least to me) that the workstation info could only be obtained using active directory Windows login data

@NoellaG
Good point. Somehow I had overlooked the comment in post #1 that the BE is in SQL Server

[Micron]

the workstation info could only be obtained using active directory Windows login data

No, maybe I can make it more clear if I state it another way.
Using the function, it doesn't matter if you share a pc as long as each person has to log in and out of Windows when using that pc.
The point about sql server security is a good one.