Adding Data from Excel to Access Table

[Ginny2222]

Hi

I have the following Sub Routine which exports data from an Excel Worksheet into an Access Table. This works perfectly if the table contains no data, but errors if it does. Is there anything I can add to this to allow the data to be appended to the table.

Sub ADOFromExcelToAccess_Core_Time()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=D:\Database.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Core_Time_tbl", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("Persno") = Range("B" & r).Value
.Fields("Name") = Range("C" & r).Value
.Fields("TmType") = Range("D" & r).Value
.Fields("TimeTyText") = Range("E" & r).Value
.Fields("Period") = Range("F" & r).Value
.Fields("Date") = Range("G" & r).Value
.Fields("Number") = Range("H" & r).Value
.Update
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

All help appreciated
Ginny

 

[pbaldy]

What is the error? My guess is a primary key violation. If ID is a key field, and you already have "A1" in the table, you can't append another one. What would you want done in that case?

 

[Ginny2222]

Paul you are a genius!!!! Thanks. I should have thought of it. All sorted. I am controlling the Primary Key numbers from Excel so a simple adjustment and it's done.

Thanks again

Ginny