Excel to Access with ADO: checking for duplicates?

ossuary

Active Member
Joined
Sep 5, 2004
Messages
279
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):

Code:
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. :)
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

nirvana_

Board Regular
Joined
Mar 25, 2009
Messages
141
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

Active Member
Joined
Sep 5, 2004
Messages
279
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
What determines if the record already exists/ ie what is the unique identifier that you can use to determine this?
 

ossuary

Active Member
Joined
Sep 5, 2004
Messages
279
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

New Member
Joined
Jan 9, 2009
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top