You are here:

Using MS Access/on error resume next statement

Advertisement


Question
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      
         
         .Update
        ' stores the new record
   End With
   x = x + 1
   i = i + 1
   Loop
'Next i
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


very grateful for your time

Answer
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

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.