Hello.

I don't understand much about transactions and I would really like to know
if commit/rollback can help guard against corruption of the back end, and if
so what is the general procedure to make this happen.

Many thanks in advance.

With Jet? I would say that I don't see in what the transactions exposed to
us could help against corruption. At least, if the corruption comes from a
lost of connection, there is not mechanic I am aware of, which time out a
started but uncommitted transaction. On the other hand, if you lost the
connection after you started a transaction, it is unlikely that you will be
able to get back the same connection... to roll it back.... but why will you
roll it back if you can then continue to deal with the database and finally
commit the transaction?


The goal of a transaction is to make a complex operation appears as ONE
operation. As example, you have to append data in tableA, then erase it from
tableB. But observe that, in theory, the system may fall after you append in
tableA but before you erase it from tableB and that could 'invalidate' your
data (as if you send 1 M$ to clientA and fail to debit clientB, you will be
losing 1M$). So, will you start a transaction, append in tableA, delete in
tableB, and commit your transaction. If there is a failure while the
transaction is active, nothing outside the transaction will be influenced by
it. Note that technically, appending data in tableA and not being able to
delete it from tableB is not "corruption" of data, even if it creates a
state you don't want. Corruption is more like a wrong data type, an index
pointing to the wrong data, a unique constraint or a data referential
integrity rule that is not respected, or something like that.


Vanderghast, Access MVP


"JString" <(E-Mail Removed)> wrote in message
news:22DCA45B-F1BC-4115-BD05-(E-Mail Removed)...
> Hello.
>
> I don't understand much about transactions and I would really like to know
> if commit/rollback can help guard against corruption of the back end, and
> if
> so what is the general procedure to make this happen.
>
> Many thanks in advance.

That was a *very* good explanation, Michel. Thank you for that!

I'm wondering if there are any general "guidelines" for when one should use
(or not use) transactions? Some folks (he said, pointing at himself) can get
carried away and overdo it if they don't have some guidelines to help them
decide when a transactional methodology is better (or worse) than just doing
it a regular way.

Fot instance, I'm creating a program that includes a subroutine that creates
a fiscal period table (fiscal period number, period start and end dates,
Yes/No field for whether the period is closed, etc.). The routine takes the
fiscal year start date supplied by the user (e.g. January 1st of any year)
and does 12 loops to create each row in the table. (Btw, the routine
accounts for fiscal years that don't start on January 1st and automatically
adjusts for leap years, which I thought was pretty cleaver of myself. (He
said proudly!))

Anyway, I'm thinking that maybe it might be a "good idea" to use a
transaction to create the table? That way, if something pukes during the
process, the table won't end up partially completed. Would *that* be an
example of an appropriate use for transactions? What are some other examples
of when (or when not) to use transactions?

Thanks, in advance, for your guidance and teaching...

Regards, Chris

"Michel Walsh" wrote:

> With Jet? I would say that I don't see in what the transactions exposed to
> us could help against corruption. At least, if the corruption comes from a
> lost of connection, there is not mechanic I am aware of, which time out a
> started but uncommitted transaction. On the other hand, if you lost the
> connection after you started a transaction, it is unlikely that you will be
> able to get back the same connection... to roll it back.... but why will you
> roll it back if you can then continue to deal with the database and finally
> commit the transaction?
>
>
> The goal of a transaction is to make a complex operation appears as ONE
> operation. As example, you have to append data in tableA, then erase it from
> tableB. But observe that, in theory, the system may fall after you append in
> tableA but before you erase it from tableB and that could 'invalidate' your
> data (as if you send 1 M$ to clientA and fail to debit clientB, you will be
> losing 1M$). So, will you start a transaction, append in tableA, delete in
> tableB, and commit your transaction. If there is a failure while the
> transaction is active, nothing outside the transaction will be influenced by
> it. Note that technically, appending data in tableA and not being able to
> delete it from tableB is not "corruption" of data, even if it creates a
> state you don't want. Corruption is more like a wrong data type, an index
> pointing to the wrong data, a unique constraint or a data referential
> integrity rule that is not respected, or something like that.
>
>
> Vanderghast, Access MVP
>
>
> "JString" <(E-Mail Removed)> wrote in message
> news:22DCA45B-F1BC-4115-BD05-(E-Mail Removed)...
> > Hello.
> >
> > I don't understand much about transactions and I would really like to know
> > if commit/rollback can help guard against corruption of the back end, and
> > if
> > so what is the general procedure to make this happen.
> >
> > Many thanks in advance.
>
>
>

Most corruption is caused by user errors. For example,
deleting the wrong record. Or updating the wrong record.
Or Adding the wrong record.

That happens, and your data is wrong: it is no longer
true. It is corrupt.

