Using SQL Server 2008 R2.
I've got an Access database file configured as a linked server. I use this to push data from a data warehouse to an application used by some of our engineers. About once a quarter, the engineers take a snapshot of their data, create a copy of this backend, and save this copy to a new folder on the network. They then ask me to change the linked server to point to this new file.
I know, it seems like they could simply make a copy and save the copy but continue to populate the same database as the linked server datasource, but I cannot convince them to do that. So, I'm looking for a T-SQL command that would allow me to change the file that the linked server points to, without having to drop the linked server and create a new one.
When I open the properties dialog for the linked server, I cannot simply change the data source, as that field is disabled.