I am using Excel and Access 97.
I have an excel workbook that is linked to access for reporting purposes via the "view ms access report" button which works fine. Each year I want to create a new excel spreadsheet from the current one, clear some of the data and either a new access db or not depending on which is the easiest (doesn't really matter which as its only used to generate the reports).

The problem is even though I have
1. created new copy of spreadsheet
2. created new access db and imported all tables and reports into new one
3. edited names of tables and reports and changed "recordsource" within the properties dialog box
4. pointed access to the new excel file via tools>add-ins>linked table manager and browsed to location of new excel file
5. in excel changed range names
it still doesn't work.

If I click on the "view ms access report" button on the excel sheet it still jumps off to the old access database.
In access if I open the new db file and try to open one of the linked tables I get the error "the microsoft jet database engine could not find the object 'name of old access table'. Make sure the object exists and you spell its name and path name correctly"

There doesn't seem to be any way of properly updating the link between excel and access ?

Any suggestions?

Thanks