export excel data to access table using ADO
 

Excel sheet name - Sheet 1 and my data has header,
Access db name - is stored in cell B2
Access table name - Main

Code :
Sub Button14_Click()

' Exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection
Dim r As Long
Dim LastRow As Long
Dim dbfile As String

dbfile = Cells(2, 2).Value

Set cn = New ADODB.Connection

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source= " & dbfile

' Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon

' Find LastRow in Col A into the Sheet1
LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

' Insert unto a table called Main
scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]"
strSQL = "INSERT INTO MAIN " _
& "SELECT * FROM " & scn & ".[Sheet1$A3:AI" & LastRow & "]"

' Execute the statement
cn.Execute strSQL


cn.Close

Set cn = Nothing

End Sub