Solved

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

Posted on 2002-07-23
7
245 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Sireesha
Comment Utility
OOPS!I am using db2 not dql server
0
 

Author Comment

by:Sireesha
Comment Utility
sorry,sql server misspelled it.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
Comment Utility
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now