Import from Excel and avoid duplicates 1

[doctorswamp]

Hi all

I'm importing order records from Excel into an Access table using this procedure.

***************
Private Sub Import_Click()
' Import orders from Excel file Orders.xls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCustomerOrders", "C:\orders.xls", True

End Sub

***************

Can I prevent the same set of records in Orders.xls from being imported a second time if I run the procedure again?

I'd like the Access database to build up a record of all orders in tblCustomerOrders for future reference, but only once. The subsequent steps in Access add a flag to orders that have been processed so they are queried out to prevent repeat processing.

Thanks

 

[Darrylles]

Hi doctor,

No need for such flags - the 'flags' are the current orders themselves.

Create a 'link' to the Excel spreadsheet and then treat it like any other table.

Create a query that finds 'unmatched' records from your current table compared with the Excel linked file. (Use the 'Unmatched' query wizard).

Use this in your 'append' query to filter out unwanted (already imported orders) and thus only add orders that don't exist in your table.

ATB

Darrylle


atb

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[doctorswamp]

Splendid, thanks Darrylle. I really didn't know you could link to an Excel spreadsheet like this, and the way you suggest makes the whole process so much easier.

Definitely worth a star!

 

[doctorswamp]

Adding on to this, I now find that any query based on the table linked to the Excel spreadsheet isn't updatable, which is a problem.

How best to resolve this?

Tahnsk

 

[Darrylles]

Doc,

You should only want to update records already in your internal table. Use the Excel linked query only to import relevant records - don't use it for anything else.

Hope this makes sense.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[doctorswamp]

Darrylle

Perfect sense thanks. The problem goes away if I update an 'internal' orders table with the one linked to Excel.

Many thanks, indeed.

Doc