Solved

can anyone help me how to use begintrans in ADO with Remote Provider

Posted on 2002-03-24
9
294 Views
Last Modified: 2013-11-13
Hello sir, i am using an access2000 database in my local server and an MSSQL
Server database in net, while data transactions, i 'll be selecting some
records and inserting in my net database, for that i have to use begin trans
with my net server, i have connected to the database with connString ="provider=MS
Remote;dsn='Online';uid='user';pwd='';remote provider='SQLOLEDB.1'"
like wise but f i give db.begintrans i got an error as the provider doesnt
support ... can anyone help me...bcoz, i have to ensure the data transactions....if
anything
goes off, it should be rollback....
with expectations
Karthik.P


0
Comment
Question by:am_karthik
9 Comments
 
LVL 2

Expert Comment

by:dirkmartin
ID: 6892810
Place your begintrans on the recordset, not the database.  If you have:
Dim RS as recordset
Dim DB as database

Rather than db.begintrans, do: rs.begintrans
0
 
LVL 3

Expert Comment

by:Bahnass
ID: 6892812
Not Sure but

db.cursortype = aduseclient
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6892983
If using ADO, transactions are controled through the connection object.

e.g.

Dim CN as ADODB.Connection
Dim SQL as string
Dim OpFailed as Boolean

OpFailed=False
On Error Resume Next

CN.BeginTrans

SQL="Insert Into MYTable (Field1,Field2) Values(10, 20)"

CN.Execute SQL
if Err.Number = 0 Then
   CN.CommitTrans
End If
If Err.Number <> 0 Then
   CN.RollBack
   OpFailed=True
End If

If OpFailed Then
   Msgbox "Transaction aborted"
   exit sub
End if

You can also create tables and do all sorts of things within transactions.

0
 
LVL 2

Expert Comment

by:VincentWong
ID: 6893559
If you are using SQL Server 2000, SET XACT_ABORT would be a alterative for BeginTrans.

0
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.

 

Author Comment

by:am_karthik
ID: 6894320
Hello sir, thanx for your information...but i was using a connection object and i will be connecting to a net server(SQL Server)and i will be checking for a registration tag and i shall be creating tables and inserting values for five tables one by one....so if any disconnection i have to roll back all the transactions....and if i going for registration again... the process should be started from first table...so.. i cant go by ecordset.. instead i have to go with conn.begintrans.
 i am not clear abt your alternate information that is
"SET XACT_ABORT "..can you guide me more...
with thanx and expectations...
karthik.P
0
 
LVL 2

Accepted Solution

by:
VincentWong earned 50 total points
ID: 6895423
SET XACT_ABORT

Specifies whether Microsoft® SQL Server™ automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error.

Syntax
SET XACT_ABORT { ON | OFF }

Remarks
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

Examples
This example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, the SET XACT_ABORT setting is turned ON. This causes the statement error to terminate the batch and the transaction is rolled back.

CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO

SET XACT_ABORT ON
GO

BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO

/* Select shows only keys 1 and 3 added.
   Key 2 insert failed and was rolled back, but
   XACT_ABORT was OFF and rest of transaction
   succeeded.
   Key 5 insert error with XACT_ABORT ON caused
   all of the second transaction to roll back. */

SELECT *
FROM t2
GO

DROP TABLE t2
DROP TABLE t1
GO
0
 

Author Comment

by:am_karthik
ID: 6896726
Hello Sir... thanx for your information... i shall workit out and get you....once again i thank you....
with Love
karthik.P
0
 
LVL 16

Expert Comment

by:twalgrave
ID: 7753608
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- points to VincentWong
Please leave any comments here within the
next seven days.
0
 

Expert Comment

by:SpideyMod
ID: 7809977
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

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

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 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…

747 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

9 Experts available now in Live!

Get 1:1 Help Now