Microsoft.Jet.OLEDB.4.0 Linked Server Transactions

Hi All,

I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)

BEGIN TRAN

INSERT INTO ACCES...Table

Msg 7390, Level 16, State 2, Line 2

The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "HES" does not support the required transaction interface.

I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface.

The main question is - does MS Access linked servers support transactions?

Thank you for your help,

I have exausted all resources available to me in finding out the answer.

Thanks,
Eric

Hi,

as far as I can remember Access does not support DT.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

This really is bad news for me. The previous interface was just dropping a file on the network and then access had a utility to import and update, however this methodology was horrible. If any step errored then it was a nightmare to clean up the mess and restart, and the data is so out of sync it is ridiculous. I am trying to find a more ACID way.

Maybe I can use the linked server to prepare some temp tables, then write a utility in C#.net to do the update. I bet the connection in .net can support non-distributed transactions.

Microsoft, I love you and hate you.