Solved

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

Posted on 2002-03-24
9
310 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
[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
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
Industry Leaders: 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!

 
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
 

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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SLMGR Switches Are Not Working On KMS Host 3 139
need help to install vb6.0 7 86
RUNRMTCMD from AS/400 13 89
Problem to line 23 73
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 …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

761 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