Working with Access Database from Excel

A week ago, one friend of mine asked me to help with reading some data from Microsoft Access to Excel using VBA script. I haven’t found any really simple way after few clicks so I prepared something simple myself. Enjoy..

Before start, we need to open VBA macro editor, its Alt+F11 shortcut in Excel.

Second requirement to make things working is adding Reference to Microsoft ActiveX Data Objects. From Excel VBA editor its in Tools – References menu and look for Microsoft ActiveX Data Objects

Now we need to write some code. You can open ThisWorkbook and copy/paste following:

Sub LoadAccessData()
Const DBPath = “C:\peopledb.mdb”
Const ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & DBPath & “;”
Const SQLCommand = “select * from people”
    ‘ ADODB Connection
    Dim connection As New ADODB.connection
    ‘ ADODB Recordset
    Dim recordset As New ADODB.recordset
    ‘ Number of columns in query
    Dim FieldCount As Integer
    ‘ Number of rows in query
    Dim RowCount As Integer
    ‘ temporary variables for walk on rows and columns
    Dim irow As Integer
    Dim icol As Integer
    Dim readed As Variant
    ‘ Destination in Excel Sheet
    Dim DestinationRange As Range
    Set DestinationRange = Sheets(1).Cells.Range(“A2”)
    ‘ Open connection to the database
    connection.Open ConnectionString

    ‘ Open recordset based on sql
    recordset.Open SQLCommand, connection
    FieldCount = recordset.Fields.Count
    irow = 0
    While (Not recordset.EOF)
        For icol = 0 To FieldCount – 1
            readed = recordset(icol).Value
            If IsDate(readed) Then
                Set readed = Format(readed)
            ElseIf IsArray(readed) Then
                readed = “Array”
            End If
            DestinationRange.Cells(irow + 1, icol + 1).Value = readed
        Next icol
        recordset.MoveNext
        irow = irow + 1
    Wend
    RowCount = irow
    recordset.Close
    connection.Close
End Sub

Listing: Reading Data to Excel from Access

Sub SaveAccessData()
Const DBPath = “C:\peopledb.mdb”
Const ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & DBPath & “;”
    ‘ ADODB Connection
    Dim connection As New ADODB.connection
    ‘ Number of columns in query
    Dim FieldCount As Integer
    ‘ Number of rows in query
    Dim RowCount As Integer
    ‘ temporary variables for walk on rows and columns
    Dim irow As Integer
    Dim icol As Integer
    Dim readed As Variant
    ‘ Destination in Excel Sheet
    Dim SourceRange As Range
    Set SourceRange = Sheets(1).Cells.Range(“D2”)
    ‘ Open connection to the database
    connection.Open ConnectionString
    irow = 1
    Do
        Dim fieldname As String
        fieldname = SourceRange.Cells(irow, icol).Value
        connection.Execute (“insert into people ([name]) values(‘” & fieldname & “‘)”)
        irow = irow + 1
    Loop While Not IsEmpty(SourceRange.Cells(irow, icol).Value)
    RowCount = irow
    connection.Close
End Sub

Listing: Inserting data to Access db from Excel Sheet

There are several ways how to get out of this, especially when inserting to Access in second snippet. This way is really simple (means short) because does not require prepared RecordSet before.

When selecting some data, there is also a faster method (using CopyFromRecordset), but this one should work even with older Excel versions (version 97 and earlier).