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
