Link to home
Start Free TrialLog in
Avatar of nl1545
nl1545

asked on

transactions and Access

Hi Guys,

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

Thanks in advance

Nick
Avatar of gazi
gazi

yes Access does support transactions
What do you mean but isolation level??


Avatar of nl1545

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of perove
perove
Flag of Norway image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nl1545

ASKER

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.

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