oakvick
asked on
Last Logon Via Active Directory Viewable from MSAccess
I have a user database in MSAccess. I also have a VBS script that produces an Excel spreadsheet that lists last logons for users from Active Directory. I would like to know how to get last logon information from Active Directory to appear on MSAccess form for any user I query on from my user database. Basically I would like to pull up a user and there would be a text box indicating whether account needs to be disabled for inactivity - say a 60 day threash hold. I am not proficient enough to make my VBS script to work in MSAccess VBA.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nicobo,
I placed the function in a textbox, i.e. "=UserLastLogin([name])", where name is the user name in my user database. What convention does your function put the Active Directory user name? I imagine that the name is my user db must match the convention of your function. Or, how do I properly use your function? Can I test it in the immediate window?
Thank you for your help! Joe
I placed the function in a textbox, i.e. "=UserLastLogin([name])", where name is the user name in my user database. What convention does your function put the Active Directory user name? I imagine that the name is my user db must match the convention of your function. Or, how do I properly use your function? Can I test it in the immediate window?
Thank you for your help! Joe
ASKER
I get "00:00:00" when I enter "?userlastlogin(name)" via the immediate window.
ASKER
Okay. I can get lastlogin from immediate window now by entering this, "?userlastlogin("smith, john")." I created a textbox with the default value of "=userlastlogin([name])" where name is the textbox with the user name in it. It returns "00:00:00". I hard code "smith, john" and it returns the last login date. How do I pass contents from name textbox to your function?
Joe
Joe
ASKER
I changed the "name" textbox name to "username". Placed it into the control source property and it now works! Cool beans!
Thank you Nicobo! Joe
Thank you Nicobo! Joe
Thats strange. Are you absolutely sure the [name] field contains exactly the text "smith, john" or any other name that you can find when you use the immediate window?
Change the beginning of the function to:
Now run the function from the immediate window and then from the form. Then check the immediate window if the name is correct. Check for double or leading or trailing spaces and things like that. The length is added after the second | so you can check if there are any hidden characters.
Change the beginning of the function to:
Public Function UserLastLogin(LoginName As String) As Date
debug.print "|" & LoginName & "|" & len(LoginName)
Dim conn As New ADODB.ConnectionNow run the function from the immediate window and then from the form. Then check the immediate window if the name is correct. Check for double or leading or trailing spaces and things like that. The length is added after the second | so you can check if there are any hidden characters.
There was an error in the original script
UserLastLogin user.LastLoginUserLastLogin = user.LastLogin
You can change the line:
sFilter = "(&(objectCategory=person)(objectClass=user)(name=" _
& LoginName & "))"sFilter = "(&(objectCategory=person)(objectClass=user)(samAccountName=" _
& LoginName & "))"
Sorry things got a bit mixed up because you posted things while I was preparing my posts.
ASKER
Yeah. I corrected that earlier. Great work bro! It works but I think I need to adjust now for multiple domain controllers. I have one user who logged on recently but it is showing an old login date.
Thanks again, Joe
Thanks again, Joe
That's strange. You can open "Active Directory - Users and Computers" and then select View, Advanced Features. In the property window of a user select the "Attribute Editor" tab, and find the lastLogon value. You can compare this value to the value returned by the script.
dsquery user -limit 0 - inactive 8 <--number of weeks