Export Data from Excel into existing Table in Access

[K]

Hi all, I got Access Database on path "C:\Data\Rec.mdb". I got one
table in that database with the name "Table1" with five columns in
it. I want macro in excel which should delete all the records in
"Table1" and then copy Range("A2:E100") of Sheet2 and paste it into
"Table1". (Note: headings in Range("A1:E1") of Sheet2 are exctly
match with the "Table1" headings). Please can any friend help me on
this. I dont know if its possible or not!

 

[Mike]

this should work.
Option Explicit
Sub UploadDataToAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, sConn
Dim iRow As Integer
Dim ws As Worksheet

Set ws = Worksheets("Sheet2")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Data\Rec.mdb;Persist Security Info=False"

Set cn = New ADODB.Connection
With cn
.Open sConn
sSQL = "DELETE Table1.* FROM Table1;"
Set rs = New ADODB.Recordset
With rs
.Open sSQL, cn
End With
Set rs = Nothing

sSQL = "Table1"
Set rs = New ADODB.Recordset
With rs
.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdTable
iRow = 2
Do While Len(ws.Range("A" & iRow).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("Field1") = ws.Range("A" & iRow).Value
.Fields("Field2") = ws.Range("B" & iRow).Value
.Fields("Field3") = ws.Range("C" & iRow).Value
.Fields("Field4") = ws.Range("D" & iRow).Value
.Fields("Field5") = ws.Range("E" & iRow).Value
' add more fields if necessary...
.Update ' stores the new record
End With
iRow = iRow + 1 ' next row
Loop
.Close
End With
Set rs = Nothing
.Close
End With
Set cn = Nothing
End Sub