Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: linked server, DTC, SQLOLEDB, Access, linked tables = HEADACHE!

    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.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Can you call an SP to do the update?
    set transact abort on gets round some transaction incorporation problems but I doubt if it would solve this.

  3. #3
    Join Date
    Jan 2003
    Posts
    4
    no, I can't do an SP in this situation. It's just MS Access updating a linked table (view). I did try to find a way to do this with an SP to employ the trasact abort thing, but it didn't apply.

  4. #4
    Join Date
    Jan 2003
    Posts
    4
    I think I'm going to X-Post this in the usenet forums. Seems to be a lot of traffic in there. I'll be a nice x-poster though, if I get an answer in one forum, i'll post it in the other.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •