I have an ADO connection to a SQL server database.
Physical Server Name = XYZ
SQL Server instance = XYZ\ABC
Database = MyDatabase
Table = tblMyTable

All connects fine with
Provider='SQLOLEDB';Server='XYZ';Data Source ='XYZ\ABC';
Initial Catalog='MyDatabase';

And any queries run OK with data returned to the spreadsheet.

However there are two methods of connecting, one using (as I understand
it) Dynamic ports and one using static ports. The machine (Client) that
it works on uses a static port for the XYZ machine.
(I can determine this from the ODBC connection even though I don't use
ODBC)
but a machine that uses dynamic ports on ODBC doesn't connect to the
server with the above connection string.
Error message: 'Specified SQL Server not found'
I have tried replacing the Server name with the IP address as
determined from using Ping XYZ (works fine).
I know the ODBC is a red herring but it seems to be the only thing that
I can think of that may give a clue.
Is there a different syntax that I should be using ?
The whole idea is to avoid having to configure other machines when
distributing the spreadsheet.

Thanks