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

x
?
Solved

transactions and Access

Posted on 1998-11-30
5
Medium Priority
?
624 Views
Last Modified: 2008-02-26
Hi Guys,

does Access support transactions (begin, commit and rollback transaction) ??
If yes, what isolation level is supported ??

Thanks in advance

Nick
0
Comment
Question by:nl1545
[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
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:gazi
ID: 1969646
yes Access does support transactions
What do you mean but isolation level??


0
 

Author Comment

by:nl1545
ID: 1969647
transactions are a means to ensure multi-user functionality without anomalities regarding the data. the ideal case is when a transaction can behave as if it is the only one on the database. that means for instance nobody will see a change to a table unless the transaction is committed. This case would be considered a "dirty read" because somebody reads data from a transaction that could be rollbacked.
In short: level of isolation determines whether each change is postponed until the transaction is committed and to what degree changes made by other transactions are visible during your transaction's time span.
Normally (ideal case, ensured in big backend database systems like Oracle, SQLServer, Informix), transactions have to follow the ACID principle which stands for:

A...tomicity  (either the whole transaction is done or nothing)
C...onsistency  (one transaction transforms the database from one consistent situation to another one which is consistent (logically and physically)
I...solation (all parallel transactions don't influence each other)
D...urancy  (a committed transaction ca't be rollbacked because it's committed)

But small desktop databases like Access probably don't have a transaction manager like big ones, so maybe not all principles are followed.
0
 
LVL 9

Accepted Solution

by:
perove earned 200 total points
ID: 1969648
Yes, Access support transcations.
NOT on the non NATIVE ISAM formats (NB) but for the native Access and for most of the ODBC it does.

So to the isolation levels supported:
It can be up to five levels deep, and you can have two(or more) independent transactions if you use two(or more) workspaces.
(can this be equal to Isolation)

I guess that Atomicity are appied with access, if you inside a transaction discover a ex. lock on one of the record you have to trap the error and roll back the whole transaction.

Closin' a workspace without commiting the transac. and the whole transct. are automaticly rolled back.

Hope this helps,
I dont think Access is unsin the terms in the ACID model in thie documentation, let me know if this was the info you where looking for

perove
0
 

Author Comment

by:nl1545
ID: 1969649
what does you mean with workspace ??
actually, I am programming a online database, unfortunately has the firm no own webserver to use SQLServer. that means we use a provider and have uploaded a Access database (.mdb-file). I am accessing this by using Active Server Pages (ODBC-Connection). The only objects I use therefore are ADODB.Connection, ADODB.Recordset and others  but there is no Workspace - object. So I just wanted to use SQL Statements ("begin transaction", "commit transaction", "rollback transaction") for the transactions. Normally I am used to the following:

1. Opening connection.
2. Executing statements against this connection
3. Eventually, processing the results

My question is now: Is it possible that two or more users can access the database parallel (means two or more parallel transactions) ?
As I said I don't have a workspace. I assume this is the case when programming with VisualBasic directly against Access, but I don't believe that workspaces are a part of ODBC

Normally, when working with "real" transactions you don't have to trap errors when requesting a locked object. In this case the requesting transaction has to wait till the working transaction has either finished or rollbacked.

0
 
LVL 9

Expert Comment

by:perove
ID: 1969650
Oh..I see, so that you are relly wondering weather ADO supports transaction.
( You are again correct, workspaces are when you are using the native jet connection, but i assume that the same rule goes for ADODB.connections)
But if you connect trough two recordset using TWO connections you can do a paralell transaction.

Take a look at
http://support.microsoft.com/support/kb/articles/q180/8/43.asp?FR=0

That goes for vb using a adodb.connection to sqlserver but I assume that the same is happen for you trough access /odbc

perove
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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