How to set up a DB connection to a remote DB not on the local machine

Hello, I am trying to adapt the following code to allow me to query one or more network databases. So far i have been testing the ADODB methods below on my local machine which is working 100%, but ultimately i would like to query a remote database sitting on the network at a known, static location.


I've tried altering the following statement to allow for remote connection without success (I pulled the entire routine shown at the bottom of this post from stackoverflow.com) -- this statement is where i declare the database name, username and password:

    objmyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Environ("computername") & "\myDBname;User ID=SQLUserName;Password=SQL_pswd;"

The ENVIRON("COMPUTERNAME") function returns my local machine name -- all computers i will be dealing with carry a SQL database with the same name (mydbname will be the same for all) and the sql "server" name for each machine is the computerName\mydbname.

I can connect to each remote DB in SQL directly - i have already added the ODBC connections for each on my computer -- and i've tried replacing the computer name part in the code above (environ("computername")) with the other machines computer name (which is the sql server name on that machine also) and I've tried putting in the IP address: Datasource=1.000.000.00\mydbname - doesn't work

Can anyone provide help on altering this code to allow for remote connections?

Private Sub GeneralSQLCaputureStatment()
    Dim stmt As String
    Dim outputSheet as worksheet
    Dim inputSheet as worksheet

Set outputsheet =Thisworkbook.worksheets("output test")
Set inputsheet=Thisworkbook.worksheets("input test")

'Build SQL Statement'
    'First half here'
        stmt = ""
        
'Set the ADODB variables'
    Set objmyconn = New ADODB.Connection
    Set objmycmd = New ADODB.Command
    Set objmyrecordset = New ADODB.Recordset
    
'Open Connection to LOCAL machine DB'
    objmyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Environ("computername") & "\myDBname;User ID=SQLUserName;Password=SQL_pswd;"
    objmyconn.Open
    
'Set and Excecute SQL Command - capture PROGRAM_TAB data
    Set objmycmd.ActiveConnection = objmyconn
    objmycmd.CommandText = "My SQL Query Here"
    objmycmd.CommandType = adCmdText
    objmycmd.Execute
    
'Open Recordset'
    Set objmyrecordset.ActiveConnection = objmyconn
    objmyrecordset.Open objmycmd
  
'Paste PROGRAM_TAB data to Excel'
    outputSheet.Range("A2").CopyFromRecordset (objmyrecordset)

'Close the Connection'
    objmyconn.Close

End Sub