Update Access table from excel spreadsheet

[josepacheco]

Hi,

I curently have a database which contains client details, including thier bank details for which we use to debit them. Inniatily the data is captured onto a excel spreadsheet and I then upload the data into the table called Policies.

My problem: Sometimes I get data on the excel spreadsheet which contains changes to the bank details only but the rest of the data is the same. I get an error when i try to import the data because the primary key in the table already exists. How would I go about importing the data and only changing eg the bank details and leave what ever is the same alone.
Aslo getting rid of the error I get.

Kind Regards and Thanks in advance.

Jose

 

[Joe4]

Instead of importing the Excel file into Access, it might serve you better to link the Excel data to an Access table. Then the information is really held in Excel, not Access. Then when you get a new file, you should simply be able to replace the old Excel file with the new one, and all the changes will be reflected automatically in Access.

In Access help, type in "Import or link data and import database objects" for details, and specifically look at the item titled "Get more information to help decide whether to import or link tables" to help you decide if it is better to import or link your data.

 

[josepacheco]

Hi,

Thanks for your reply. All the data is stored in access and is more than excel can handle. I was hoping to automate the process we currently use to amend client details. As mentioned in the previous thread we recieve the amendments on a list in excel via e-mail.

Then someone processes each amendment manually (sometimes between 5 and 600 changes). I was hoping there was a way to upload these lists and automatically update the database.

Any other ideas besides linking the spreadsheets?

Kind Regards

Jode

 

[Norie]

Jode

Why not link the Excel spreadsheet as suggested?

Then use an update do make the changes in the existing table.

 

[josepacheco]

Thanks Norrie and jmiskey for your help. Your suggestions eventually got me on the right track.