Linked table to an Excel file won't hold the connection if the Excel file is moved or renamed

[cliff.clayman]

I'm not sure if this is the right place to ask this question, so please feel free to move it to the appropriate spot. I have an Access database that has a linked Excel file. I want to be able to make changes in the Excel file and then have them updated in the Access table. However, I have multiple users for the Excel file and they are apt to change the name of the file or move the file. When that happens, the data does not get updated in the Access database. Is there anyway to maintain the link if the Excel file is moved and/or renamed?

[John_G]

Unfortunately, no. Moving or renaming an Excel file won't automatically update the link in an Access database. You would have to fix the manually in the Access database.

I suppose you could always try to educate your users....... but that's a bit like trying to train a cat.

[cliff.clayman]

Unfortunately, no. Moving or renaming an Excel file won't automatically update the link in an Access database. You would have to fix the manually in the Access database.

I suppose you could always try to educate your users....... but that's a bit like trying to train a cat.

Is there a way with VBA to create a Submit button on the Excel file that will update the Access table no matter what the user may do to the file?

[John_G]

Is there a way with VBA to create a Submit button on the Excel file that will update the Access table no matter what the user may do to the file?

If the Access database links to the Excel file, then there is no Access table to update - that is how linking works. In Access, the linked spreadsheet looks like a table, and can be used like a table (not updated, though), but it isn't in the Access database file.

If you are referring to using VBA in Excel to update the link information in the Access database, then that might be possible, but all you would get would be the latest value, which might or might not work in the database.

If your users are apt to move or rename the Excel file, how likely are they to do something worse, like make their own copies?

[Perceptus]

Sound like you want Sharepoint. Where you can submit your data to a common location for it to be reviewed as a whole, and have that data in turn used in a database that pulls from sharepoint.

[cliff.clayman]

I was able to write some VBA to suffice for my needs. We do use Sharepoint, but apparently that is an issue for some reason unknown to me.