Solved

transactions and Access

Posted on 1998-11-30
5
617 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 50 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Linking Master Child Fields 2 24
MS Access, hourly employee status 8 40
Item not found in this collection 5 29
Hidden queries with "~sq_f" and "~sq_r" 3 14
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 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