Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2002-03-24
9
Medium Priority
?
340 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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

670 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