You are here:

Using MS Access/on error resume next statement


Hey Scottgem,

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 :

Sub print()
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; " & _
   "Data Source=test.accdb;"
' 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
       With rs
         .AddNew ' create a new record
         .Fields("Name") = Range("A" & i).Value
         .Fields("Rollno") = Range("B" & i).Value      
        ' stores the new record
   End With
   x = x + 1
   i = i + 1
'Next i
Set rs = Nothing
Set cn = Nothing

End Sub

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.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.