cellmasters.net - Tips and Information about Microsoft Excel|Masaru Kaji aka Colo
Getting data from the Table of MS Access (DAO)

If you have the source data as an access database table and you'd like to use the data in Excel, using Data Access Objects (DAO) might be one of the best way to go.

DAO is a programming interface for operating the database created by Microsoft Access. It is possible to use all operations including creation of a database from a program. It is appended to the programming tool "Visual Studio" series as a standard, and it can be used in Visual Basic, Visual C++, etc.

Okay it's time to exercise.


Assume you have an Access file that has a data table of "Worldwide Tropical Cyclone Names", as Shown in the picture below.
  • The path of the Access file- The same path of this workbook
  • The file name of mdb(an Access file) - db1.mdb
  • The table name - Table1

an Access table

Get the data from the Access file and enter it into a ComboBox and a ListBox on the Userform of Excel.

Userform (Excel)


You can download the sample workbook from here. sample_071.zip  | downloaded  time(s)

'Place following in an Userform Module
'Need to check reference Microsoft DAO 3.X Object Library

Private Sub UserForm_Initialize()
    ComboBox1.List = GetFieldsName(ThisWorkbook.Path & "\db1.mdb", "Table1")
End Sub

Private Sub ComboBox1_Change()
    Me.ListBox1.Column = ArrayFromMDB( _
                                      ThisWorkbook.Path & "\db1.mdb", _
                                      "Table1", _
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Function GetFieldsName( _
                               strFilename As String, _
                               strTbl As StringAs Variant
    'strFilename: The full path of the Access file(.mdb)
    'strTbl        : The table name
    Dim db As Database
    Dim rs As Recordset
    Dim i As Long
    Dim buf()

    Set db = OpenDatabase(strFilename)
    Set rs = db.OpenRecordset(strTbl, dbOpenTable)
    ReDim Preserve buf(0 To rs.Fields.Count - 1)
    For i = 0 To rs.Fields.Count - 1
        buf(i) = rs.Fields(i).Name
    Set rs = Nothing
    Set db = Nothing
    GetFieldsName = buf
End Function

Private Function ArrayFromMDB( _
                              strFilename As String, _
                              strTbl As String, _
                              strFieldName As StringAs Variant
    'strFilename  : The full path of the Access file(.mdb)
    'strTbl          : The table name
    'strFieldName:The field name in the Access table
    Dim db As Database
    Dim rs As Recordset
    Dim i As Long
    Dim buf()

    Set db = OpenDatabase(strFilename)
    Set rs = db.OpenRecordset("Table1", dbOpenTable)
    Do Until rs.EOF
        i = i + 1
        ReDim Preserve buf(1 To 1, 1 To i)
        buf(1, i) = CStr(rs(strFieldName).Value)
    Set rs = Nothing
    Set db = Nothing
    ArrayFromMDB = buf
End Function

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo