Number of open transactions in DAO

[Guest]

Hi,

How do I check wether a transaction is open in a DAO workspace before
calling .Rollback on it? I need this for cleaning up but calling .Rollback or
..CommitTrans when there is no transaction causes a runtime error.

 

[Allen Browne]

It would be really useful if DAO provided that information, but given that
you can nest transactions (5-levels deep IIRC), it would not be a simple "Is
it or isn't it?" question.

The best solution is to set your own flag (boolean variable). Immediately
after the BeginTrans, set it to True, and immediately after the
CommitTrans/Rollback, set it to False. You can then be sure whether your
particular transaction is active or not.

For an example and other general suggestions and warnings (traps) re
handling DAO transactions, see this example:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[David C. Holley]

How are you structuring your code? In all of the examples that I've seen
.BeginTrans, .CommitTrans and .RollBack have always been used in
blocks (a la IF...END IF statements) of code and not orphaned across
multiple SUBS?

 

[David C. Holley]

But if the .BeginTrans issued a transaction number and .Rollback and
..CommitTrans required that number as a parameter, wouldn't things be
easier? Not to mention that you could then take the value and tag all of
the records affected with it for auditing purposes.

 

[Guest]

Since I am controlling my listbox's content by controlling it's recordset I
want to do non comitting updates on it's recordset. Therefore I call
Workspace.BeginTrans in my form's OnOpen event. I commit when the user
presses the save button (which will also add more info to the records in the
recordset) and rollback on the form's OnClose event. Errors will perform a
rollback too.
My form class has some properties that are initialised in the OnOpen event,
like:

wrkSpace = DBEngine.Workspaces(0)
dbConn = Workspaces.OpenDatabase(...)
rsRules = dbConn.OpenRecordset(sql)

Keeping track of the number of transactions works fine. However my
msaccess.exe now reproducably crashes everytime I call Workspace.Rollback,
especially if I have first made changes in a recordset. Have reproduced this
behavior on two different Compaq nc6120 laptops. Though I guess I should
report this to Microsoft support, right?

 

[David C. Holley]

You're utilizing the concept of Transactions in a manner that they were
never intended for. The idea behind transactions is to ensure that all
updates (or deleted) across multiple tables in a database can succeed
prior to committing them.

Which leads me to the next question, what information is in the listbox
and why do you *NEED* to control its content?

 

[Guest]

True, the reason I am using one transaction form wide here is to workaround
the fact that I do not want to save the data in the recordset to disk right
away but do want it to be displayed in the listbox. Besides of it being out
of the ordinary it should be quite possible I recon. Doing this using the
recordset seemed a faster way than writing to the listbox directly. Using the
recordset currency values get nicely displayed etc. If I have to write data
to the rows manually I would have a lot of overhead writing utilities to do
data conversion/layout, at least as far as I can see.

I need to control the listbox's content because it contains lines of an
invoice that can be manually entered or removede from the list. Only when all
lines are validated, and the chosen recipient is validated can a unique
invoice number be generated and all data be saved.

 

[David C. Holley]

True, the reason I am using one transaction form wide here is to workaround
the fact that I do not want to save the data in the recordset to disk right
away but do want it to be displayed in the listbox. Besides of it being out
of the ordinary it should be quite possible I recon. Doing this using the
recordset seemed a faster way than writing to the listbox directly.

Again, Transactions were never intended for this purpose nor were
listboxes intended to be updated in such a manner. Use a temp table.

Using the

recordset currency values get nicely displayed etc. If I have to write data
to the rows manually I would have a lot of overhead writing utilities to do
data conversion/layout, at least as far as I can see.

You might have a lot of overhead creating code to do this, but how much
time have you spent trying to make this work? Again a temp table would
probably do the job.

I need to control the listbox's content because it contains lines of an
invoice that can be manually entered or removede from the list. Only when all
lines are validated, and the chosen recipient is validated can a unique
invoice number be generated and all data be saved.

Again use a temp table, that stores the lines of the invoice and then
appends them to the table that contains the invoice detail when its all
validated. Even better, add a field to the invoice detail that indicates
the status of the line item (Unvalidated, validated, approved, etc.)
which is updated as needed.

 

[Guest]

First of all: Thank you very much for the trouble to look into my issue.

The reason I am trying to do it "the hard" way is that in my opinion data
that has the same lifespan of the form it's displayed on is a waste to store
persistently. I was under the impression that it could be solved in an easy
matter. Were it a .NET app I might have used an in memory ADO XML
representation to store the temp data. Or with a MySQL backend I would have
used an in memory type table. It just seemed like a waste to create a
persistant temp table to store data with such a short lifespan.

However, since it does indeed seems like the most viable option I will
create the temp table, store the temp records there linked to UID (form is
modal anyways) and have the form's OnClose do the clean-up after. Does that
sound like a good way to go about it?

Regards,

 

[David C. Holley]

Pretty much given that you want the text to be formatted.

 

[Tim Ferguson]

The reason I am trying to do it "the hard" way is that in my opinion
data that has the same lifespan of the form it's displayed on is a
waste to store persistently. I was under the impression that it could
be solved in an easy matter.

An alternative solution for this would be a listbox function and a
collection declared at Private Module level.

B wishes


Tim F