Access no longer guards against your computer failing
or being turned off while you are using it (as it did in Access
2.0), so the only kind of corruption transactions now
prevent is the kind of corruption that occurs when you
have a programming or data error that halts a complex
sequence of actions, leaving some of your tables updated
and some of your tables not updated. This is database
corruption: your data is corrupt.

A transactional file system like Novell Netware could do
the same thing at the file system level, and it could be
used (not with Access) to prevent your data becoming
corrupted when the network went down or your computer
turned off.

Access also sometimes has problems with the structure
of the database becoming corrupted. Transactions are
not used by Access to protect the structure of the database.
If transactions were used to protect the structure of the
database, they would protect the structure of the database
during complex sequences of actions, just like you can
use them to protect your data during complex sequences
of actions.

Access still does not use transactions to protect the
database structure even if you use transactions to
protect your data during complex sequences of actions.

When you said 'corruption' you probably meant damage
to the database structure. No, transactions do not
prevent that, because transactions are not used by
Access at that level.

If you only have simple database actions, then transactions
do not do anything.

It is good to keep in mind that corruption of your
data by a network error is much less likely than
corruption of your data by user error. Transactions
do not prevent user error.

(david)


"JString" <(E-Mail Removed)> wrote in message
news:22DCA45B-F1BC-4115-BD05-(E-Mail Removed)...
> Hello.
>
> I don't understand much about transactions and I would really like to know
> if commit/rollback can help guard against corruption of the back end, and
> if
> so what is the general procedure to make this happen.
>
> Many thanks in advance.

If there is a need to add records in two different tables to accomplish one
goal, that can be a matter of using a transaction. If there is a need to add
12 records in one table, that may be better to use ONE insert query (which
will be almost like one atomic operation) but if that cannot be done and
looping need to be done, then, that may also become a good case to make a
transaction between the start and the end of the loop.


Transaction should be short. If there is a step that requires a user
intervention, inside the transaction, that is *probably* not very good (CAN
be too long, the user may have left for lunch) since tables and records may
become locked. With Jet, you don't get much control on what kind of
'isolation' (what other users can see about your transaction, even if they
can delete or append records on tables you just 'took a look at' , in your
transaction) you have, in comparison with MS SQL Server, but note that you
can indeed lock other users out of tables and records you touched: that is
an inconvenient about transaction, for these other users (or your own access
to the data though other means than through your transaction).


The user interface uses a transaction when you insert a bunch of data into a
table. Indeed, when you get the prompt about nnn records won't be added for
this or that reason, the prompt (user intervention ! ) is about accepting
the records that are not in 'error', or to abort the whole insertion thing.
This *is* a transaction that the interface started for you, and asked you
about committing it, or rolling it back! Inserting a bunch of data and
having to validate it is, indeed, a good example for a transaction. If you
have to 'touch' many tables (or the same table many times) and cannot do it
in one single query, that is typical example where a transaction can be
useful. Note that the operation system can also supply its own
transaction-thing when components outside a database may be involved (bank
transfer of money, as example), but that is, here, out of scope, even if it
is interesting to know that such thing is available.


Vanderghast, Access MVP


