How to import the content of MS Access Database into combo box of Excel?

Question

I have a simple MS Access database with the following design:

---

|Last Name | First Name | Country |

And I have a combo box on Excel. I would like to list down all the Last Name and First Name from MyDatabase into combo box with the following format:

$LastName, $FirstName

I hope anyone could help me regarding this matter. Your response is very much appreciated.

Thank you and God bless!

Answer 1

Your can do it with vba macro. In order to do this, you have to 1)Connect to database and get the contents of the table to a recordset 2)Populate combobox with data from the recordset. OK, Here is the code

Private Sub Worksheet_Activate()
     Dim Db As Database
     Dim strSQL As String
     Dim rstFromQuery As Recordset
     Dim databasePath As String

     'Here you should specify path to to your database
     databasePath = "c:\db.mdb"
     Set Db = DBEngine.workspaces(0).OpenDatabase(databasePath)
     'Put the name of the table from you database instead of users
     strSQL = "select * from users"
     Set rstFromQuery = Db.OpenRecordset(strSQL, dbOpenSnapshot)

     usersBox.Clear
     i = 0
     While Not rstFromQuery.EOF
        usersBox.AddItem
        usersBox.List(i, 0) = rstFromQuery(0)
        usersBox.List(i, 1) = rstFromQuery(1)
        usersBox.List(i, 2) = rstFromQuery(2)
        rstFromQuery.MoveNext
        i = i + 1
     Wend
     rstFromQuery.Close

End Sub

The name of combobox is usersbox, and don't forget to

-change the databasePath in the code above

-change the name of the table("users")

-Set columncount property of combobox to 3

-Check "Microsoft DAO object library" in "Tools-References" in Visual Basic Editor