Capture record update in backend database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using MS Access 2003. I've split the database so that two of us can
enter and update records to the backend database.

I have a table that holds global values, so to speak, that saves some
parameters for the frontend. One field contains a value that changes from
"Notuptodate", "Updating" and "Uptodate".

Now when one of the frontends causes the field to change the value to
"Updating", is there a way to trigger a set of procedures when this happens?
 
Access doesn't support triggers the way SQL Server does, mainly because an
Access back-end is really just a flat file. Could your front-end perform
these procedures?

Barry
 
Hi Barry,

I've got the procedures in my front end. The problem is: whichever event I
use--Load Event, Current Event, BeforeUpdate Event, AfterUpdate Event (you
would think AfterUpdate Event would do the trick), it doesn't run my
procedures. The events are just not being triggered.

I have a form with a bound text box. This text box's control source points
to a field that changes between "Notuptodate", "Updating" and "Uptodate" in
the backend database. The change would occur when another frontend causes it.
For example, my colleague would hit a button in his frontend to update a
table in the backend database. When this happens, the field in the backend
would change to "Updating".

At the moment when the field changes to "Updating", the text box on my
frontend would show "Updating". When this happens, I want one of my
procedures to run. Something like triggering a message box informing me that
my colleague is updating the database and making the other buttons on my
frontend to become locked. I've put my procedure in the AfterUpdate Event on
the textbox that would show "Updating" on it. But still with no results.

I've got this from the Help file of MS Access for AfterUpdate Event:
Changing data in a control by using Visual Basic or a macro containing the
SetValue action doesn't trigger these events for the control. However, if you
then move to another record or save the record, the form's AfterUpdate event
does occur

Although I have not tried it yet, but if you programmatically save the
record from one frontend, will this trigger the event on the other frontend.
Meaning, if my colleague clicks on the update button in his frontend which
also saves the record in the backend database, will this trigger the
AfterUpdate Event in my frontend thus causing the procedures written in the
AfterUpdate Event in my frontend to run?

Regards,
Nils
 
The AfterUpdate event only fires when client-side updates occur. In other
words, it won't do what you're asking.

I think the solution to what you're trying to do lies in record-locking.
Instead of having a warning pop up when someone is editing a record, you
should have the form lock the record. This will give an error if you try to
edit a record or table while someone else is editing it. Take a look at the
form's RecordLock property.

HTH,
Barry
 
Thanks for the tip. As I am just an intermediate user of MS Access there are
some features in the Access VBA that I have not touched yet for the main
reason that I could not see how I could apply them.

Record locking, although not entirely unfamiliar, is something I'll need to
delve into. At least now I can put this into use. No doubt I'll be asking
questions about record-locking soon--unless I find similar questions already
answered.

You have been very helpful, thanks again.

Regards,
Nils
 
Back
Top