Can I edit the Path to Linked Table?

[LG]

I have an excel doc that I have linked as a table to my database (2007) and
the excel doc is housed on an intranet page and is constantly updated with
new information. The filename has changed and I need to edit the path to the
table to bring in the new data.

I cannot do it through the linked table manager because the filename has
changed.

How can I change the link to look at a different file without having to edit
the query that's attached to it?

 

[Douglas J. Steele]

I'm not sure why you say you can't use the Linked Table Manager: that's
essentially what it's for.

Or are you saying that you must use HTTP to get to the file? Afraid that's
not possible.

 

[LG]

No, It is an xlsx spreadsheet that I used "get external data" from within
excel to pull from my database. It just happens to live in a folder on a
intranet site for editing.

I then linked the table from within access and ran a query to update a
larger table with new data from the excel sheet. It worked fine but then
someone changed formatting in the excel sheet and it compromised the way the
data was appearing when refreshed.

So, I had to reformat and save as a different file. I want to relink to the
new sheet using the already linked table in order to not have to redo my join
query.

Can I have the old linked table and want to change the properties so that it
see's the new file? Is it possible to do this?

Thank you

 

[Douglas J. Steele]

If the changes to the spreadsheet changed anything about the linked table,
it's possible you'll have to link to the new spreadsheet using a different
name, then create a query that returns the original formatting. Give the
query the same name as the original linked table (which you'll have to
delete first).