Query based on logged on user (Newbie)
 

I'm pulling data into excel from a SQL database. Rather than create several
identical workbooks where each queries a different users information, can I
add some code at some level that matches the [User_Name] field in the data
to the Windows logged in user name on the computer. Both are first intial +
last name.
The one workbook would have several different data sources (different SQL
Views from the same database) and each would refresh automatically upon
open.

If I can, is there some simple code anyone could offer me on this?

Thanks for any direction at all!
Tim

Query based on logged on user (Newbie)
 

Hi Tim,

Tim Miller wrote:
I'm pulling data into excel from a SQL database. Rather than create
several identical workbooks where each queries a different users
information, can I add some code at some level that matches the
[User_Name] field in the data to the Windows logged in user name on
the computer. Both are first intial + last name.
The one workbook would have several different data sources (different
SQL Views from the same database) and each would refresh
automatically upon open.

If you're using SQL Server, there is an suser_sname() function that will
return the current system user. If your query is using integrated
authentication (network credentials used instead of SQL Server login), then
you could add WHERE [User_Name] = suser_sname() to your queries in order to
filter by the connecting user.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Query based on logged on user (Newbie)
 

Thanks Jake,
That IS very useful. Unfortunately however, I log into it via a SQL Server
logon. My application that uses the database needs it that way (although
I'd like to change that). Any other options? It makes sense to me that
some code in the excel worksheets could do this, where it applied the
appropriate filter programatically on open but before refresh. Sounds good
when I say it, huh! Unfortunately I don't have a clue.
Tim



"Jake Marx" wrote in message
...
Hi Tim,

Tim Miller wrote:
I'm pulling data into excel from a SQL database. Rather than create
several identical workbooks where each queries a different users
information, can I add some code at some level that matches the
[User_Name] field in the data to the Windows logged in user name on
the computer. Both are first intial + last name.
The one workbook would have several different data sources (different
SQL Views from the same database) and each would refresh
automatically upon open.

If you're using SQL Server, there is an suser_sname() function that will
return the current system user. If your query is using integrated
authentication (network credentials used instead of SQL Server login),
then you could add WHERE [User_Name] = suser_sname() to your queries in
order to filter by the connecting user.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Query based on logged on user (Newbie)
 

Hi Tim,

OK - that does make things a bit trickier, but you should still be able to
get it to work. You can get the current user's network id with this:

Debug.Print Environ("Username")

This should work in most situations, but I prefer to use the API directly:

Public Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function sCurrentUser() As String
Dim sUserNm As String * 256
Dim nActualLen As Integer

On Error GoTo ErrHandler

If GetUserName(sUserNm, 256) Then
nActualLen = InStr(sUserNm, vbNullChar) - 1
If nActualLen 0 Then
sCurrentUser = Left$(sUserNm, nActualLen)
Else
sCurrentUser = sUserNm
End If
End If

ExitRoutine:
Exit Function
ErrHandler:
Resume ExitRoutine
End Function


Once you have the user's network id, you should be able to add it to your
WHERE clause on the client side in order to filter your results by user.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


Tim Miller wrote:
Thanks Jake,
That IS very useful. Unfortunately however, I log into it via a SQL
Server logon. My application that uses the database needs it that
way (although I'd like to change that). Any other options? It makes
sense to me that some code in the excel worksheets could do this,
where it applied the appropriate filter programatically on open but
before refresh. Sounds good when I say it, huh! Unfortunately I
don't have a clue. Tim



"Jake Marx" wrote in message
...
Hi Tim,

Tim Miller wrote:
I'm pulling data into excel from a SQL database. Rather than create
several identical workbooks where each queries a different users
information, can I add some code at some level that matches the
[User_Name] field in the data to the Windows logged in user name on
the computer. Both are first intial + last name.
The one workbook would have several different data sources
(different SQL Views from the same database) and each would refresh
automatically upon open.

If you're using SQL Server, there is an suser_sname() function that
will return the current system user. If your query is using
integrated authentication (network credentials used instead of SQL
Server login), then you could add WHERE [User_Name] = suser_sname()
to your queries in order to filter by the connecting user.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]