3 capas con 2 servidores SQL Prueba-Desarrollo

Con la api CoCreateInstanceEx. Te pego un módulo para hacerlo.
'''''''''''''''''''''''''''''''''
Option Explicit
Private Type GUID
Data1 As Integer
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type
Private Type COSERVERINFO
dwReserved1 As Long 'DWORD
pwszName As Long 'LPWSTR
pAuthInfo As Long 'COAUTHINFO *
dwReserved2 As Long 'DWORD
End Type
Private Type MULTI_QI
pIID As Long 'const IID*
pItf As Object 'IUnknown *
hr As Long 'HRESULT
End Type
Public Enum CLSCTX
CLSCTX_INPROC_SERVER = 1
CLSCTX_INPROC_HANDLER = 2
CLSCTX_LOCAL_SERVER = 4
CLSCTX_REMOTE_SERVER = 16
CLSCTX_SERVER = CLSCTX_INPROC_SERVER + CLSCTX_LOCAL_SERVER +
CLSCTX_REMOTE_SERVER
CLSCTX_ALL = CLSCTX_INPROC_SERVER + CLSCTX_INPROC_HANDLER +
CLSCTX_LOCAL_SERVER + CLSCTX_REMOTE_SERVER
End Enum
'This is IDispatch's GUID -- Do not ever change this !!
Private Const IID_IDispatch As String =
"{00020400-0000-0000-C000-000000000046}"
Private Declare Function CLSIDFromString Lib "OLE32" (ByVal lpszCLSID As
Long, pclsid As GUID) As Long
Private Declare Function CLSIDFromProgID Lib "OLE32" (ByVal lpszProgID As
Long, pclsid As GUID) As Long
Private Declare Function CoCreateInstanceEx Lib "OLE32" (rclsid As GUID,
ByVal pUnkOuter As Long, ByVal dwClsContext As Long, pServerInfo As
COSERVERINFO, ByVal cmq As Long, rgmqResults As MULTI_QI) As Long
Public Function CreateObjectEx(ByVal Class As String, Optional ByVal
Server As String = "") As Object
Dim rclsid As GUID
Dim riid As GUID
Dim hr As Long
Dim ServerInfo As COSERVERINFO
Dim mqi As MULTI_QI
Dim Context As Long
' Convert IID_IDispatch string to binary IID
hr = CLSIDFromString(StrPtr(IID_IDispatch), riid)
' MsgBox "CLSIDFromString = " & hr
If hr 0 Then Err.Raise hr
mqi.pIID = VarPtr(riid)
' Convert provided CLSID or ProgID string into a binary CLSID
If ((Left(Class, 1) = "{") And (Right(Class, 1) = "}") And (Len(Class)
>= 38)) Then
' Create CLSID from string representation
hr = CLSIDFromString(StrPtr(Class), rclsid)
If hr 0 Then Err.Raise hr
Else
' Create CLSID from ProgID
hr = CLSIDFromProgID(StrPtr(Class), rclsid)
If hr 0 Then Err.Raise hr
End If
' Setup appropriate context
If Server = "" Then
Context = CLSCTX_SERVER
Else
Context = CLSCTX_ALL 'SERVER 'CLSCTX_REMOTE_SERVER
ServerInfo.pwszName = StrPtr(Server)
End If
' Create an instance of the object
hr = CoCreateInstanceEx(rclsid, CLng(0), Context, ServerInfo, CLng(1),
mqi)
MsgBox "CoCreateInstanceEx = " & str(hr)
' If hr 0 Then Err.Raise hr
Set CreateObjectEx = mqi.pItf
End Function
'''''''''''''''''''''''''''''''''''
Luego para usar la función
Dim x as Object
'Para que corra en la máquina remota:
Set x = CreateObjectEx("ClassID", "Machine_Name")
'Para que corra en una local:
Set x = CreateObjectEx("ClassID")