import from excel /

i want to import some rows (cells) from excel ,
but without repeating the same row ,
i used this code its working very gut ,,
how i can check before import that some record already exist !!
thnaks

hier my code

on click
    Dim oXL As Object
    Dim oWs As Object
    Dim rs As DAO.Recordset
    Dim i As Integer

    Set oXL = CreateObject("Excel.Application")
    Set oWs = oXL.Workbooks.Open("C:\Users\User1\Desktop\booking.xlsx")

    Set rs = CurrentDb.OpenRecordset("booking")
    zeile = 2
      
    Dim Ende As Long
    Ende = 0

    Do Until oWs.workSheets("booking").Cells(Ende + 2, 2) = ""
        Ende = Ende + 1
    Loop
 
    MsgBox "TRANSFER DONE"
    With rs
        For i = 1 To Ende
            .AddNew
               
            .Fields("Date of Tour") = oWs.workSheets("booking").Cells(zeile, 6)
            .Fields("Pickup Time") = oWs.workSheets("booking").Cells(zeile, 7)
            .Fields("name of account") = oWs.workSheets("booking").Cells(zeile, 3)
            .Fields("Tour") = oWs.workSheets("booking").Cells(zeile, 5)
            .Fields("Pax AD") = oWs.workSheets("booking").Cells(zeile, 8)
            .Fields("Pax ch") = oWs.workSheets("booking").Cells(zeile, 9)
            .Fields("cost AD") = oWs.workSheets("booking").Cells(zeile, 10)
            .Fields("cost ch") = oWs.workSheets("booking").Cells(zeile, 11)
            .Fields("sale AD") = oWs.workSheets("booking").Cells(zeile, 12)
            .Fields("sale ch") = oWs.workSheets("booking").Cells(zeile, 13)
            .Fields("PaymentMeth") = oWs.workSheets("booking").Cells(zeile, 15)
            .Fields("Driver") = oWs.workSheets("booking").Cells(zeile, 16)
            .Fields("Guide") = oWs.workSheets("booking").Cells(zeile, 17)
            .Fields("TotalCollect") = oWs.workSheets("booking").Cells(zeile, 18)
            .Fields("bookedby") = oWs.workSheets("booking").Cells(zeile, 20)
            .Fields("mob") = oWs.workSheets("booking").Cells(zeile, 21)
            .Fields("pickupfrom") = oWs.workSheets("booking").Cells(zeile, 22)
            .Fields("Guset Name") = oWs.workSheets("booking").Cells(zeile, 23)
            .Fields("others") = oWs.workSheets("booking").Cells(zeile, 24)
            .Fields("provider") = oWs.workSheets("booking").Cells(zeile, 26)
            .Fields("language") = oWs.workSheets("booking").Cells(zeile, 28)
            .Fields("e mail") = oWs.workSheets("booking").Cells(zeile, 29)
       
            .Update
            
            zeile = zeile + 1
            DoEvents
        Next i
        .Close
    End With
    Set rs = Nothing
        
    oWs.Close
    oXL.Quit
    Set oWs = Nothing
    Set oXL = Nothing
    DoCmd.Requery

Open in new window

sami_hamadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can import your worksheet to a temporary "staging" table (just a table in your Access db that won't be used for anything else). Once you've imported, you can then use standard SQL to determine if you have duplicate data, which is much simpler than do a row-by-row comparison.

You can use TransferSpreadsheet to import you Excel workbook:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "StagingTableName", "Path to your spreadsheet", True

See this for more info on TransferSpreadsheet: https://msdn.microsoft.com/en-us/library/Bb214134%28v=office.12%29.aspx?f=255&MSPPError=-2147217396

After getting the data into that table, you can then determine which rows need to be moved over to your "live" tables. I'm not sure how you're determining that, but for example if your "staging" table has a column named "CustomerNumber", and you want to be sure that you don't add any records with a pre-existing CustomerNumber, you could do this:

CurrentDB.Execute "INSERT INTO YourLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM YourStagingTable LEFT OUTER JOIN YourLiveTable ON YourStagingTable.CustomerNumber=YourLiveTable.CustomerNumber WHERE YourLiveTable.CustomerNumber IS NOT NULL"

This would only insert those records where the JOIN expression above does not find a "match" in YourLiveTable.

Of course, your validation may be more involved than that, but you can add multiple fields to the JOIN expression, or multiple criteria to the WHERE clause, etc.

Note you may also be able to simply Link the Excel workbook instead of Importing it. If that's the case, you just link it and do NOT import it to the staging table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
Adding a unique index to your table will prevent duplicates.  You should trap for the duplicate key error(s) and keep on adding new records.
tomfarrarCommented:
Why not get rid of the duplicate rows before import.  Make a copy of the worksheet.  Use Data, Remove Duplicates..
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.