Concurrency and Transaction in Access

[CW]

I am new to ACCESS VBA and would appreciate some help regarding ACCESS's
behaviour when it comes concurrency and transactions.

I am planning on using MSDE as the backend database engine, and ACCESS as
the development environment. I like ACCESS for its fast protyping
functionality. However, I have a few concerns with using ACCESS databound
forms and controls.

It's obvious that the main advantage I see in ACCESS is its databound forms
and controls. If I were to use unbound forms, then I'd stick with VB as the
tool to develop the front end.

However, with databound forms, I do have the following concerns:

(1) Say I need to change a set of data in subform, and once that's complete,
I would update the main form's certain status field. Now, I need the process
(updating subform records, and updating main form records) to be in a single
transaction. In data bound form, changing data on the form would immediately
change the underlying data. While I could wrap the code to change the
subform/main form data in a single transaction via the use ADO, I lose the
advantage of doing something like

frmMain.Status=1.

I would instead write something like

Conn.Exec ... 'code to update data for subform recrds
Conn.Exec "Update tblMain Set Status=1 where MainID=" & Me.MainID

Me.Refresh
Me.subfrmMain.Refresh


A similar dilemma exists for using the AfterUpdate event. I don't think any
change to the database made in the AfterUpdate event handler occurs in the
same transaction context as the original update that triggered the
AfterUpdate event.

I want to avoid writing SQL statement using ADO connection and unbound
forms - because they defeat the purpose of using Access, being a rapid
development environment. Is there anyway I can bound controls and yet at the
same time wrap some of the update/delete/insert in a single transaction?

The program is envisaged for small (5-10 user environment) office, and
ACCESS database by itself is most likely to be already adequate. By using
MSDE, it just gives me more options later on.

The only thing that bugs me is basically how I could handle transaction
processing in ACCESS with bound controls.

All advice appreciated.

 

[GreySky]

I believe the answer depends on application requirements.

You can, during the BeforeUpdate event of the subform,
update the main form. If that update fails, Cancel the
update in the subform. I might even try beginning a main-
change transaction in the BeforeUpdate and comitting it in
the AfterUpdate (depends if record locking becomes an
issue, among other considerations). If you use this in
conjunction with status flags (e.g., beforeupdate flags
subform as dirty and flags main as dirty, afterupdate
flags both as clean within a single transaction), and old-
data temporary fields, you could guarantee the results.

Personally I would feel comfortable with using
combinations similar to the one described above. But, in
fact, they would be two separate transactions linked
together, with a check concept if required.

My understanding of how MS Access works is based upon my
C++ coding with MFC objects. I believe MS Access uses a
DDV/DDX architecture when working with its bound controls.

David Atkins, MCP

 

[TC]

A few points which might or might not help.

(1) As you probably know, the concurrency & transaction issues are partly
controlled by the database engine (Jet, MSDE, whatever), and partly
controlled by Access itself (by virtue of its decision on when to save
changes etc.).

Be aware that Jet transactions are not really true transactions! They do
*not* guarantee that all of the updates in the transaction will be saved -
or not saved - and the database will be left in one of those two states.
They just record the changes in memory, and then, at commit time, they write
the changes out to disk as fast as possible, hoping that nothing will go
wrong in that short timeframe!

So if you used Jet, you could not achieve what you want - if that is, true
transactions (eg. like in Oracle). As for MSDE, I don't know how it handles
transactions, so you should confirm that issue, before you worry about the
Access end.

(2) Access does not expose the workspace that it uses for a form - even a
simple form with no subform. So even if you were using a backend database
with full support for transactions (eg. Oracle), there is no way you can put
*Access'es* changes, and *your* changes (eg. from an AfterUpdate), into a
single transaction, when using a databound form. You would have to use an
unbound form.

HTH,
TC