Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

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
0
nl1545
Asked:
nl1545
  • 2
  • 2
1 Solution
 
gaziCommented:
yes Access does support transactions
What do you mean but isolation level??


0
 
nl1545Author Commented:
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
 
peroveCommented:
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
 
nl1545Author Commented:
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
 
peroveCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now