?
Solved

transactions and Access

Posted on 1998-11-30
5
Medium Priority
?
628 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
  • 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

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.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

569 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