Data Macro triggers/fires only under certain circumstances

[Papa_Bear1]

I'm trying to use "data macros" as a basis for performing a trigger-like action in Access (365). Ordinarily I might do this simply with an "After Update" event on the field of interest with some VBA etc., but in this case, I have a split database, where code is running in the back-end, and I'm trying to provide status updates to a user in a front-end. So I figured I'd look into using a trigger of some kind - hoping to make the connection that way.

What I have right now successfully updates a control on a form when I manually change the data in one field of a front-end table, and even when I run a query that changes the data in that same front-end database. But when I run a query in a linked back-end database, that updates that same front-end table, it does not work (i.e., it will not even fire/trigger.) I can't see why it should care how it was changed, if it is basically triggered by a change. I know the data is changing (via breakpoints etc.), but it will not fire the trigger (data macro) action. Is this a known limitation - that this can't work in a split database situation?

Also, while on this same topic - it only works when I base it on the overall table changing, it will not work when I set it to fire based only on the field that I'm actually updating, which seems weird. Why would it not work when coded more precisely to fire based on exactly how I'm changing it? (Table only has one field anyway - so it should work either way.)

Any help would be very much appreciated!

 

[jdraw]

Please describe your application in a little more detail.
You have a split database, but you have a front-end table?

 

[Papa_Bear1]

Hi jdraw - thanks for asking...
Yes - it is a split database, and normally you have the linkages from front-end to back-end tables.
In this case, I have a bunch of code in the back-end that updates the shared data, but I was trying to keep all users connected via the front-end, including the admin-type who will run that code. In addition, I like to put status feedback up for users when processes take a while. This is quite easy when the code is running locally - but in this case, the code is running in the back-end. So, I thought I'd look into seeing if Access had something like a trigger, so when the back-end code reached a key (noteworthy) point, a message could be sent to the admin user - simply by making a change to data in a table. In this case then, this particular table resides in the front-end (so that the trigger/data macro will work), and the back-end (in a backward manner) reaches back to the front-end - linking to this particular table, and makes the change to that table. That table is nothing more than one field with one row, where I simply change the status comment.

So, within the front-end - everything works beautifully. When the form is open, if I change the content of this table, the form is updated - awesome. It even works when I run SQL to make that change - but only if that SQL is run within the front-end. If the back-end is the one effecting the change to the table - then it does not work - it is not triggered - even though I have seen/proven the change is indeed taking place. Somehow, data is changing in the table without the data macro being triggered.

 

[jdraw]

Does every user have their own copy of the front end on their own PC? Your description of the set up is confusing (to me at least).
Can you post a zipped copy of your test databases(FE and BE)?

 

[Papa_Bear1]

Yeah- I agree - it is indeed weird. I don't even really like it since the back-end is linking to a table in the front-end. But since I know I'll have only one Admin - or certainly only one Admin at a time, it seemed OK to have the back-end reach back and link to the front-end for this purpose.
It's just frustrating to have it working - almost - but not quite. I have too much data I'd have to scrape out of the system to try to send the files.

I did see another question asked that seemed to have the same problem - where it worked when run locally, but they had an external program trying to do this same thing- and apparently the conclusion was that it won't work. Really a bummer - because that is exactly when you need it. If it is local, then I can just do the usual "after update" stuff in other ways - so the "data macro" isn't adding much value - at least it seems to me.

I'm beginning to wonder if I'll just need to move all that update code to the front-end and be done with it. I really wanted that code to reside in the back-end - oh well.

 

[jdraw]

Typical Access usage would have tables in the BE. Forms/reports/queries in FE.
BE ion a shared location. Every user with their own copy of FE on their own PC.

I don't need all the data, just enough records to exercise the application and highlight your issue.

What exactly "triggers" the message for the Admin?

 

[theDBguy]

Hi. Can you maybe post the trigger code?

 

[Papa_Bear1]

I do have the system set up in the normal way - with shared data in the back-end etc. etc. Most users will be doing the usual 'thing', running queries and generating their own output etc., based on data in the shared tables. But one user will be managing the input to this system, and this was a one-off to try to only handle running that related update code in the back-end, while providing status messages to one particular user in the front-end. I just couldn't find any other way to send a 'message' to the front-end dynamically, other than a trigger.

I just had another thought - perhaps if the back-end remotely runs code in the front-end to make the change, maybe that would work. This seems like a lot of gymnastics to get something to work - but maybe it would work.

I'll see if I can quickly generate a pair of files...

 

[arnelgp]

to exercise the application

or experiment?

perhaps if the back-end remotely runs code in the front-end to make the change, maybe that would work

you will need to have a "linked" table from Each FE going to BE. so you will "know" the address of each FE.
promising but will take much work.

if the datamacro is not being run, maybe create a routine that will imitate the behaviour of the macro.

 

[Papa_Bear1]

In my table (called tabFEMessages) I have set up a RunDataMacro on the After Update, that calls a Data Macro named "tabFEMessages.dmUpdateStatusMessage.

