Download the contents of a table from Access to Excel

Download the .mdb sample file (68,765 bytes)
Download the .accdb sample file (71,432 bytes)
If you can't create the database, download this one (8558 bytes)

This is Part 4 of 7 in a tutorial on using code to integrate Excel and Access.

In the sample file, Table Download is blank and contains two buttons. By pressing Download Table you will populate the sheet with everything in tblPopulation.

Note:

The database name is defined as a constant at the top of the code module. You will need to add this to the top of your code module as well, above the first Sub.
You will need to set a reference to the Microsoft ActiveX Data Objects 2.x Library to run this code.

The constant is shown below.

Const TARGET_DB = "DB_test1.mdb"

The code does the following:

Creates an ADO connection to the database.
Defines a recordset based on tblPopulation
Loops through the field names in table and writes them out to row 1 of the worksheet
Uses the CopyFromRecordset method to transfer the database records to Excel, starting at row 2. CopyFromRecordset is more efficient than looping through the records one at a time; thousands of records can be transferred at once.
Cleans up references

Sub TransferTableFromAccess()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim i As Long
   Dim ShDest As Worksheet

   Set ShDest = Sheets("Table download")

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:="tblPopulation", _
            ActiveConnection:=cnn, _
            CursorType:=adOpenDynamic, _ 
            LockType:=adLockOptimistic, _
            Options:=adCmdTable

   'clear existing data on the sheet
   ShDest.Activate
   Range("A1").CurrentRegion.ClearContents

   'create field headers
   i = 0
   With Range("A1")
     For Each fld In rst.Fields
      .Offset(0, i).Value = fld.Name
      i = i + 1
    Next fld
   End With

   'transfer data to Excel
   Range("A2").CopyFromRecordset rst

   ' Close the connection and clean up references
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing

End Sub

<<Previous: Part 3 Next: Part 5>>

DataWright Information Services

Consulting and Resources for Excel and Access

Download the contents of a table from Access to Excel