"Chris O''''Neill" <(E-Mail Removed)> wrote in message
news:5E2616C6-59F9-4DFF-B649-(E-Mail Removed)...
> That was a *very* good explanation, Michel. Thank you for that!
>
> I'm wondering if there are any general "guidelines" for when one should
> use
> (or not use) transactions? Some folks (he said, pointing at himself) can
> get
> carried away and overdo it if they don't have some guidelines to help them
> decide when a transactional methodology is better (or worse) than just
> doing
> it a regular way.
>
> Fot instance, I'm creating a program that includes a subroutine that
> creates
> a fiscal period table (fiscal period number, period start and end dates,
> Yes/No field for whether the period is closed, etc.). The routine takes
> the
> fiscal year start date supplied by the user (e.g. January 1st of any year)
> and does 12 loops to create each row in the table. (Btw, the routine
> accounts for fiscal years that don't start on January 1st and
> automatically
> adjusts for leap years, which I thought was pretty cleaver of myself. (He
> said proudly!))
>
> Anyway, I'm thinking that maybe it might be a "good idea" to use a
> transaction to create the table? That way, if something pukes during the
> process, the table won't end up partially completed. Would *that* be an
> example of an appropriate use for transactions? What are some other
> examples
> of when (or when not) to use transactions?
>
> Thanks, in advance, for your guidance and teaching...
>
> Regards, Chris
>
> "Michel Walsh" wrote:
>
>> With Jet? I would say that I don't see in what the transactions exposed
>> to
>> us could help against corruption. At least, if the corruption comes from
>> a
>> lost of connection, there is not mechanic I am aware of, which time out a
>> started but uncommitted transaction. On the other hand, if you lost the
>> connection after you started a transaction, it is unlikely that you will
>> be
>> able to get back the same connection... to roll it back.... but why will
>> you
>> roll it back if you can then continue to deal with the database and
>> finally
>> commit the transaction?
>>
>>
>> The goal of a transaction is to make a complex operation appears as ONE
>> operation. As example, you have to append data in tableA, then erase it
>> from
>> tableB. But observe that, in theory, the system may fall after you append
>> in
>> tableA but before you erase it from tableB and that could 'invalidate'
>> your
>> data (as if you send 1 M$ to clientA and fail to debit clientB, you will
>> be
>> losing 1M$). So, will you start a transaction, append in tableA, delete
>> in
>> tableB, and commit your transaction. If there is a failure while the
>> transaction is active, nothing outside the transaction will be influenced
>> by
>> it. Note that technically, appending data in tableA and not being able to
>> delete it from tableB is not "corruption" of data, even if it creates a
>> state you don't want. Corruption is more like a wrong data type, an index
>> pointing to the wrong data, a unique constraint or a data referential
>> integrity rule that is not respected, or something like that.
>>
>>
>> Vanderghast, Access MVP
>>
>>
>> "JString" <(E-Mail Removed)> wrote in message
>> news:22DCA45B-F1BC-4115-BD05-(E-Mail Removed)...
>> > Hello.
>> >
>> > I don't understand much about transactions and I would really like to
>> > know
>> > if commit/rollback can help guard against corruption of the back end,
>> > and
>> > if
>> > so what is the general procedure to make this happen.
>> >
>> > Many thanks in advance.
>>
>>
>>

It seems you put in the same bag very different concepts such as data
integrity and database corruption, mainly when you speak of 'user error'.

If the user enter 23 when what should be entered is 32, that is a mistake.
That is a user error and indeed, no transaction could help. A cup of coffee,
having a validation number (CRC or otherwise) to be entered, having two
people entering the same data are all techniques that may help, but
transaction, no.

If the user fails to enter required data, or enter a not existing reference,
that should be covered and trapped by table design (not null) or by data
integrity rule, etc.

Transactions have NEVER been designed as safeguard for those previous kind
of 'user errors' in mind.

Sure, go immediately to the absurd, if its hard disk fails, then you are
likely to have to relay on having a RAID system, NOT on transaction, to be
able to recover from these errors.

You can come with cases were transactions are useless, indeed.

BUT

the table structures are protected by lock, when you modify them, and
internally by a transaction (never got the error message that the
modification(s) could not be written because some data won't match a new
constraint you just added ?). But you won't use transaction "to protect
table structure" when no-one is modifying them, you use a back-up (or
scripts, ... or RAID).


Vanderghast, Access MVP



"david" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Most corruption is caused by user errors. For example,
> deleting the wrong record. Or updating the wrong record.
> Or Adding the wrong record.
>
> That happens, and your data is wrong: it is no longer
> true. It is corrupt.
>
> Access no longer guards against your computer failing
> or being turned off while you are using it (as it did in Access
> 2.0), so the only kind of corruption transactions now
> prevent is the kind of corruption that occurs when you
> have a programming or data error that halts a complex
> sequence of actions, leaving some of your tables updated
> and some of your tables not updated. This is database
> corruption: your data is corrupt.
>
> A transactional file system like Novell Netware could do
> the same thing at the file system level, and it could be
> used (not with Access) to prevent your data becoming
> corrupted when the network went down or your computer
> turned off.
>
> Access also sometimes has problems with the structure
> of the database becoming corrupted. Transactions are
> not used by Access to protect the structure of the database.
> If transactions were used to protect the structure of the
> database, they would protect the structure of the database
> during complex sequences of actions, just like you can
> use them to protect your data during complex sequences
> of actions.
>
> Access still does not use transactions to protect the
> database structure even if you use transactions to
> protect your data during complex sequences of actions.
>
> When you said 'corruption' you probably meant damage
> to the database structure. No, transactions do not
> prevent that, because transactions are not used by
> Access at that level.
>
> If you only have simple database actions, then transactions
> do not do anything.
>
> It is good to keep in mind that corruption of your
> data by a network error is much less likely than
> corruption of your data by user error. Transactions
> do not prevent user error.
>
> (david)
>
>

I think I probably should have made myself a little more clear about what I
meant by 'corruption'. Obviously corrupt data is bad data that Access cannot
handle. However, it's my understanding that Access will mark a database as
being 'suspect' if a connection is terminated prematurely, when a user is
manipulating data, even if there hasn't been an actual corruption of that
data. The end result is that all users will be locked out of the database
until someone recovers it.