The Data Macro has only one command in it:
SetLocalVar
Name varStatus
Expression = UpdateStatus([StatusMessage])

Where "UpdateStatus" is my function and [StatusMessage] is the field in the tabFEMessages table with the content I want displayed.

The Public Function UpdateStatus(pMessage) currently has three lines, but only the 2nd one is getting any work done. The 1st and 3rd were just me trying to force it to recognize the change.

DoEvents
Forms!frmStart.Controls("txtStatus") = pMessage
Forms!frmStart.Controls("txtStatus").Refresh

 

[jdraw]

@arnelgp --yes experiment/test/run/adjust/decipher... an actual sample of the op's code.

@Papa_Bear1 did you intend to post a file?

 

[theDBguy]

Hi. Thanks for the additional information. I thought you said your data macro in the BE is updating a table in the FE, but I don't see that in what you just posted.

 

[Papa_Bear1]

@jdraw - I will see if I can quickly gin up a couple files - but was hoping that the explanation of the code might be sufficient.
@theDBguy - All of what I described is in the front-end and it works when changes are made within the front-end. What I have is a back-end database that links to that same front-end table. The hope was for changes made to that table (based on code running in the back-end) would do the same triggering - resulting in messages on the form in the front-end. I already have everything in place for the connections etc. The only thing not working is the actual triggering when the change is made to the table from the back-end (via linking to that table in the front-end).

...so close... and yet so far...

 

[arnelgp]

so that is the Code on the BE?
i think you need a Table for those message, plus a Flag (yes/no field) in th ]e BE.
this table is a Linked table on the FE.

whenever an update is made, the FE writes the message to this linked table and reset the to Yes.
the Be meanwhile has a form with timer that check this table, every once in a while to see if
there is a message with the flag set to True.

if it does find, display the message and reset the Yes to No.

very simple, yet the implementation is up to you.

 

[theDBguy]

@jdraw - I will see if I can quickly gin up a couple files - but was hoping that the explanation of the code might be sufficient.
@theDBguy - All of what I described is in the front-end and it works when changes are made within the front-end. What I have is a back-end database that links to that same front-end table. The hope was for changes made to that table (based on code running in the back-end) would do the same triggering - resulting in messages on the form in the front-end. I already have everything in place for the connections etc. The only thing not working is the actual triggering when the change is made to the table from the back-end (via linking to that table in the front-end).

...so close... and yet so far...

Okay, just so we're clear, you have a data macro in the BE that updates a table in the FE, correct? Then, you have a data macro in the FE that calls your function, correct? And, you have verified that the BE was successfully changing the FE table, correct? And you have verified that changing the FE table locally triggers your function, right?

 

[Minty]

Surely an(other) issue with this is that the FE database also needs to be accessible all the time otherwise the table the data-macro is trying to update simply won't be there?

Or have I missed something in translation/understanding?

 

[Papa_Bear1]

In the attached, there are a couple database files, one is for FE and one for BE. Note that they only contain what is necessary for this question - so they don't even have any of the usual data tables that would be in the back-end. So I know it looks reversed, but that just happens to be what I'm attempting for this...
(The BE is wired for the the FE being in C:\AccessTest - So, place in a folder with that name if you don't want to have to re-link from BE to FE.)

If you work solely in the front-end, you should see that manually changing data in the tabFEMessages table indeed updates the form.
However, if you make changes to that same table, but in the back-end --- no dice.

 

[Papa_Bear1]

Okay, just so we're clear, you have a data macro in the BE that updates a table in the FE, correct? Then, you have a data macro in the FE that calls your function, correct? And, you have verified that the BE was successfully changing the FE table, correct? And you have verified that changing the FE table locally triggers your function, right?

Actually - there is only one Data Macro and it resides in the front-end. I figured it best to get it working in the isolated front-end area first - which it does. Then in the BE, I linked to the FE message table. The hope is to have BE code be able to make changes to that FE message table and have the trigger recognize the changes - but again - no dice. I'm beginning to think MS simply did not intend for this to work this way. Bummer if so.

 

[Papa_Bear1]

Surely an(other) issue with this is that the FE database also needs to be accessible all the time otherwise the table the data-macro is trying to update simply won't be there?

Or have I missed something in translation/understanding?

I'll have to chew on that. I guess it is indeed another anomaly of this approach - as it will always be in a state of "having-been-connected" to someone's front-end. But since I'm expecting that to be quite static (essentially one person under normal circumstances) it seemed at least OK to do it - and it will only attempt to relink when a certain user type is chosen, so it will link to whomever was the latest admin to run the front-end solution.

 

[Papa_Bear1]

so that is the Code on the BE?
i think you need a Table for those message, plus a Flag (yes/no field) in th ]e BE.
this table is a Linked table on the FE.

whenever an update is made, the FE writes the message to this linked table and reset the to Yes.
the Be meanwhile has a form with timer that check this table, every once in a while to see if
there is a message with the flag set to True.

if it does find, display the message and reset the Yes to No.

very simple, yet the implementation is up to you.

Yes - a timer is another way to go - but I was trying to avoid that and have it actually event-triggered.