?
Solved

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

Posted on 2002-07-23
7
Medium Priority
?
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

764 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