Query linked SQL Server from Excel VBA?

[JeffK627]

We have a set of Excel files that query a SQL Server database. They were all created from a template and all use the same connection string. Now that server is being decommissioned and the new server will follow a different naming convention, so the server name will change. The problem is that there are over 10,000 files using the existing connection string and no simple way to do a global replace for several reasons.

How can I keep 10,000+ files from breaking when the server name is changed? If we can get the DBAs to set up a linked server using the old server name will VBA be able to use that and keep running with the current connection string?

So if the server name changes from ABCNT3 to ABCSQL16P1, will "Data Source = ABCNT3;" still work if a linked server named ABCNT3 pointing to ABCSQL16P1 is created?

 

[Grumpy Old DBA]

No.
You would need to connect to the new server first and to be able to reach the linked server.

What you want is a SQL Server alias. See either of these links:
https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/
https://docs.microsoft.com/en-us/sq...lias-for-use-by-a-client?view=sql-server-2017

 

[Grumpy Old DBA]

Since you have DBAs, just ask them to set up the SQL Server alias.