Using transaction scope with access database

6 Feb 2007 4:36 AM

Rash VV

Hi,

I am working on two databases one is in SQL server 2000 and one is in MS
access. My application is required to read data from the access file and
update the SQL database.
I want the complete operation to be done in a single transaction. For
that I have used TransactionScope class provided by .net 2.0.
The Application results in to the exception
"The ITransactionLocal interface is not supported by the
'Microsoft.Jet.OLEDB.4.0' provider".
It works fine if I remove the transaction.

my code is like :
using (TransactionScope scope = new TransactionScope())
            {
// database operations here
                               scope.Complete();
            }

Thanks in advance

*** Sent via Developersdex http://www.developersdex.com ***

6 Feb 2007 12:35 PM

Patrick Steele

In article <eZlHihaSHHA.4***@TK2MSFTNGP04.phx.gbl>,
vijayvargiy***@yahoo.com says...

> I want the complete operation to be done in a single transaction. For
> that I have used TransactionScope class provided by .net 2.0.
> The Application results in to the exception
> "The ITransactionLocal interface is not supported by the
> 'Microsoft.Jet.OLEDB.4.0' provider".
> It works fine if I remove the transaction.

Do you have a question about this behavior? If so, what is it?

--
Patrick Steele
http://weblogs.asp.net/psteele

6 Feb 2007 3:16 PM

Mary Chipman [MSFT]

The error message is correct -- you can't use TransactionScope with
Jet. If you need distributed transactions you should consider moving
your data to SQL Express. You can still use Access as a front-end to
the Express tables.

--Mary

On Mon, 05 Feb 2007 20:36:33 -0800, Rash VV <vijayvargiy***@yahoo.com>
wrote:

Show quote

>Hi,
>
>I am working on two databases one is in SQL server 2000 and one is in MS
>access. My application is required to read data from the access file and
>update the SQL database.
>I want the complete operation to be done in a single transaction. For
>that I have used TransactionScope class provided by .net 2.0.
>The Application results in to the exception
>"The ITransactionLocal interface is not supported by the
>'Microsoft.Jet.OLEDB.4.0' provider".
>It works fine if I remove the transaction.
>
>my code is like :
> using (TransactionScope scope = new TransactionScope())
>            {
>// database operations here
>                               scope.Complete();
>            }
>
>
>Thanks in advance
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***