VBA TIPS
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.

Example

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)


Download

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", _
                                      ComboBox1.Value)
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
    Next
    rs.Close
    db.Close
    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)
        rs.MoveNext
    Loop
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    ArrayFromMDB = buf
End Function



| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo