loading a combo box from a module

[KRNGI]

Hi all, I am completley new to access/VBA. Trying to find out a way to give the user the option to select list of sql servers/databases by using a combo box on a form. How do I do this? I am using SQL DMO to get a list of all the sql servers/databases but I cant figure out a way to display this in a form..How do I link the form controls to a code in my module so that the combo displays all the servers and the user can select them for the second combo to list all databases..Please help ..

Thanks!

[NeoPa]

I've no idea what SQL DMO is, but if you post what you've got so far we can look at it for you.

[ADezii]

Like NeoPa, this is hardly my area of expertise, but you can use the following code as a Template which will hopefully point you in the right direction, at least as far as populating the Combo Boxes. The code makes only 2 assumptions, namely, the Combo Box listing the Servers is named cboServers, and the Combo Box listing the Databases for each Server selected in cboServers is cboDBs. Good Luck.

1. To populate cboServers with the list of available Servers:
   

   Dim i As Integer
   Dim oNames As SQLDMO.NameList
   Dim oSQLApp As SQLDMO.Application
    
   Set oSQLApp = New SQLDMO.Application
   Set oNames = oSQLApp.ListAvailableSQLServers()
    
   For i = 1 To oNames.Count
     Me![cboServers].AddItem oNames.Item(i)
   Next i

2. Once a Server is selected, populate cboDBs with the list of Databases residing on the chosen Server. The context of where the code is executed is critical, namely in the AfterUpdate() Event of cboServers.
   

   Private Sub cboServers_AfterUpdate()
   Dim i As Integer
   Dim oSQLServer As New SQLDMO.SQLServer
    
   If Not IsNull(Me![cboServers]) Then
     If <UserName> = "" And <Password> = "" Then
       oSQLServer.LoginSecure = True
     End If
    
     oSQLServer.Connect Me![cboServers], "<UserName>", "<Password>"
    
     For i = 1 To oSQLServer.Databases.Count
       With oSQLServer.Databases(i)
         If Not .SystemObject Then
            cboDBs.AddItem .Name
         End If
       End With
     Next i
   End If
   End Sub

3. Make the appropriate substitutions for <UserName> and <Password> in Lines 6 and 10. You can actually refer to to Values in Controls (Text Boxes), namely Me![txtUserName] for <UserName>, and Me![txtPassword] for <Password>.