Import data from Access to Excel (ADO)

 2001-11-27    ADO    0    267

With the procedure below you can import data from an Access table to a worksheet.

Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", "TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable 
        ' all records
        '.Open "SELECT * FROM " & TableName & " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText 
        ' filter records
        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
'        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
'            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'        Next
'        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub
You'll find the procedure RS2WS by clicking on this link. The macro examples assumes that your VBA project has added a reference to the ADO object library. You can do this from within the VBE by selecting the menu Tools, References... and selecting the Microsoft ActiveX Data Objects x.x Object Library. Use ADO if you can choose between ADO and DAO for data import or export.