How to connect Access from Excel via ADO

'===========================================================================

'The macro example 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 Microsoft
'ActiveX Data Objects x.x Object Library.
'Use ADO if you can choose between ADO and DAO for data import or export.

'===========================================================================
Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
   
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\FolderName\DataBaseName.mdb;"
   
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
   
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("FieldName1") = Range("A" & r).Value
        .Fields("FieldName2") = Range("B" & r).Value
        .Fields("FieldNameN") = Range("C" & r).Value
        ' add more fields if necessary...
        .Update ' stores the new record
        End With
    r = r + 1 ' next row
    Loop
   
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub