I am trying to export data out of an Excel worksheet to an Access table. Everything executes properly when I export to a newly made table (table1). When I attempt to add a record to an existing table (the one I need to add records to, I am getting an Automation Error. What could be the cause of that? I am not sure what information I can provide you with to help resolve my problem, below is the code and I am not sure how I can provide you with the properties of my table. Please let me know. Thanks


Code:
Sub ExcelToAccess()

Dim CN As ADODB.Connection, RS As ADODB.Recordset, r As Long
Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=U:\Intranet\PMData.mdb;"
Set RS = New ADODB.Recordset
RS.Open "dtlChangeOrderdt1", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

RS.AddNew
RS.Fields("JobNumber") = Range("b2").Value
RS.Update 'stores the new record


RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

End Sub