Excel to Access with ADO: checking for duplicates?

[ossuary]

Hi,

I found some code on the net to send data from a worksheet in Excel to an existing table in Access, which I've modified to work with my worksheet and table layout. The only problem I'm having with it at this point is that the code crashes if I get to a duplicate record (the first field in the table is the key field, with duplicates not allowed).

I tried adding an error handler section to the code to skip over the .update section of the code if the record already exists, but I couldn't get it to work.

Here is the code I am currently using (the failed error handler is not included):

Const myDB = "DSD Errors DB.mdb"
Private Sub CommandButton1_Click()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long
    Dim Rw As Long
    
    [Q3].Select
    'go to the correct sheet and determine the # of used rows
    Rw = Range("A65536").End(xlUp).Row

    'create the connection to the database
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & "\" & myDB

    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open MyConn
    End With

    'create the recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="DSD_Invoice_Requests", _
             ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, _
             LockType:=adLockOptimistic, _
             Options:=adCmdTable

    'Load all records from Excel to Access,
    'by looping through the rows and columns
    myErrors = 0
    For i = 2 To Rw
        rst.AddNew
       'field headings are in row 1 of the worksheet.
       For j = 1 To 15
          rst(Cells(1, j).Value) = Cells(i, j).Value
       Next j
       rst.Update
    Next i

    ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Can someone please help me add an error check to this code so that it will not attempt to add a record to the Access table if the record already exists? Also, are there any other potential error checks I should be thinking of adding to this? I'm using Excel 2003 and Access 2003, in case that is important.

Thanks in advance.

 

[nirvana_]

You should filter your data before sending it to ms access. It is easier to remove duplicates in excel then run into trouble later.

 

[ossuary]

Thanks for the advice, but that wouldn't really work, because there may be items in Excel that already exist in Access, I'm not worried about having the same record twice in Excel, I'm worried about trying to send a record to Access that is already in there - I would have no way to filter those out ahead of time. I need to know how to check the record in Excel to see if it already exists in Access before I try to send it over.

 

[Richard Schollar]

What determines if the record already exists/ ie what is the unique identifier that you can use to determine this?

 

[ossuary]

The first field in the Access table, which is also the first field in the Excel sheet. It's a number (the purchase order number).

 

[Aebay]

Hey Ossuary, did you ever get an answer or find a solution? I am having a similar problem and an On Error isn't working.


Thanks