How to Add Active Directory Users to an Access Combo Box via VBA
Question details
The user needs to retrieve a list of Active Directory users and populate them into a Microsoft Access combo box.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Developing an Access database form that requires a dropdown list dynamically populated with user accounts from the organization's Active Directory.
- Observed behavior
- Requires a VBA script using LDAP queries or the Active Directory Services Interface (ADSI) to connect to the directory and load the user names into the form control.
Ensure you have the necessary network permissions to query your organization's Active Directory and that you know your exact domain structure.
Use LDAP and ADSI Queries in VBA
Connect to Active Directory using LDAP queries through GetObject and retrieve user names to load directly into the combo box.
To accomplish this, you must query the directory using the Active Directory Services Interface (ADSI). This requires adding a reference to the Active DS Type Library in your VBA environment so you can utilize objects like IADs, IADsGroup, and IADsUser.
You can connect to the RootDSE or a specific domain path using GetObject, retrieve the user accounts, and append them to your combo box.
Open your Microsoft Access database and press Alt + F11 to launch the Visual Basic for Applications (VBA) editor.
In the top menu, click on Tools, then select References. Scroll down the list, check the box next to 'Active DS Type Library', and click OK to apply.
In your form's code module, declare the necessary variables using Dim objRoot As ActiveDs.IADs, Dim objGroup As ActiveDs.IADsGroup, and Dim objUser As ActiveDs.IADsUser.
Use the GetObject function to bind to the directory root by adding the line: Set objRoot = GetObject("LDAP://RootDSE").
Loop through the Active Directory accounts using a specific path (e.g., Set objUser = GetObject("WinNT://" & strDomainName & "/" & strUserName & ",user")). Inside the loop, use the AddItem method on your combo box control to insert each retrieved username.
Looking for a Highly Compatible Alternative to Microsoft Office?
While Microsoft Access is used for complex database management, WPS Office is the ideal lightweight alternative for all your everyday document, spreadsheet, and presentation needs. Enjoy a familiar interface and advanced features without the hefty subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the lightweight installer and follow the on-screen prompts to set up the software on your device.
- 3. Open Your Office Files: Launch WPS Office and instantly open your existing Microsoft Office documents without worrying about format distortion.
Frequently Asked Questions
Why do I get a 'User-defined type not defined' error when running this VBA script?
This error occurs if you have not enabled the necessary library reference. In the VBA editor, go to Tools > References, and ensure the 'Active DS Type Library' is checked.
Can I filter the Active Directory users by a specific group before adding them to the combo box?
Yes. You can modify your LDAP query to target a specific Organizational Unit (OU) or check the 'memberOf' attribute during your loop to only load users who belong to the specified group.
Will this Active Directory VBA script work on a Mac?
No, querying Active Directory using ADSI, LDAP, or WinNT paths via VBA relies entirely on Windows-specific libraries and APIs. This method will only function in a Windows environment.
