Solved

How do i know if the connection object has any active transactions?

Posted on 2002-07-23
7
248 Views
Last Modified: 2010-05-02
Hi,
   i want to find out if a connection object has any active transactions to commit or rollback.i am diong something like this
sub X()
on error goto errorhandle
conn.begintrans
set rs conn.open "select........"
................
.................
sql = "update......."
conn.execute sql
conn.committrans
exit sub
errorhandle:
 if conn.errors.count > 0 then
    conn.rollbacktrans
   msgbox("Update failed")
   exit sub

 end if
end sub

I am getting this error
  "Run time error no active transactions"
Please some one help me.Thanks
0
Comment
Question by:Sireesha
7 Comments
 

Expert Comment

by:wensinkg
ID: 7173239
What database provider are you using? According to Microsoft if the provider doesn't support transactions you will get an error for trying to call BeginTrans.  This could be your problem.  Try checking the "Transaction DDL" property in the connection object.  If this value is adPropNotSupported (0) that means that the property is not supported and you will have to find some other way to manage your transaction.

HTH

Gary
0
 

Author Comment

by:Sireesha
ID: 7173293
wensinkg,
          I am using sql server.There are no errors in no of modules.it works fine.But,if there is a different database error and i try to rollback the transaction that is when it is giving me an error that says no active transaction mode.Please let me know your thoughts on this.I can post the exact errors tomorrow.I will try to debug and get more info.Thanks

0
 

Expert Comment

by:Amitabh
ID: 7173806
well i think since there is nothing to update, there is no transaction and hence you are facing this error.

one possible way can be to use connectionobject.errors and check for this error and subsequently take an appropriate action

or to use @@trancount global variable in sql server to find out number of active transactions. like using
rs.open "select @@trancount" and then accessing rs(0) to see if there is any active transaction.

hope this should work though frankly i have not tried this at my end :-)
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Sireesha
ID: 7174448
OOPS!I am using db2 not dql server
0
 

Author Comment

by:Sireesha
ID: 7174516
sorry,sql server misspelled it.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8040442
Hi Sireesha,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

Sireesha, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8095736
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question