So I definately get what you're saying about transactions having no affect
on data integrity, BUT, can it guard against a database being placed in a
'suspect' state? It seems to me that if a transaction is never committed (or
even rolled back) because of a system crash or whatever, Access shouldn't do
this.

"Michel Walsh" wrote:

> It seems you put in the same bag very different concepts such as data
> integrity and database corruption, mainly when you speak of 'user error'.
>
> If the user enter 23 when what should be entered is 32, that is a mistake.
> That is a user error and indeed, no transaction could help. A cup of coffee,
> having a validation number (CRC or otherwise) to be entered, having two
> people entering the same data are all techniques that may help, but
> transaction, no.
>
> If the user fails to enter required data, or enter a not existing reference,
> that should be covered and trapped by table design (not null) or by data
> integrity rule, etc.
>
> Transactions have NEVER been designed as safeguard for those previous kind
> of 'user errors' in mind.
>
> Sure, go immediately to the absurd, if its hard disk fails, then you are
> likely to have to relay on having a RAID system, NOT on transaction, to be
> able to recover from these errors.
>
> You can come with cases were transactions are useless, indeed.
>
> BUT
>
> the table structures are protected by lock, when you modify them, and
> internally by a transaction (never got the error message that the
> modification(s) could not be written because some data won't match a new
> constraint you just added ?). But you won't use transaction "to protect
> table structure" when no-one is modifying them, you use a back-up (or
> scripts, ... or RAID).
>
>
> Vanderghast, Access MVP
>
>
>
> "david" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Most corruption is caused by user errors. For example,
> > deleting the wrong record. Or updating the wrong record.
> > Or Adding the wrong record.
> >
> > That happens, and your data is wrong: it is no longer
> > true. It is corrupt.
> >
> > Access no longer guards against your computer failing
> > or being turned off while you are using it (as it did in Access
> > 2.0), so the only kind of corruption transactions now
> > prevent is the kind of corruption that occurs when you
> > have a programming or data error that halts a complex
> > sequence of actions, leaving some of your tables updated
> > and some of your tables not updated. This is database
> > corruption: your data is corrupt.
> >
> > A transactional file system like Novell Netware could do
> > the same thing at the file system level, and it could be
> > used (not with Access) to prevent your data becoming
> > corrupted when the network went down or your computer
> > turned off.
> >
> > Access also sometimes has problems with the structure
> > of the database becoming corrupted. Transactions are
> > not used by Access to protect the structure of the database.
> > If transactions were used to protect the structure of the
> > database, they would protect the structure of the database
> > during complex sequences of actions, just like you can
> > use them to protect your data during complex sequences
> > of actions.
> >
> > Access still does not use transactions to protect the
> > database structure even if you use transactions to
> > protect your data during complex sequences of actions.
> >
> > When you said 'corruption' you probably meant damage
> > to the database structure. No, transactions do not
> > prevent that, because transactions are not used by
> > Access at that level.
> >
> > If you only have simple database actions, then transactions
> > do not do anything.
> >
> > It is good to keep in mind that corruption of your
> > data by a network error is much less likely than
> > corruption of your data by user error. Transactions
> > do not prevent user error.
> >
> > (david)
> >
> >
>
>
>

Since nothing will terminate the transaction, some tables/records may be
locked forever. You *may* be able to recover from these locks by deleting
the ldb file, but I won't bet much that you will get your data 'uncorrupted'
after having done that, in every cases.


Vanderghast, Access MVP

On Wed, 6 Aug 2008 00:11:02 -0700, Chris O''''Neill
<(E-Mail Removed)> wrote:

>The routine takes the
>fiscal year start date supplied by the user (e.g. January 1st of any year)
>and does 12 loops to create each row in the table. (Btw, the routine
>accounts for fiscal years that don't start on January 1st and automatically
>adjusts for leap years, which I thought was pretty cleaver of myself. (He
>said proudly!))

I've done something similar using an auxiliary Num table with integer values
0-10000 or so, and the DateAdd function (which handles leapyears with no
additional effort):

INSERT INTO table
SELECT DateAdd("m", [N], [Enter start date of FY:])
FROM Num
WHERE N < 12;

No code, no loops, and no transactions needed.
--

John W. Vinson [MVP]

JString <(E-Mail Removed)> wrote:

>I don't understand much about transactions and I would really like to know
>if commit/rollback can help guard against corruption of the back end, and if
>so what is the general procedure to make this happen.

If anything using transactions would increase the chance of
corruptions as Access/Jet would be flagging the database as being
updated for microseconds/milliseconds longer than without using
transactions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/