Problem with Transaction Management

[Cyril Gupta]

Hello,

I am having a little problem here with concurrent connections to the database.

My application has a permanent connection to the database that I am using
to Retrieve information. All of my updations are done using transactions
and for that I create a new connection to the database (as some of my update
operations also retrieve info).

Now this seems to work just fine on paper - the transaction to the new connection
works fine, and the older connection also retrieves data fine.

The problem is that once I Commit a transaction, it takes a little while
for it to update the MS Access DB that I have. Meanwhile when my former connection
retrieves information, it fetches older data, some of which is invalidated.

So basically I want to make sure that after committing a transaction from
another connection, I still get the same data when I query next.

I have tried using IsolationLevel(ReadUncommitted)... But that's not working
for me.

Any ideas?

Regards,
Cyril Gupta
- You can do anything with a little bit of 'magination.
- I've been programming so long, my brain is now soft-ware.

 

[Cyril Gupta]

Hello,

I could solve this issue by closing the connection to the Transaction object,
then closing and re-opening my original connection.

I am putting this here just in case there's anyone else who's having the
same issue as me.

Another method I found was by using the JRO.JetEngine objects, but that's
an interop, and I am trying to avoid Interop in this application. It needs
a ADO Connection object. (JRO.JetEngine.RefreshCache(Connection))

Regards,
Cyril Gupta
- You can do anything with a little bit of 'magination.

- I've been programming so long, my brain is now soft-ware.

CG> Hello,
CG>
CG> I am having a little problem here with concurrent connections to the
CG> database.
CG>
CG> My application has a permanent connection to the database that I am
CG> using to Retrieve information. All of my updations are done using
CG> transactions and for that I create a new connection to the database
CG> (as some of my update operations also retrieve info).
CG>
CG> Now this seems to work just fine on paper - the transaction to the
CG> new connection works fine, and the older connection also retrieves
CG> data fine.
CG>
CG> The problem is that once I Commit a transaction, it takes a little
CG> while for it to update the MS Access DB that I have. Meanwhile when
CG> my former connection retrieves information, it fetches older data,
CG> some of which is invalidated.
CG>
CG> So basically I want to make sure that after committing a transaction
CG> from another connection, I still get the same data when I query
CG> next.
CG>
CG> I have tried using IsolationLevel(ReadUncommitted)... But that's not
CG> working for me.
CG>
CG> Any ideas?
CG>
CG> Regards,
CG> Cyril Gupta
CG> - You can do anything with a little bit of 'magination.
CG> - I've been programming so long, my brain is now soft-ware.