Post reply

Problem with MS Access linked database

  • December 13, 2004 at 2:53 pm

    #87241

    I have linked MS Access 2000 database to my SQL Server 2000 Standard edition. I would like to perform very simple update on Access table via trigger on my SQL database table:

    CREATE TRIGGER [TestTrigger]

    ON dbo.TestTable

    FOR INSERT, UPDATE

    AS

    UPDATE LinkedDB...Test

    SET TestField = GetDate()

    WHERE ID = 1

    GO

    Trigger operation fails with message:

    «[Microsoft][ODBC SQL Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLE.DB.4.0' IUknown::QueryInterface returned 0x0004002] [Microsoft][ODBC SQL Driver][SQL Server]The requested operation could not be performed because the OLE DB Provider 'Microsoft.Jet.OLE.DB.4.0' does not support the required transaction interface.»

    But when we execute the same code in stored procedure or from Query Analyzer, everything works fine:

    UPDATE LinkedDB...Test

    SET TestField = GetDate()

    WHERE ID = 1

    Any help will be appreciated.

  • December 13, 2004 at 3:07 pm

    #533654

    The problem is with distributed transactions (DTC).  When you want to update a separate database within the context of a SQLServer transaction this thing (DTC) wants to manage it...

    So it is how your DTC is set up and/or the access driver.

  • December 15, 2004 at 10:54 am

    #533997

    Thank you John, I'll try to follow your instruction.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply