Updating exising access tables by importing from excel

[paintguy]

I have an access database ( mainly contact information) - and I send extracts to folk in the form of excel spreadsheets.

I would like to allow users to update a spreadsheet - send it to me and I will update the master access database by importing it (overwriting?).

My questions are:

1. how would I do this?
2. can I do it id the data in the spreadsheet comes from different access tables?
3. Is there a better way?

Many thanks

 

[lovemuscle2005]

You can do it by macro if you're not the best VB-er (I hate access VB!).

There is a command called transfer spreadsheet, follow the steps at the bottom, i.e. which table to append, address of the excel sheet etc

 

[Norie]

You dont necessarily need to use a macro.

You could use various append/update queries.

What they will be exactly is dependent on the data structure both of the existing Access table(s) and the Excel spreadsheets.

 

[paintguy]

I tried a macro - and I could only get it to append records - which then leaves duplicates. Am I missing something?

Thanks

 

[Norie]

Can you please give some more information?

This sort of thing is specific to how everything is set up/organised.

 

[paintguy]

I have 2 tables.

-One with a Child name (first name and surname) and primary key.
-One with a sponsor details - sponsor (primary key), name address email detials etc. There are also people on this list who have no relationship with the children table.

I give a spreasheet to a trustee A list of children names and refs. relating to each child sponsor (combined output from 2 tables).

From time to time the trustee will make changes ( address corrections, email changes, children changes,new sponsors) I would like to have these changes reflected in the access database by re-importing this data and getting it to make the changes.

As you can see it is a simple database - but I am struggling to see who I can get changes ( and additions) translated from the spreadsheet into accesss.