Returning current user from SQLServer

[Robman]

I have converted my Access 2007 back end to a SQLServer and am trying to
return the current user back to the application. Unfortunately if I use the
built in function CurrentUser is only returns Admin which is from Access and
if I try to use the TransactSQL function CURRENT_USER in a SQL Docmd.RunSQL
statement (Select CURRENT_USER) Access errors and says it is looking for a
SELECT, INSERT etc type of statement. If I run 'Select CURRENT_USER Go' in a
SQL Query analyser window it returns the logged in user name.
Does anyone know how to get Access 2007 to tell me the name of the user that
is logged onto the backend (SQLServer) database?
Thanks

 

[Douglas J. Steele]

I don't believe it's possible to use RunSQL for T/SQL statements. Access has
no way of knowing that you're trying to connect to an external data source.
As well, as you've already discovered, RunSQL can only be used with Action
queries (INSERT INTO, UPDATE, DELETE)

You'll have to write a pass-through query, then open a recordset based on
that pass-through query to retrieve its response.

 

[Robman]

Douglas:
Thanks very much for the advice!
It seems very strange that MS has removed User level security from Access
but has provided no easy way to find out who the current user is when working
on SQL Backends! As you can imagine without user level security it is
relatively difficult to secure individual things in a database.
Anyway I will try to figure out how to do this but so far it has me stumped
and I don't know if a pass through query will do the job as there is only one
value to be returned in each case!
Thanks a lot!
Robman

 

[Chris O'C via AccessMonster.com]

Where did you get your misinformation? Microsoft hasn't removed user level
security. It's still available for all db files that use the Jet db engine.
If you're complaining you don't have user level security, you're not using
Jet. The accdb files don't use Jet, they use the ACE db engine.

Chris
Microsoft MVP

 

[Robman]

Chris the information I have came straight out of the MS Access help files
for 2007

"Cause
User-level security is not supported by the new file formats in Microsoft
Office Access 2007."

Hence the reason I am trying to get the user name from the SQLServer back end.

 

[Chris O'C via AccessMonster.com]

That tells me if I need user level security not to save the db in the new
file format, accdb. Use mdbs, which are managed by Jet. Jet is *required*
for user level security.

Did you interpret it differently? Did you assume Access 2007 isn't capable
of creating mdbs? It can make mdbs, adps and accdbs and all the derivitive
file types like mdes, accdts, etc. You're not forced to use the accdb file
format to store your db app.

Convert your front end db file to an Access 2007 mdb, apply user level
security and use CurrentUser to get the user's name.

Chris
Microsoft MVP

 

[Robman]

Unfortunately that is not what I read the message to mean. My understanding
was that if you wanted to use the new capabilities of Access 2007 then you
had to save your db in the new format - accdb as opposed to the old mdb.
Thanks for your post.

 

[Rick Brandt]

Unfortunately that is not what I read the message to mean. My
understanding was that if you wanted to use the new capabilities of
Access 2007 then you had to save your db in the new format - accdb as
opposed to the old mdb. Thanks for your post.

Some of the new features do require that.

Implementing user level security just so you can capture user's names would
be phenominal overkill. Just use the API call to return the current windows
account name.

 

[Chris O'C via AccessMonster.com]

He was also interested in securing "individual things in a database".

Chris
Microsoft MVP

 

[Rick Brandt]

He was also interested in securing "individual things in a database".

With a SS back end data should be secured with SS security. If he wants to
use code to decide what users can do in the front end getting the user name
from SS or the Windows API is still a better option than going through all
the guff to set up Access ULS particularly if he wants to use the new AccDB
features.

 

[Robman]

Rick (and Chris):
Thanks very much for your posts on this subject.
Just to clarify:
I have secured the data in SS using OS based security and roles.
I am attempting to create some "smart" menus that display only what is
appropriate for the user signed in.
I have figured out how to use a Pass Through query to get the user name back
- this comes with some problems because SYSTEM-USER returns both the username
and the PC name i.e. PCNAME/Username instead of just user name and to be
honest I can't remember how to use the StringLeft,Mid,Right funcitons to get
rid of the PC name and the slash (/) SO having said that is there a way
within Access to "call" the API on the server to get just the user name that
is logged onto the Access session?
Not being a "programmer" it takes a while to learn each of these things one
by one so I really appreciate all the help that both of your are providing!
Thanks
Robman

 

[Chris O'C via AccessMonster.com]

You can find the code here:

http://www.mvps.org/access/api/api0008.htm

Use the fOSUserName function to get the current user's login name.

Chris
Microsoft MVP

 

[Robman]

It worked just fine - thank you!
The only problem I have run into is how to make the username variable
"global" or "public" so that it persists throughout the session.
I know that each time I want to user the user name I could call the function
again but what I tried to do, unsuccesfully, was to declare the variable as
public so that it would be available at all times (once read).

The way I attempted to do this was in a declaration:
Public loggeduser as string
loggeduser=fOSUserName

Even though I declared loggeduser as a variable length string I received an
error code "Invalid attribute in Sub or Function" and the "Public" was
highlighted.
A thorough re-reading of the Public function did not clarify why the
statement was invalid so hopefully somebody knows!

Perhaps there is a better way to accomplish what I want to do other than try
and make loggeduser a persistent variable?

Thanks for all your help so far!
Robman

 

[Chris O'C via AccessMonster.com]

Because it's a variable declaration, you can put it in the declarations
section of the module:

Public loggeduser as string

Because it's a command, you must put this line inside a procedure:

loggeduser=fOSUserName

Unless you make an mde, as soon as there's an error, even if it's handled by
your error handler, all global variables will be reset, so loggeduser = "".

A better way is to open a hidden form on startup that has an unbound text box.
When this hidden form opens, it assigns the user's name to the text box, like
this:

Me.txtUserName = fOSUserName

Later whenever you need the user's name, use vba code to get it from the text
box in the hidden form:

Me.txtUpdatedBy = Forms!frmHidden.txtUserName

Chris
Microsoft MVP

 

[Robman]

Chris:
No doubt two heads are better than one!
Thanks for the suggestion - that should get the job done - what I will do is
since I have put the user name on the opening form I will just leave that
form open behind everything else and reference it!
Thanks for all your help!