Synchronise database (1 Viewer)

[Bieke]

Hello,

I have a database running on a server. On 2 different PC's i'm running 2 different forms form the same database, one on each PC. I use the same tables.
I have made a login form that i run from PC 1 to login as a user, i write the loginname to a table. On PC 2 i can check this tabel to see if i'm logged in.
In PC 2 there is a possibility to logout, i write (name ='nobody') to the same table where i logged in on PC1.
My question now is, how can i synchronise the form on PC1 so that the logginname dissapear from PC1 when I logout on PC 2.
Is it possible to execute a function in PC1 from PC2. I know that on the same PC you can use 'property let' to execute a sub on another form.

Thanks in advance,

Bieke

 

[arnelgp]

If you Split your db, you will see the Changes (immediately?).

 

[Bieke]

If you Split your db, you will see the Changes (immediately?).

The DB is already splitted (Tables in another DB) if that's what you mean.
The Login record in the table (wich is used by both PC's) is changed by PC2 but the field in PC1 is not automatically update

 

[Gasman]

Refresh the form periodically?

 

[Bieke]

Refresh the form periodically?

I'm not a fan of the form.timer.
It would be nice if there was a function that is executed when you write a value to a table or field on the form.
When you type value's in a field there are several functions like 'on change' but these are not executed when the field is changed when writing a value in it bij using VBA.

 

[Gasman]

You can connect to another DB, but that will be another instance, so as far as the DB is aware, that form is not open.

The essence here really, is do not login on multiple computers at the same time?

 

[GPGeorge]

Access is event driven.

That means, essentially, "Things happen within the database (records added, forms required, reports opened, etc. ) when the user initiates an event in the current database".

In this case, a record being added or modified in a second copy of the accdb doesn't directly trigger an event in the current database. You, the user, have to initiate the action in the current database. You can do that in more than one way, of course.

You can put a timer on open forms so that the timer event fires and in that timer event you can force the form's recordsource to be updated at regular intervals.

You can manually force the form's recordsource to be updated by adding a click event to a control on the form to fire that event.

There are, no doubt, other ways to fire events. The bottom line is still that the event that requeries or refreshes the form's recordsource has to be fired, somehow.

I am beginning to wonder if there is a way to take advantage of Data Macros on tables to initiate events on open forms, though. They are always triggered when data is added, updated or deleted from the table. They are there, I think, primarily for data related tasks. However, they can possibly be used to run procedures in VBA and that might allow you to initiate form requeries. I can't see a way to accomplish that right now, but it might be deeper consideration.

 

[GPGeorge]

Access is event driven.

That means, essentially, "Things happen within the database (records added, forms required, reports opened, etc. ) when the user initiates an event in the current database".

In this case, a record being added or modified in a second copy of the accdb doesn't directly trigger an event in the current database. You, the user, have to initiate the action in the current database. You can do that in more than one way, of course.

You can put a timer on open forms so that the timer event fires and in that timer event you can force the form's recordsource to be updated at regular intervals.

You can manually force the form's recordsource to be updated by adding a click event to a control on the form to fire that event.

There are, no doubt, other ways to fire events. The bottom line is still that the event that requeries or refreshes the form's recordsource has to be fired, somehow.

I am beginning to wonder if there is a way to take advantage of Data Macros on tables to initiate events on open forms, though. They are always triggered when data is added, updated or deleted from the table. They are there, I think, primarily for data related tasks. However, they can possibly be used to run procedures in VBA and that might allow you to initiate form requeries. I can't see a way to accomplish that right now, but it might be deeper consideration.

As I was thinking through this a bit more, it occurred to me, though, that writing code to implement some sort of auto-requery based on the Data Macro event would be so complex as to call into question the ROI needed. As my mother (the daughter of a store-keeper) used to say, "That is too much sugar for a cent." For those of you who never visited a country store, a lot of commodities, like salt, flour and sugar, were sold from bulk containers, weighed out and put into a paper or cloth bag at the time of purchase.

 

[Bieke]

Access is event driven.

That means, essentially, "Things happen within the database (records added, forms required, reports opened, etc. ) when the user initiates an event in the current database".

In this case, a record being added or modified in a second copy of the accdb doesn't directly trigger an event in the current database. You, the user, have to initiate the action in the current database. You can do that in more than one way, of course.

You can put a timer on open forms so that the timer event fires and in that timer event you can force the form's recordsource to be updated at regular intervals.

You can manually force the form's recordsource to be updated by adding a click event to a control on the form to fire that event.

There are, no doubt, other ways to fire events. The bottom line is still that the event that requeries or refreshes the form's recordsource has to be fired, somehow.

I am beginning to wonder if there is a way to take advantage of Data Macros on tables to initiate events on open forms, though. They are always triggered when data is added, updated or deleted from the table. They are there, I think, primarily for data related tasks. However, they can possibly be used to run procedures in VBA and that might allow you to initiate form requeries. I can't see a way to accomplish that right now, but it might be deeper consideration.

I don't know how to run a procedure by changing/updating values in a table by use of data macros. Maybe you have somewhere an example otherwise there maybe is an example on youtube?

Thanks for helping...

 

[arnelgp]

I'm not a fan of the form.timer.
It would be nice if there was a function that is executed when you write a value to a table or field on the form.

the timer is your last hope. so be fan of it.

 

[Bieke]

You can connect to another DB, but that will be another instance, so as far as the DB is aware, that form is not open.

The essence here really, is do not login on multiple computers at the same time?

It is more a common question . Everytime i want to run a database and use 2 different PC's i want to synchronise the other pc for common things like 'login' or changing values in controlfields on the remote form.

 

[GPGeorge]

I don't know how to run a procedure by changing/updating values in a table by use of data macros. Maybe you have somewhere an example otherwise there maybe is an example on youtube?

Thanks for helping...

I was speculating on whether that might work, but the more I thought about, the less enthusiastic I was. It seems the long way around the block to accomplish the task in any event. A timer on a form in the current database is less trouble to implement.

Moreover, I'm not even sure that the Data Macro could accomplish that without some other mechanism in your current database to listen for a change made by the Data Macro. The DM won't invoke a procedure directly, so it's just passing the task down the line, so to speak. All the way around, whatever convenience one might gain from having the current form requeried automatically comes at some cost that makes it a questionable investment.

That said. Here's the basic documentation on Data Macros. https://support.microsoft.com/en-us/office/create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200

 

[Pat Hartman]

I am beginning to wonder if there is a way to take advantage of Data Macros on tables to initiate events on open forms, though. They are always triggered when data is added, updated or deleted from the table.

Due to the way the OS operates, it does not allow a one PC to cause some action on a different PC. Think of the security nightmare that would ensue if Suzie could cause your form to requery at will. Doc has written extensively on this topic.

Access automatically refreshes a form's recordsoruce periodically but it doesn't requery it. That means that changes to data will eventually be visible but new records will never show up because they were not retrieved originally and deleted records cannot disappear but they will show #deleted#.

You can change the refresh interval to make it happen more frequently. The only other option is a timer.

 

[Edgar_]

The browser control can trigger events in multiple frontends at the same time without any timer by using chat technology (websockets). Let me know if you want to implement it. Here's a sample.

Post in thread 'Who keeps Access opened 2 ?' https://www.access-programmers.co.uk/forums/threads/who-keeps-access-opened-2.331475/post-1925259

 

[The_Doc_Man]

I'm with Pat Hartman on this one.

Windows implements a basic security rule called "process isolation" (or some similar name). There is a long history of why this is so, but I'll skip the "why" and get straight to the "how" details:

A running process is not allowed to affect ANY PART of the operation of another (different) running process unless BOTH processes were designed ahead of time to support such an operation. When you throw in "on another machine" then the processes have to establish a network link - a socket, in common terms - so they can connect to each other.

Everytime i want to run a database and use 2 different PC's i want to synchronise the other pc for common things like 'login' or changing values in controlfields on the remote form.

"Process isolation" says "No" to that UNLESS you design a specific type of interface for both processes.

The normal interface for any Access app is the Keyboard/Video/Mouse (KVM) combination, which get assigned to the internal task IN, OUT, and CMD channels (not necessarily in that order). If you want a process to respond to some action, intervening via the KVM connections would likely sever the "normal" connection to the three critical channels, leaving the intervened process uncontrolled. To preserve the KVM connections, you would have to open a connection through a fourth non-standard channel. One method is network-oriented - a network socket, as mentioned earlier.

It is also forbidden to try to step into and alter the memory of another process, again unless a formal shared communication channel was set up. Even so, an in-memory method exists. The Windows MailBox (not to be confused with elements of more formal e-mail products) is an API-controlled pseudo-device that resides in memory. It can be used to communicate between two processes, but communication IS a two-way street, so BOTH processes need to be aware of this method AND they have to have a formal way of handshaking before doing ANYTHING over such a channel. I don't recall whether the MB device can cross network links.

Without a socket or mailbox device, the only point normally in common between the two machines would be the shared back-end, and if that is a "native Access" back-end, it is strictly passive. That is, the DB's BE file is a FILE; there is no attached DB engine there. There is nothing running on the BE's host to actually implement a data macro action. That data macro that was being discussed in other threads won't do any better if you consider where the active database engine resides. The DB engine is attached to the FE's Workspace, it only links to the BE file. Before you say "but Access IS running on the other machine" ... yes. It is running against the FE file. This separation of connection is one of the implications of using Server Message Block (SMB) protocol as the Windows File and Printer Sharing protocol. Unfortunately, that is the way Access was designed so there is no simple way around it.

I know my response makes it sound like this is impossible. It is not. The key is to recognize that ordinary Access methods are quite limited with regard to process interactions. Doing what you want will require some design work to draw out the details of what you need to do to have the desired effect. These details WILL involve a non-standard form of communication between cooperating DBs. The details WILL NOT include a method that violates Windows process isolation rules.

 

[GPGeorge]

Due to the way the OS operates, it does not allow a one PC to cause some action on a different PC. Think of the security nightmare that would ensue if Suzie could cause your form to requery at will. Doc has written extensively on this topic.

Access automatically refreshes a form's recordsoruce periodically but it doesn't requery it. That means that changes to data will eventually be visible but new records will never show up because they were not retrieved originally and deleted records cannot disappear but they will show #deleted#.

You can change the refresh interval to make it happen more frequently. The only other option is a timer.

I am with you on these points. The idea I was entertaining was not allowing your colleagues to randomly interact with your computer. What I was thinking was that a procedure could be set up to react to changes in data in a shared backend, not to an event on a different computer. But that is what a form timer would do anyway.

 

[Bieke]

I'm with Pat Hartman on this one.

Windows implements a basic security rule called "process isolation" (or some similar name). There is a long history of why this is so, but I'll skip the "why" and get straight to the "how" details:

A running process is not allowed to affect ANY PART of the operation of another (different) running process unless BOTH processes were designed ahead of time to support such an operation. When you throw in "on another machine" then the processes have to establish a network link - a socket, in common terms - so they can connect to each other.



"Process isolation" says "No" to that UNLESS you design a specific type of interface for both processes.

The normal interface for any Access app is the Keyboard/Video/Mouse (KVM) combination, which get assigned to the internal task IN, OUT, and CMD channels (not necessarily in that order). If you want a process to respond to some action, intervening via the KVM connections would likely sever the "normal" connection to the three critical channels, leaving the intervened process uncontrolled. To preserve the KVM connections, you would have to open a connection through a fourth non-standard channel. One method is network-oriented - a network socket, as mentioned earlier.

It is also forbidden to try to step into and alter the memory of another process, again unless a formal shared communication channel was set up. Even so, an in-memory method exists. The Windows MailBox (not to be confused with elements of more formal e-mail products) is an API-controlled pseudo-device that resides in memory. It can be used to communicate between two processes, but communication IS a two-way street, so BOTH processes need to be aware of this method AND they have to have a formal way of handshaking before doing ANYTHING over such a channel. I don't recall whether the MB device can cross network links.

Without a socket or mailbox device, the only point normally in common between the two machines would be the shared back-end, and if that is a "native Access" back-end, it is strictly passive. That is, the DB's BE file is a FILE; there is no attached DB engine there. There is nothing running on the BE's host to actually implement a data macro action. That data macro that was being discussed in other threads won't do any better if you consider where the active database engine resides. The DB engine is attached to the FE's Workspace, it only links to the BE file. Before you say "but Access IS running on the other machine" ... yes. It is running against the FE file. This separation of connection is one of the implications of using Server Message Block (SMB) protocol as the Windows File and Printer Sharing protocol. Unfortunately, that is the way Access was designed so there is no simple way around it.

I know my response makes it sound like this is impossible. It is not. The key is to recognize that ordinary Access methods are quite limited with regard to process interactions. Doing what you want will require some design work to draw out the details of what you need to do to have the desired effect. These details WILL involve a non-standard form of communication between cooperating DBs. The details WILL NOT include a method that violates Windows process isolation rules.

conclusion, there is no way to update a remote form from the same database on another PC...
Thanks to all for replying.

 

[Edgar_]

Just a friendly reminder: antivirus software exists because it's indeed possible for computers to trigger actions on one another. So, not only it is not impossible, but it's so common Access can do it.

One way is to utilize websockets; the web browser control can employ that technology to give us the ability to synchronize any number of frontends with the aforementioned control listening for the external stimuli, which is what your common chat application does.

conclusion, there is no way to update a remote form from the same database on another PC...
Thanks to all for replying.

Wrong read post #14....

Example: PC2 issues a notification saying that PC1 wants to log out; since all frontends are listening to each other through the web browser control, PC1 would read it and trigger the action required in real time, no timer involved.

 

[cheekybuddha]

@Edgar_ ,

Do you ever feel like the Invisible Woman

 

[Edgar_]

@cheekybuddha
Please don't make fun of my access-programmers/accessforums/utteraccess experience