VBA code to loop through access table (1 Viewer)

[Pat Hartman]

Why is the Excel spreadsheet the master? The master record should be what is in your Access application. If changes need to be made, they should be made in the Access app.

If Excel truly is the master, then nothing in your Access app is permanent. Every time you import the spreadsheet data you would first have to delete the prior imported data.

There is no secure way to match two files that do not have unique identifiers.

 

[luzz]

Import to a temp buffer table first which includes a pk field.
Then use an unmatched query to append non duplicate records.

If there are dupes in the new excel data, the pk in the buffer table should prevent more than one of each being imported.

If you haven't yet done so, delete all existing dupes then add a PK field to your main table to solve the issue.
Its madness to have a database without using PK fields.

Omg! Yes, it is madness. As the excel sheet is retrieve from a system which has no primary key, it is very hard on to assign a PK on my database when i import it over to Access.