Query linked SQL Server from Excel VBA?

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,304
Messages
5,635,417
Members
416,857
Latest member
m2pk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top