How Sql Server can interact with Microsoft Access (or other application) ? (1 Viewer)

[amorosik]

Yes, of course, this is hypothesis 2 considered in the post #19
It is certainly the most effective, but also the most technically complex to implement

 

[The_Doc_Man]

1- no, the SQL Server code sends the messages to the intended recipients, but it doesn't maintain a queue and doesn't retransmit.
2- yes, the code inside the SQL Server trigger must know which clients to send the message to.
3- no, SQL Server sends and doesn't care who receives.
4- the client side might not be running, and the message addressed to it would be lost.
5- 'ramification delay' I don't understand what you mean
Sorry, but I don't understand "..but I believe the label on the can you just opened reads 'Acme Worms'."

1. Then there is no feedback to acknowledge receipt. You have no way to know that ANYONE saw ANY message.
2. Is this list mutable? Can you easily add an employee to get these messages AFTER this solution is deployed? How easy would that be? (In your design in your head that you are working from.) Can you REMOVE an employee after deployment? Have you considered this kind of maintenance?
3. Then basically you could have a case where everyone on the list was elsewhere at that time and nobody saw anything. And when nothing gets done correctly, the intended recipients later have plausible deniability that they didn't get the message.
4. Then when nobody is listening, nobody heard the tree that fell in the forest and no one will hear its echoes.
5. OK, if you don't like "ramifications", try "consequences". If you want to know that the message went out to the intended recipients, you need a feedback mechanism. If you don't have a feedback mechanism, then you obviously don't care whether someone gets notified. So my solution is don't send any messages in the first place, since you don't even care to verify that they are received.

Basically, your words tell us that you have overspecified this messaging requirement because you have no feedback loop to verify that the messages went through. This "asynchronous notification" method doesn't need to know anything at all. Which means NONE of your specifications are self-consistent. You might as well have NO messaging. Heck, polling would be more reliable than what you just described. I don't give a raspberry about "no polling" because your specification is so inconsistent that it might just as well be "no messages."

I reiterate that you might as well use SMTP-class mail to the person, NOT to the application, because as lax as the feedback seems to be in your problem description, ordinary e-mail will do just fine.

The common phrase you didn't understand is "opening a can of worms". It implies starting a project which will be extremely complex, with many parts and difficult relationships. Gets worse when the phrase is "bucket of worms" since some of them might still be alive and therefore still moving when you pour them out of the bucket. This is the Google Gemini definition:

"Opening a can of worms" means to create a complicated, unpleasant, or difficult situation by doing something that leads to many new problems. The phrase is a warning that an action, though perhaps intended to solve one issue, will likely result in a tangled mess of new complications that are difficult to control. The idiom alludes to the messy, unmanageable jumble of worms that would come out of a literal can.

 

[amorosik]

1. Then there is no feedback to acknowledge receipt. You have no way to know that ANYONE saw ANY message.
2. Is this list mutable? Can you easily add an employee to get these messages AFTER this solution is deployed? How easy would that be? (In your design in your head that you are working from.) Can you REMOVE an employee after deployment? Have you considered this kind of maintenance?
3. Then basically you could have a case where everyone on the list was elsewhere at that time and nobody saw anything. And when nothing gets done correctly, the intended recipients later have plausible deniability that they didn't get the message.
4. Then when nobody is listening, nobody heard the tree that fell in the forest and no one will hear its echoes.
5. OK, if you don't like "ramifications", try "consequences". If you want to know that the message went out to the intended recipients, you need a feedback mechanism. If you don't have a feedback mechanism, then you obviously don't care whether someone gets notified. So my solution is don't send any messages in the first place, since you don't even care to verify that they are received.

Basically, your words tell us that you have overspecified this messaging requirement because you have no feedback loop to verify that the messages went through. This "asynchronous notification" method doesn't need to know anything at all. Which means NONE of your specifications are self-consistent. You might as well have NO messaging. Heck, polling would be more reliable than what you just described. I don't give a raspberry about "no polling" because your specification is so inconsistent that it might just as well be "no messages."

I reiterate that you might as well use SMTP-class mail to the person, NOT to the application, because as lax as the feedback seems to be in your problem description, ordinary e-mail will do just fine.

The common phrase you didn't understand is "opening a can of worms". It implies starting a project which will be extremely complex, with many parts and difficult relationships. Gets worse when the phrase is "bucket of worms" since some of them might still be alive and therefore still moving when you pour them out of the bucket. This is the Google Gemini definition:

All the points you listed have nothing to do with what I requested
Who is making the request, whether there is feedback or not, whether the recipient list can change or not, have nothing to do with the original questione, that is:
"...how can we 'notify' the executing Access procedure that the ARTICLES table at the row ARTICLES_ID=12345 has been modified?..."
My question focuses on how to "enter" the message recipient's Access code
Maybe I can't explain myself very well, but this is the question
And I wanted to understand if, beyond the two options listed in post #19, there are any others that are better or simpler to implement.

"..I reiterate that you might as well use SMTP-class mail to the person.."
This option would be fine if the SMTP server code were integrated into the Access code. Conversely, if we're talking about using a classic SMTP server separate from the Access procedure, then it wouldn't be suitable because it would force Access code a periodic query, and that's what I want to avoid

 

[CJ_London]

the problem is you say

