Change Login Info to match current Windows Machine for Table/Query 2

[kjv1611]

I have an Excel spreadsheet which runs an instance of Microsoft Query in a table within that spreadsheet. What I want to be able, currently, to change/update is the user login information. I set everything up on my machine, and it worked fine. However, when another person tries to use the same, it is attempting to login as me (which won't work).

I need to change the user authentication to just look at the windows authentication, or else set those values from Windows programatically. I tried looking at the QueryTable object in Excel help, but did not see a property for the login credentials (I might have just overlooked it).

This is what I would like to say:

Sheets("Query").LoginName = ntuser
Sheets("Query").Password = ntuserPassword

Or either set some option to look at the local machine's login info.

Does anyone know the method for doing this, or if there is one? I sure hope there is!

Thanks!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Have a look at the QueryTable.Connection property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Thanks for the hint. I'll take a look..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, I've looked at the Connection property for the QueryTable, and here is what it has (edited for private details):

ODBC;DSN=dw;Description=data warehouse;UID=[B]MyLoginName[/B];APP=Microsoft Office 2003;WSID=ABC35C13X51;DATABASE=dw;Trusted_Connection=Yes

And the help file says this interesting point:

When using an offline cube file, set the UseLocalConnection property to True and use the LocalConnection property instead of the Connection property.

So, does that mean I do it that way, or do I just insert ntuser or some other VBA function in the place of MyLoginName?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Alright, this is what I've come up with so far. It looks like it works, but I wanted to be sure it's the most effective way:

ActiveWorkbook.Worksheets("Query").QueryTables(1).Connection = _
    "ODBC;DSN=dw;Description=data warehouse;UID=" & _
    [b]Application.UserName[/b] & ";APP=Microsoft Office 2003;" & _
    "WSID=GVLRES7B3GS81;DATABASE=dw;Trusted_Connection=Yes"

Does that appear that it would do the trick, or do I also need to set some other portions via code?

--

"If to err is human, then I must be some kind of human!" -Me

 

[Rofeu]

Hi,

I wouldn't use Application.UserName. That returns the username as specified in Excel.

To get the Windows Login Name use this:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If

End Function

Check out this site for the different connection strings you can use:

http://www.connectionstrings.com

Cheers,

Roel

 

[PHV]

A simpler (less safer) way: Environ$("UserName")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Thanks for the info, Rofeu. I may use that in some applications, and at least it's very good information to know. I think I'll go with the Environ mention by PHV. I've used that before, and it's simple, and safe, as he stated.

Thanks all!

--

"If to err is human, then I must be some kind of human!" -Me