SQL query based on current user AD attribute match

[wallit]

Hi,

I have an Excel report, which pulls information from a database. The data is sensitive to each user so I would like the query to return the relevant data only for that logged-in user. The complication appears to be matching up the current logged-in user with the users in the database.

The database holds its own list of usernames which are made up of users initials, so user Joe Bloggs is JSB.

In Active Directory, we store these user initials in the AD property 'initials'.

For us, this is the most accurate way of linking the logged in user with the correct user in the database. Using any other method (such as the Application.Username) would not be reliable for us.

Is there a way to query AD for the 'initials' value then pass this to the query?

Kind regards and thanks in advance.

 

[Kyle123]

This will give you the initials of the currently logged in user (and cache them as AD can be a bit slow):

Function UserInitials() As String
    
    Static ini As String
    
    If Len(ini) = 0 Then
        ini = GetObject("LDAP://" & CreateObject("ADSystemInfo").UserName).Initials
    End If
    
    UserInitials = ini
    
End Function

I'd have probably used sAMAccountName, but it's horses for courses

 

[wallit]

This will give you the initials of the currently logged in user (and cache them as AD can be a bit slow):

Function UserInitials() As String
    
    Static ini As String
    
    If Len(ini) = 0 Then
        ini = GetObject("LDAP://" & CreateObject("ADSystemInfo").UserName).Initials
    End If
    
    UserInitials = ini
    
End Function

I'd have probably used sAMAccountName, but it's horses for courses

Thanks. This is exactly, what I needed. The next part is getting this variable into the SQL select statement, or however I can link these, which is where I struggle. The statement appears in the 'Connections' properties for the workbook. But the code above resides in the module. How can I pass the variable to the SQL statement?

For example, assume the where clause has something like this in it

WHERE dbo.EntityCode = Entities.UserRef='JSB'

How would I get the variable into that statement?

Many thanks again.

 

[Kyle123]

Where is the statement? Is it in your code? Can you post how you are currently doing it?

 

[wallit]

Hi,

The code currently exists in the connections properties (Excel 2010 ribbon > Data > Connections > Properties > Definition > Command Text).

I was't aware the statement could exist within the code itself. (By code I assume you are referring to the 'Module' in the VBAProject?)

Best regards

 

[Kyle123]

In that case, I think you'd use a parameter query https://support.office.com/en-in/ar...er-query-addf7cb7-ddf0-442f-a60c-cd7280e201bd

You can then use the code above as a worksheet formula and set the param to look at the cell - you may also have luck (though I haven't tried it) setting the formula as a named range and using that, it wouldn't be on the worksheet that way

 

[wallit]

Brilliant!

Kyle123, thanks. As you suggested, I modified the parameter to look at a single cell. I then modified the vba code to update that cell.

The code lives in a module, which is called from the Workbook_Open Private Sub. Each time the workbook is opened it updates a cell with the AD initials and refreshes the table. Neat.

Thanks again. For the benefit of others, my VBA code looks like this

Function UserInitials() As String

Static ini As String

If Len(ini) = 0 Then
ini = GetObject("LDAP://" & CreateObject("ADSystemInfo").UserName).Initials
End If

UserInitials = ini

Worksheets("Sheet2").Range("A1").Value = UserInitials

End Function