Overwrite existing table daily with new, linked Excel data 1

[jdttek]

Hi
I have an existing account balance table (with account# as primary key) that needs to be updated every morning from an Excel spreadsheet, with fresh balances and any new accounts created overnite.
Importing to the existing table does not work because account # creates a duplictate index value and is not accepted. Is there a way to permanently link the excel spreadsheet and then run an update query on the existing table to capture the new information without going through the process of deleting the existing table and creating a new one every time? When I do this, existing relationships are broken and need to be recreated.

Any other suggestions on how best to do this?

Thanks
JDTTEK

 

[danvlas]

You can link the Excel spreadsheet:
Right click the Tables tab and choose Link
Select Files of Type = Excel files and locate the file.

Create a 'Find Unmatched' query to find which records exist in the Excel file but do not exist in the Access table
Turn the query into an append query (choose the Access table as destination)

That should work