C
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.
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.