CODEWITHSUNDEEP
ms-accessms-access-2013
JDT
JDT

Reputation: 107

How to prevent Re-Import of Existing Records in an Access Saved Import from Excel?

I have a saved Excel Import in an Access Database that appends 'Payment data' each month to the 'Payments' table.

If someone were to run the import twice by mistake I noticed that Access simply appends the data twice to the table, thereby creating duplicate entries.

How can I prevent the Import from importing entries that already exist in the Table?

Fields in the Table include:

Thanks

Upvotes: 1

Views: 408

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

Import the data to temporary table and then copy to main table only new data. Plus unique index, as in @cha answer. But just index is not good solution, it should prevent mistakes, good style - do not generate errors, including key violations

Upvotes: 0

cha
cha

Reputation: 10411

The best way to do this would be by implementing a unique index in your table. Only you know what makes the data unique, sometimes it is a unique transaction reference number, or a combination of columns.

The best candidate for the unique index applicable to your situation is [Invoice Number]. If you allow multiple payments for the same invoice (i.e. split invoice) you may need to use two columns for the index: [Invoice Number] + [Payment Date]. When you use two columns for the index make sure they are in the same unique index, not each in separate indices.

Upvotes: 0

Related Questions