OK, welcome to my nightmare.... I got 2 SQL Servers and an Access DB Front end
SQL Server1: SQL Server 7.0 (on our network)
SQL Server2: SQL Server 2000 (remote)
AccessDB: on our network
There is a Form in the Access DB that is used by Sales & Marketing to update data in both SQL Servers and there are subqueries involved. I tried just linking the tables in Access, but the connection to the remote SQL Server keeps getting dropped within seconds of opening it regardless of whatever timeouts I set anywhere. The linked tables to the local SQL Server stay connected just fine (both DSNs on the Access machine are using TCP/IP).
So, I tried adding SQL Server2 as a Linked Server to SQL Server1, then write a distributed view object (SELECT * FROM [linkedserver].[catalog].[dbo].[table]) the table I need. Then I create a linked table in Access to the new view. Well I can SEE the data fine (SELECT), but in order to update the data, it evidently requires DTC, but since it's SQLOLEDB (all together now) "does not support distributed transactions." Yup DTC is running just fone on both SQL Servers, nope we can't upgrade the SQL Server 7.0
If anyone has ANY insight into this mess, I'm all ears.