"...how can we 'notify' the executing Access procedure that the ARTICLES table at the row ARTICLES_ID=12345 has been modified?..."
My question focuses on how to "enter" the message recipient's Access code

and

the client side might not be running, and the message addressed to it would be lost.

which implies whatever the message is, it is not important and it doesn't matter if nobody receives it.

and to be clear - sql server uses smtp to send emails and unless you log the send into a sql table as part of creating the message the message is not tracked in any way. they are received using imap or pop3 for which I mentioned many posts ago could be run in python or C#. Python can update an access table or display a message on screen (or both). But python would be polling to see if anything new has arrived in the designated imap folder - and for whatever reason you don't want to poll.

No idea if this works for you, I have a simple messaging service that users can use to message each other by posting messages to a table. Access doesn't 'poll' as such but when certain events fire, such as returning to a menu form or opening another form a bit of code does a lookup on the table for unseen messages and displays them. So ultimately each user sees all their messages (something you are not bothered about, so easy to exclude 'old' messages). Point is Access is running and the user is at their desk when the messages are made visible. They switch off and go home, they'll be there when they return the following day. They go to lunch, there they are when they get back.

 

[amorosik]

It's true that sending emails from SQL Server is simpler, but I understand that a SQL Server trigger can launch a SQL Server job, which in turn can run a PowerShell or cmd script
So, on the event source side, I don't think there are any problems (although I haven't actually tried it yet) intercepting a change to a data table and running any executable
The problem is on the receiving end (there could be one or more of these)
And on the receiving end, I'd like to be able to react quickly, and only when there's actually a change to the data table
That's why I'm considering calling the public function Receive_Command() in the Access code of the running procedure as the simplest and least invasive way to do so

 

[The_Doc_Man]

May I ask in which context Receive_Command is defined? It is not one I have come across. I can find more than a couple of places where Receive (without the _Command suffix) is a defined function. But how that is any different than either a pending I/O on a network channel or polling. Everything I looked up with several web searches targeting Windows environments lacked any knowledge of that specific function. That makes it really hard to evaluate your intent.

Agreed, it would be trivial to initiate some kind of transmission from a data trigger in an SQL Server database. The "can of worms" I referenced earlier IS, as you said, on the receiving end. The wormy part is doing this on the Access end, compounded by the fact that you have to have two things going on at once, BOTH of which must act independently.

In that receiving end, one thing is whatever data entry/edit/update form your people use to do their daily work. One would presume that this part would be relatively easy, with the exception of notifications.

The other would be the separate "listener" that does something to make connections so that at appropriate times it would notify the people that something needs to be done. You talk about writing something in python or C# or something else that can update something. But... (I hear the can opener)

You said this has to notify the Access app, or at least that was the takeaway that I got from your discussions. If your data alerting the main users is updated via python or C#, you STILL have not addressed how to cross task boundaries, because that task will be a different task than Access and the Windows cross-task influence rules will get (still be) in the way.

If you update a BE table with this notice, you are back where you started. It is going to come down to having something in the FE that will respond to an event so that the FE will show it, OR my suggestion to send a MAIL notification (which doesn't have to diddle with the FE at all).

If you have a "local" table in the FE, you need that data update event to have something to intercept the event from inside the FE - but now you have to consider how this would be done because you need to notify each active task. And we come back to behavior when some users are running your app but others have walked away from their desks for personal or business reasons.

 

[Albert D. Kallal]

The user's behavior has no bearing on the request
The question is: "...how can we "notify" the executing Access procedure that the..."

The operator could be at the desk and react in some way to the message
Or they could not be there
Or the computer could be turned off

None of these three conditions has any bearing on the initial request
What is relevant is the initial request

Don't worry! - you simply wanted a non polling event - other issues are not your goal here!

I was just pointing out that others here have other ideas and are going on tangents - one's you not interested in!!!

So, while I explained others (even me) have other ideas and suggesting's here? They are not what you looking for!

These are not the droids you are looking for - move along.....

So, take no worries - I probably should not have attempted to explain why you receiving all kinds of suggesting's, one's you not looking for!

And to be fair, while I think other ideas should be on the table? Don't matter - and that is WHY my answer was framed in terms of a non polling solution. Stick to your guns here - stick to what you looking for!!!

Also, one more issue here.

If one were to adopt a SQL stored procedure (.net, or Python - SQL supports both).?
The problem is those SP's are state-less. That means the trigger starts, the sub routine starts, and then goes out of scope. That means the trigger code (in .net/phyton) can't be a winsock host (server), since the routine will be going out of scope after the SP runs. Also, creating or using COM objects in what amounts to a transaction safe SQL server? probably not a good idea.

So, this THEN means one should adopt the Service broker, and SQL server messaging. So, the trigger can que a message into the service broker que, and then a event listener (not polling) can be written in .net/phyton and do the push out to clients via tcp/ip.


So, one does not want to "load up" a SP (stored procedure), and that SP needs to be as tight as possible, clean as possible, and non blocking as possible.....

I never used the SQL queuing/messaging system in SQL, but it's high time I do learn how this works
(it has ramifications on our web site that shares the same database as our Access front ends. And we have a polling solution for the web site right now - and it would be a rather great/good idea to move over to a event based solution.

So, accept my apologies for going off your goals and about how others here are suggesting polling solutions....

R
Albert