AutoImport from Excel File (1 Viewer)

[fraser_lindsay]

Hello,

I have a fairly small DB tracking employee details and job profiles. Every so often I need to run a manual update of employee details to add new employees in and amend current employees details such as names, job title etc.

I currently do this manually, which is still a nightmare. How easy is it to write a small module/macro that will take the Excel file dumped from the HR system and update/add employee details in my DB?

To slightly complicate things, some of my columns in my database have slightly different names to the Excel file - would it be easier to keep these the same?

As I add other parts of the business to this it is obviously growing and I need a reliable way of keeping the DB up to date.

Thanks in advance

 

[mcirvine]

If you know VBA it would not be to difficult to pull this off.

Is there a field indicating you need to update a record? If not you need to scan every record (and compare with access) to see if you need to update it or not, which may be time consuming. If your excel file indicates which records need to be added or updated, you just search for those in excel and update/add those.

In general it should look like this:
1) read record excel
2) find matching record access
3) found record => update it else insert new one

The fact that your column names are different doesnt matter. If you use same column names it's just easier for you or other persons in the future to maintain your program.