MS Access: High-level design recommendations needed

I am working on an Access DB. The data source for this DB is Excel 开发者_如何学运维spreadsheets. I need to add functionality that will allow a user to clean the raw Excel data by running a VBA function and then import this data into Access, without importing duplicate records.

I have a few questions about how to approach this:

1) Should I write a VBA procedure and a simple GUI in Excel to clean the data, or should I include this in an Access Form that allows the user to import Excel data into a table? In other words, should the data be cleaned in Access or outside of Access?

2) What is the best way to prevent the import of duplicate records into a table? Should I write VBA code to do this, or should I set a property of the table (if this is possible)?

3) Should I allow the user to clean the data in Excel and then just have the user use an import wizard in Access to add the data instead of writing code in a Form to allow the import of data into a data table?

Thanks!

The best way to prevent duplicate records in the database is to define a unique index on the column(s) that constitute(s) a unique key.

For example, if you wanted to make sure that there was no more than one specialist per speciality per hospital in the hospital system, you could do this in a hypothethetical HospitalSpecialists table:

             id                autoincrementing integer primary key
             hospitalid        integer foreign key references hospitals(id)
             physicianid       integer foreign key references physicians(id)
             specialityid      integer foreign key references specialties(id)


             unique composite index on (hospitalid, physicianid, specialtyid)

Then you couldn't insert a duplicate.

So you first have to determine what constitutes a duplicate for your table: which subset of the columns, when taken together, establishes the uniqueness of the record?

If the user has access to the Excel file at all, then you need to think of it as an untrusted / unclean data source, because you have no control over it at that point -- users make mistakes, forget instructions, etc.

So whether you do any cleaning in excel or not, you definitely need to check the data in Access.

Now you want to import the data, and prevent duplicate records? One way to do this would be to create a link to the excel file (i.e., Access will treat it like a table), clean the data per whatever your requirements are, and finally, create a CREATE TABLE query that queries the excel table (SELECT DISTINCT...), and inserts the results into a new Access table.

I'm an Access programmer, not an Excel programmer, so I vote for doing everything in Access. As @kuru kuru na... suggests, if the user can edit the Excel files, they can't be trusted to stay "clean" after you've run your scrubbing on them, so I would say to do all the cleanup in Access itself.

As to importing and avoiding duplicates, you need to take to heart @Tim's last paragraph, i.e., "you first have to determine what constitutes a duplicate". That can be more complicated than it sounds (e.g., will you match "Bob Smith" with "Robert Smith" or only exact matches?).