As with most things in life, there are many ways to tackle such a need as reading data from an Access database from other applications (Excel, Word, …), but today I thought I’d demonstrate just how easy it is to do with some simple VBA.

Unprotected Database

Probably a rare occurance, but if you need to read data from an unprotected Access database it can be done by simply doing something along the lines of:

    Dim Conn                  As New ADODB.Connection 'Microsoft Activex Data Objects Library
    Dim rs                    As New ADODB.Recordset
    Dim sSQL                  As String
    Dim sConnect              As String

    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Marvin\Database.accdb;Persist Security Info=False;"
    Conn.Open sConnect
    sSQL = "SELECT * FROM Contacts"
    rs.Open sSQL, Conn
    Sheet1.Range("A1").CopyFromRecordset rs
    rs.Close
    Conn.Close

Password Protected Database

The more common scenario, connecting to a Password Protected Access Database isn’t much more difficult and everything lies in the connection string (sConnect) used.

    Dim Conn                  As New ADODB.Connection 'Microsoft Activex Data Objects Library
    Dim rs                    As New ADODB.Recordset
    Dim sSQL                  As String
    Dim sConnect              As String

    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Marvin\Database.accdb;Jet OLEDB:Database Password=99e5hhaL5bt;"
    Conn.Open sConnect
    sSQL = "SELECT * FROM Contacts"
    rs.Open sSQL, Conn
    Sheet1.Range("A1").CopyFromRecordset rs
    rs.Close
    Conn.Close

So as you can see, it is very simple to connect to an Access database to retrieve any information you want with just a few lines of code.

Listing of Tables

Here’s a simple example of how one can extract a listing of tables within a database using this approach.

    Dim Conn                  As New ADODB.Connection    'Microsoft Activex Data Objects Library
    Dim rs                    As New ADODB.Recordset
    Dim sSQL                  As String
    Dim sConnect              As String
    Dim i                     As Long

    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Marvin\Database.accdb;Jet OLEDB:Database Password=99e5hhaL5bt;"
    Conn.Open sConnect
    Set rs = Conn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
    If Not rs.EOF Then
        Do While Not rs.EOF
            i = i + 1
            Sheet1.Range("A" & i).Value = rs!TABLE_NAME
            rs.MoveNext
        Loop
    End If

    rs.Close
    Conn.Close

Useful References

There are a number of different connection scenarios, password, ULS, … to get the proper connection string syntax refer to Access connection strings.