Re: using the same login name TWICE?

From: papppp (papppp@hotmail.com)
Date: 05/17/02 

From: "papppp" <papppp@hotmail.com>
Date: Fri, 17 May 2002 20:15:53 +0300

Thank you for the answer. It saved me time!

In case someone has had the same problem with me and saw my e-mail in the
newsgroup, I should explain the problem more clearly. Maybe this would help.

I am developping an .adp application for a customer. Multiple users are
going to login to the program, each one of them using a private .adp file
stored locally. The point is that there are some data and some variables
that have to be unique for each person while he or she is using the
application: variables needed during the execution of the program, inside
views, stored procedures, VB code etc.

For example, if a user is viewing some company data for the year 2002 and
another wants to view the same data but for the year 2000, then, since they
are going to use the same SQL Views, I have to use something static as a
filter for the views, but unique for each user. And it's not very convenient
to have only parameterized stored procedures in Access.

So the only "per-user" permanent information I know of is the SQL login
name. You can get it by using the SQL built-in function suser_sname().
That's very easy. So, by using this function, I can store user variables and
settings in public tables, use these variables in JOIN queries, update them,
if needed. I just have to call the "WHERE suser_sname() = 'x' " as a filter
in a SELECT query from these public tables and I will get only the variables
for the user in question.

But there is ONE problem. What if two people login to my application
simultaneously using the same SQL login name?
The first user logs in, for example, as "John", and he wishes to view some
data for the year 2002. He makes the appropriate selections inside my
program and all the views he needs adapt to the new filter. The second user
logs in with the same login name, but he wants to view 1999 data. The
selections are the same but the filter changes! Imagine what will happen.

POSSIBLE SOLUTION:
The only thing I found as a solution to this is the table "sysprocesses".
This is a system table where (I think) SQL stores info about every running
process. By using a simple query you can find out if a specific SQL login
has already logged in to the server. You can even see what program the user
is using! I hope this works, otherwise...I won't see my customer again :)

If anyone has a better solution, please send a mail to the newsgroup!