How to prompt back end database to refresh when record is added to front end database?

[Sampoline]

Say that a user adds a record to the front end database. Is there a way to notify/prompt a message box in the backend database like: "This client version of the database has been updated, please refresh to see the latest records."?

And then the user clicks OK and this automatically refreshes the backend database..

 

[CJ_London]

Normally, the front end would not contain tables, only links to the back end tables. So think you had better clarify what you are trying to do since at the moment it is not making sense.

 

[Sampoline]

Normally, the front end would not contain tables, only links to the back end tables. So think you had better clarify what you are trying to do since at the moment it is not making sense.

When I mean add a record, I mean they use the form to create a record and that goes back into the table in the backend. Which is standard.

Say that I have a user that uses the backend to monitor the work load of front end users who are working on the form. When the front end has created a record, this updates to the table in the back end right? But for the user on the back end to view the updated table, they need to refresh the database (while it's open) yeah? How do I create a prompt for the backend user to update the database whenever a new record is output to the table?

Like a message box appears telling them that the database needs to be updated, and then when they click OK, this refreshes the back end database automatically. And the records in the table are updated to reflect the changes made on front end by the users.

Sorry if I'm being unclear. It makes better sense in my head than on text.

 

[Gasman]

PMFJI,
They would use a FE as well?,
Plus I am pretty sure this was asked just a while back but for a very very small refresh rate.?

 

[The_Doc_Man]

Unfortunately, the mechanism to do this as you describe it doesn't exist for Access. You would have to craft your own method to do this. The normal way Access works follows the Windows security principle of user isolation; i.e. you have no right to interfere in the actions of another user. I know that technically, this isn't interference that you want, but rather a type of collaboration. However, to Windows, reaching out to alter the flow of operation of another user's process is expressly forbidden without setting up this collaboration first.

The normal data flow of an Access split database is that the front-end is the place where the workspace is physically located. The back-end is more or less passive here. It is merely a file being altered through the file system and the SMB protocol. There is nothing active in the back-end to send out such change notifications. EVERYTHING happens in the front-end. Therefore, it would become the responsibility of the front-end to send out such notices. But... there is again no easy way for that to happen unless the front-end files are programmed to cooperate in a way that is not native to Access. You can't just "reach out and touch" some other front-end file on some other machine. You have to program the front-ends to EXPECT this type of cooperation. When you are just sitting in front of the FE screen/form/whatever, no events are happening - and that means there is nothing to drive this sense of change. If you tried to open up a network socket, you still have the issue that whatever is monitoring the socket has to wait for it. (This is the "LISTENER" concept in network communications.) In the absence of driving events IN THE FRONT ENDS, you have no signaling mechanism because most of the time, ACCESS is in control of listening for things.

There is such a thing as a table data-change event - but it doesn't help because this event still executes in the context of the FE that changed the data. There is no active back-end code to watch for data changes.

If you have an active back-end like SQL Server or ORACLE or one of the other popular active SQL engines, there IS a possibility of having an active signal sent out, though again there is the issue of separation of tasks. Even if you have this hypothetical active back-end, the front-ends have to be set up to expect that it COULD happen. There is a LOT of programming required to do that.

Short answer: There is no intrinsic mechanism for this. You can't get there from here without writing a BUNCH of complex code to manage the "unsolicited" interaction. As experienced as I am, this is something I wouldn't try without first stocking up on aspirin and coffee.

 

[Sampoline]

Unfortunately, the mechanism to do this as you describe it doesn't exist for Access. You would have to craft your own method to do this. The normal way Access works follows the Windows security principle of user isolation; i.e. you have no right to interfere in the actions of another user. I know that technically, this isn't interference that you want, but rather a type of collaboration. However, to Windows, reaching out to alter the flow of operation of another user's process is expressly forbidden without setting up this collaboration first.

The normal data flow of an Access split database is that the front-end is the place where the workspace is physically located. The back-end is more or less passive here. It is merely a file being altered through the file system and the SMB protocol. There is nothing active in the back-end to send out such change notifications. EVERYTHING happens in the front-end. Therefore, it would become the responsibility of the front-end to send out such notices. But... there is again no easy way for that to happen unless the front-end files are programmed to cooperate in a way that is not native to Access. You can't just "reach out and touch" some other front-end file on some other machine. You have to program the front-ends to EXPECT this type of cooperation. When you are just sitting in front of the FE screen/form/whatever, no events are happening - and that means there is nothing to drive this sense of change. If you tried to open up a network socket, you still have the issue that whatever is monitoring the socket has to wait for it. (This is the "LISTENER" concept in network communications.) In the absence of driving events IN THE FRONT ENDS, you have no signaling mechanism because most of the time, ACCESS is in control of listening for things.

There is such a thing as a table data-change event - but it doesn't help because this event still executes in the context of the FE that changed the data. There is no active back-end code to watch for data changes.

If you have an active back-end like SQL Server or ORACLE or one of the other popular active SQL engines, there IS a possibility of having an active signal sent out, though again there is the issue of separation of tasks. Even if you have this hypothetical active back-end, the front-ends have to be set up to expect that it COULD happen. There is a LOT of programming required to do that.

Short answer: There is no intrinsic mechanism for this. You can't get there from here without writing a BUNCH of complex code to manage the "unsolicited" interaction. As experience as I am, this is something I wouldn't try without first stocking up on aspirin and coffee.

Ok fair enough. Didn't realise it would be that complex actually. Sorry. Do you have a different approach I can take instead? Perhaps trying to coordinate signals/messages between FE/BE databases isn't efficient, but maybe there's a better way to sort of replicate that process? Where a backend user can still be informed in a sense that they should periodically refresh the database.

 

[bastanu]

How about a simple form with a timer that displays a message ("Records have been added...") based on a textbox with the control source = dCount("*","[tblTableToWatch]") :

Me.Requery
If Me.txtRecordCount>Me.txtRecordCount.Oldavalue then Msgbox "New records added"

Cheers,

 

[The_Doc_Man]

Where a backend user can still be informed in a sense that they should periodically refresh the database.

What is the purpose here? What is the BUSINESS goal? Why do you care? (No, not a flippant question...) We can probably offer you some ideas but we need to know the business need for this information.

Didn't realise it would be that complex actually. Sorry.

Oh, no need to be sorry. Sometimes the simplest ideas really ARE simple. This one wasn't. But if you didn't ask, you would never know.

 

[Gasman]

Ok fair enough. Didn't realise it would be that complex actually. Sorry. Do you have a different approach I can take instead? Perhaps trying to coordinate signals/messages between FE/BE databases isn't efficient, but maybe there's a better way to sort of replicate that process? Where a backend user can still be informed in a sense that they should periodically refresh the database.

I'm still trying to understand why they have to be a 'backend user'?

 

[Minty]

I'm with @Gasman nobody should be 'lurking' in the backend.

Create a management dashboard in the front end that is on a timer that refreshes record counts as @bastanu suggested.
Restrict access to it using either user profiles or a login.

Or create a separate management front end and distribute to the people that need it.

 

[Sampoline]

How about a simple form with a timer that displays a message ("Records have been added...") based on a textbox with the control source = dCount("*","[tblTableToWatch]") :

Me.Requery
If Me.txtRecordCount>Me.txtRecordCount.Oldavalue then Msgbox "New records added"

Cheers,

Hi, I'll try giving that a go.

So is this suggestion to be made on the FE Master or BE? Thanks for your help.

 

[Sampoline]

What is the purpose here? What is the BUSINESS goal? Why do you care? (No, not a flippant question...) We can probably offer you some ideas but we need to know the business need for this information.


Oh, no need to be sorry. Sometimes the simplest ideas really ARE simple. This one wasn't. But if you didn't ask, you would never know.

Essentially the goal is for the front-end users to index information of files they are processing and the back-end user to verify those records match upto the metadata provided from a client. This verification doesn't have to be every record, just periodically over time to ensure things are in check before a front-end user has run through a lot of files and realised they were making habitual mistakes (which can potentially happen when processing the amount that we do daily).

Is there a different way I could be doing this? This is just what I've been asked to do from my higher-ups and I'm trying to follow through with it, but if there are better suggestions, I'm all ears!

Thanks for the reassurance! You're definitely right Doc; until I ask, I will never know the answer!

 

[Sampoline]

I'm still trying to understand why they have to be a 'backend user'?

Mainly because the data that is on the FE is subject to change, and there was a requirement to have an admin keep data that had been processed on the FE verified via the BE.

Oh so you're suggesting there need not be a BE user at all? It's just always been my understanding that changes made to the front-end are usually made through the back-end right? Or have I misunderstood this whole concept. I probably have!

 

[Sampoline]

I'm with @Gasman nobody should be 'lurking' in the backend.

Create a management dashboard in the front end that is on a timer that refreshes record counts as @bastanu suggested.
Restrict access to it using either user profiles or a login.

Or create a separate management front end and distribute to the people that need it.

Okay, I see what you mean. That does sound interesting. Will do some more research into it.

 

[arnelgp]

i made a sample but don't know if this is what your final goal is.
i made a separate form with timer.
you need to Open the form on Autoexec macro in Hidden mode.
for demo just open this form normally and open Table1 form.
the timer will be triggered every 3 seconds.
add some records for table1.
delete some records.

 

[Sampoline]

Hi Arnel, this is almost what I needed.. essentially I wanted this to work when the table is open. The table is linked to another database.

And the form to fill is from this other database as well.. that's where my dilemma is at the moment.. Don't know if that's possible?

I want that refresh feature to work on a new database that has tables linked to this other database. Create a hidden form in this new database and then where exactly do I go from there?

I've just used FE/BE databases as an example above. They aren't exactly going to be used like that for my particular line of work, but explaining it like so was easier.

So picture this:

Database A can be thought of as a FE
Database B can be thought of as a BE

Multi-users will use the form(s) on FE and the tables are linked in the BE.

Most likely there will always be someone (no more than 1 or 2 admins) monitoring the data input of the FE users from the BE. Hence the question that came up in my head was, how can I add a refresh (or is it requery?) feature in Database B, when records are created in Database A. So that they are updated with the latest data. Of course I can just tell these admins to just click the 'Refresh All' button in the ribbon, but just was curious if I can automate that function instead.

Thanks.

 

[The_Doc_Man]

Here is my thought, having now seen your description of your workflow. First, just some advice. Calling out front-end and back-end users is very confusing nomenclature to us because those two terms have highly specific meanings to us that are not consistent with your description.

ALL repeat ALL of your users should only use a front-end file to use the app and the back-end file should NEVER be directly addressed. There is a serious danger of file locking issues leading to file corruption if you actually DID have a direct back-end user sharing with front-end users.

I believe what you call "front-end" is the front-end of your process and the "back-end" is just a later step in that same process. And if that is a correct interpretation, then what you have been saying makes a lot more sense, just involving misleading terms. If you actually meant front-end and back-end in the same way that we normally use them, you need to reconsider.

Now, regarding that notification that something has been added... You really don't care about what you asked. (Hear me out...)

Let's say that you have a small bunch of people entering data in the entry phase of your process. You have one person (from your description) that does validation. But which of those activities is really and truly time-critical? How often does it happen that the validation person catches up with the inputs of the data entry people? And if a new entry came in, would the validator stop the current validation immediately? Or would the validator finish that entry and move on to the next one?

I'm thinking that if you put a .Requery in the validation form's Form_AfterUpdate event (i.e. after the validator makes some entry that says "OK" and is then done with the entry), the validator would go on smoothly to the next request. Because at that point, the old record would have been updated but until you release that event, navigation and the "Current" event can't occur.

IF it happens that the validator CAN catch up, then the rest of the solution is to put a timer on the form the validator would use, but this timer would cycle ONLY if the validator has not yet "taken" one of these new records for examination.

I hope I said that clearly enough that I didn't confuse you.

EDIT: You posted just as I finished composing this. IF your multiple users are sharing database A as a non-split database, you STILL risk issues of file locking leading to terrible corruption. And if database B is a separate non-split database, again you have multiple users sharing a file with someone who is local to that DB, and that again can lead to issues.

 

[arnelgp]

you must test it.

i created autoexec macro on FE, so it will open frmHidden when
FE starts.
note table1 is a Linked table to BE database.

first step, relink table1 on FE to BE database.
make a second copy of FE. renaming the second copy as FE2.accdb
so you can run it both on your computer.

if you are in a network much better test environment.
i updated the code of frmHidden so it will not show
message if "you" are the one who make changes to
the table.
if another user add/delete a record, you will be prompted
to refresh it.

right now, it is manual refresh.

 

[Gasman]

Mainly because the data that is on the FE is subject to change, and there was a requirement to have an admin keep data that had been processed on the FE verified via the BE.

Oh so you're suggesting there need not be a BE user at all? It's just always been my understanding that changes made to the front-end are usually made through the back-end right? Or have I misunderstood this whole concept. I probably have!

It would appear so. @The_Doc_Man has already explained it.

@The_Doc_Man , I have worked in such a place where they kept score every two hours, and ran around like headless chickens if we were down. You would sear they thought we were in an ICU.
That was the managers way of monitoring us. The BS flows down, so they would have something to report to their managers.

Their attitude was every case took the same amount of time, which of course it does not. Even had people sit next to you with a stopwatch.
One of the worst places I worked and not just because of that. They could not organise a p**sup in a brewery.

 

[The_Doc_Man]

I've worked in a place where "Help Desk" functions were considered ultra-high priority. Since I was tier III, I rarely got anything on my plate because the first two tiers filtered it all out. The folks in the first two tiers had to run an app constantly. I could just take phone calls and do my primary job most of the time. But the catch was that this app was engineered to have notifications and wait-queues and long-wait alerts from the ground up using something other than MS Access as the primary backbone.