Do we have transactions in MS-Access?

I am developing a small desktop application using C#.NET and MS-Access. I don't have any prior experience of MS-Access. I want to know if we can use transactions in Ms-Access or not.

I have the below mentioned situation.

Insert in Tbl1
Insert in Tbl2

I want to insert in tbl2 only when insertion in tbl1 is successful. And if there is some exception during insertion in tbl2, I want to rollback the insertion in tbl1.
I know this can easily be achieved in sql-server, but in case of ms-access, How should I manage this. Please Help, Thanks in advance.

-------------Problems Reply------------

It looks like we do: MSDN - TRANSACTION Statement (Microsoft Access SQL)

Transactions are not started automatically. To start a transaction, you must do so explicitly using:

BEGIN TRANSACTION

Conclude a transaction by committing all work performed during the transaction:

COMMIT [TRANSACTION | WORK]

Conclude a transaction by rolling back all work performed during the transaction:

ROLLBACK [TRANSACTION | WORK]

Nobody has actually given you any code examples here in the answer or even cited an example (the Access help files do include examples, though). The key issue to keep in mind is that in Jet/ACE (Access does not support transactions itself -- it depends on whatever database engine you're using for that) that the transaction is controlled at the workspace level. You can create a new workspace for your transaction or create a new one. Here's some sample code:

On Error GoTo errHandler
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim lngInvoiceID As Long

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(CurrentDb.Name)
With wrk
.BeginTrans
db.Execute "INSERT INTO tblInvoice (CustomerID) VALUES (123);", dbFailOnError
lngInvoiceID = db.OpenRecordset("SELECT @@IDENTITY")(0)
db.Execute "INSERT INTO tblInvoiceDetail (InvoiceID) VALUES (" & lngInvoiceID & ")", dbFailOnError
.CommitTrans
Debug.Print "Inserted Invoice header and detail for Invoice " & lngInvoiceID
End With

exitRoutine:
If Not (db Is Nothing) Then
db.Close
Set db = Nothing
End If
Set wrk = Nothing
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in transaction"
wrk.Rollback
Resume exitRoutine

(code tested and working within Access)

Yes Microsoft Access supports Transactions and they work quite well. I built a commercial POS application using Access as my database several years ago, and the transaction support worked very well.

Even so, if possible, I would use SQL Server Express. It's free and much more powerful than Access.

Example for OleDbTransaction on MSDN is here http://msdn.microsoft.com/en-us/library/93ehy0z8.aspx

Category:c# Views:0 Time:2010-01-16

Related post

  • transactions in MS Access 2010-05-13

    Let's say I have the following code in a form that is triggered on some click event. DoCmd.SetWarnings False DoCmd.OpenQuery "AddSomeStuff" DoCmd.OpenQuery "UpdateSomeOtherStuff" DoCmd.OpenQuery "DeleteABunchOfCrap" DoCmd.SetWarnings True Can I assum

  • The chosen transaction strategy requires access to the JTA TransactionManager or Unable to build EntityManagerFactory 2010-11-18

    I am using Spring 2.0.6 and Hibernate 3.2.x on apache tomcat5.5,now we planed to changed our hybernate mapping files into hybernate+jpa support mapping files. for that we created files like this daoConfig.xml <beans:bean id="dataSource" class="org

  • Are there any alternatives to COM+ transaction based data-access layer? 2011-11-16

    We are using our data-access layer with COM+. It's suboptimal. But the reasons to keep it are that it's transaction-secure. Are there any other alternatives to transcation-secure data-access handling? --------------Solutions------------- TransactionS

  • Access 2007 - "Could not update; currently locked" 2008-12-17

    First sorry for my bad english but I'll try my best. Right now I'm programming a .net application using Access 2007 as datastore. In a nutshell: I have two threads. One thread inserts a row with a transaction into a table. The other thread updates ma

  • MySQL transaction support with mixed tables 2009-01-01

    It seems like I will be needing transaction with MySQL and I have no idea how should I manage transactions in Mysql with mixed InnoDB/MyISAM tables, It all seems like a huge mess. You might ask why would I ever want to mix the tables together... the

  • Access DB - Operation must use updateable query 2009-01-26

    I have a website I've built in VS2005, C#, .NET 2.0. This website does transactions against an Access database. It works fine in development. The mdb file resides in App_Data I have setup IIS on this machine to test the website as it would be deploye

  • Sharing JMS and Hibernate transactions in a Spring MDB using Oracle Streams AQ? 2009-08-03

    I'm using Oracle 11g for my database and its Oracle Streams AQ feature as JMS implementation. For all I know, it should be possible to implement a Spring based message-driven POJO (MDP) that uses the same data source for both transactional data acces

  • Should network DTC be enabled for oracle transactions 2009-08-31

    I have a WCF based web service hosted in windows sever 2003 machine. The database is Oracle 10G on solaris. The web service uses transactionscope extensively and ado.net transactions in some places. Most of the transactions involve only one resource

  • MSDTC problem with transactions in ADO.NET Entity Framework 2009-10-23

    in our current project we are using ADO.NET Entity Framework as data layer for the application. There are some tasks which require to run in a transaction because there's a lot of work to do in the database. I am using a TransactionScope to surround

  • Java Solutions for Distributed Transactions and/or Data Shared in Cluster 2009-10-23

    What are the best approaches to clustering/distributing a Java server application ? I'm looking for an approach that allows you to scale horizontally by adding more application servers, and more database servers. What technologies (software engineeri

  • ADO.NET database access 2009-12-08

    I have written a program in VB.NET and one of the things this program does is insert records into a Microsoft Access database. The backend of my program that access the database is written as an interchangeable layer. If I "swap" this layer out with

  • TRANSACTION ISOLATION LEVEL REPEATABLE READ in SQL Server 2010-01-28

    What are the risks or performance degradation when using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ? We have some SP that are called from BizTalk services. We are getting deadlock sometimes. If we change the isolation level, what are the risks a

  • What is the purpose of Managers / Transactions? 2010-06-10

    I'm building a spring application for the first time. I'm running into lots of problems with concurrency, and I suspect that there is something wrong with the way I'm managing the backend. The only difference I can see between my backend code and exa

  • transaction problem with entity framework 4 2010-09-29

    I'm trying to implement a transaction with entity framework 4. From what I've read, the code below is correct. The SaveChanges works fine but as soon as I hit the first ExecuteFunction call I get the following exception: The underlying provider faile

  • Network access disabled in MSDTC 2010-12-14

    I am using System.Transactions.TransactionScope() in a unit test to insert a record, attempt to read it, and let the transaction scope rollback so as to leave no footprints behind in the database. 1 var rep = new RunRepository(); 2 using (var scop =

  • grails getting lazzy initialization exception in transactional service 2011-02-23

    I'm getting a typical hibernate Lazy initialization exception in my transactional service while accessing propert of an object of a SET. org.hibernate.LazyInitializationException: could not initialize proxy - no Session class ProductService { static

  • Spring @Transactional - JPA context in transaction problem 2011-03-24

    I'm using Spring + JSF + JPA configuration hosted on Glassfish v3.1. I'm experiencing some strange (at least for me) behavior of @Transactional annotation. Here is my simplified example: @Transactional public void associateGroupToRole(String role, St

  • SQL Microsoft Access 2011-04-16

    I have a table of transactions in Microsoft Access that contains many transactions for many vendors. I need to identify if there is sequential transaction numbering for each vendor. I don't know what the sequence will be or the number of transactions

  • Can a SQL "DELETE" statement followed by a "WHERE NOT IN" statement be in same transaction? 2011-05-02

    If I have the following SQL block (in SQL SERVER 2008 R2): BEGIN BEGIN TRAN DELETE FROM dbo.fooData WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE()) COMMIT BEGIN TRAN DELETE FROM dbo.barData WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.f

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.151 (s). 11 q(s)