Using MS Access/on error resume next statement
Greetings of the day.
I have an macro that is updating values from excel to Access. In Access database I have defined a primary key that prevents any duplicate entries to be entered in the database.
The issue that I am facing is , as soon as any duplicate entry comes, macro simply ends rather than skipping the entry and moving the next record. Please suggest a way out for this.
my macro looks like this :
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim x, i As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Dhwani", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
'For i = 1 To 2
'x = 0
'i = 2
x = 2
i = 2
Do While Not IsEmpty(Range("A" & x))
'Do While Len(Range("A" & x).Offset(0, x).Formula) > 0
' repeat until first empty cell in column A
.AddNew ' create a new record
.Fields("Name") = Range("A" & i).Value
.Fields("Rollno") = Range("B" & i).Value
' stores the new record
x = x + 1
i = i + 1
Set rs = Nothing
Set cn = Nothing
very grateful for your time
Are you running this from Excel or Access? If from Excel, why? It would be easier to do from Access, Just link to a named range in worksheet and run an Append query.
But there are two possibilities here. The first is to add error trapping. Check for the error number being thrown and use Resume Next when encountered.
The second is to check for the existence of the key value before You run the .Update.