The biggest problem - and your ideas of using a common connection point are in line with the restriction I'm about to mention - is that due to old security issues, no Windows process (local OR network-based) may interfere with the operation of another Windows process unless (a) the interfering process is privileged or (b) they have both adopted a method of communication. You are obviously going via path b. In this case, even the benign recognition of a data change is a type of interference, in that it is an attempt to have process B directly make process A do something other than what it was previously doing, by having it recognize a change to something.
From my understanding of Sharepoint, which is strictly limited to what I've read, they are passive tables and you get to them as passive files. Which means the tables themselves will not "help" you directly. They cannot notice anything because they have no intrinsic change detection capacity.
You suggested something running every second via a timer but rightly concluded that a high-frequency event would be likely to conflict with other code. Which means you are correctly considering the overall image. Yes, a once-per-second timer is a real pain in the toches to manage. Every case is different, but I found that timers more often than once per minute were easily able to cause problems. Of course, that depends on what the timer code was doing, but your goal would require some kind of query, and a once-per-second query WILL eat your socks because Access does not normally do parallel processing. You could build a parallel thread using WinAPI calls, but true parallelism isn't the default case.
Your next idea would be to have a backend DB or separate DB. Using another DB, you run into the same problem mentioned earlier, the inability of any non-privileged process to interfere with another process. So you would need to build some sort of communications mechanism in order to implement "path b" as noted above.
I have seen some articles suggesting an I/O based action in which you have a "notifier" that does the checking - and very little else - and have it open a channel via Winsock or another socket-based network communication. Then have a hidden form in your main routine that acts as a listener for that channel. The down side is that the listener form CANNOT DO ANYTHING ELSE. Due to Access and its synchronous I/O operations, that form is dead to the world until the message comes in. That is why it needs to be a hidden, silent form. A further complication is that due to something called I/O rundown, the main process cannot go away unless you open ANOTHER socket to send a message (yes, a process talking to itself) to trigger the shutdown. The "notifier" process might not respond correctly either since it will have a channel open that needs to be closed first before IT will go away peacefully.
Your kind of request comes up frequently, but unless you have a type of database backend that supports data-based triggers, any solution you attempt will be doomed to a massive degree of complexity for what sounds like such a simple question. Active SQL backends can do this but as noted before, Sharepoint is a passive data storage method. For that reason, I think that your request will be difficult if not impossible to get exactly as you might want it.
By the way, we have had several folks claiming they wanted to do something similar to what I described, but I don't recall seeing any success stories.
