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

[amorosik]

"..The "function in Access code" has to check for the message, i.e. polling.."

No, it's not a periodic, continuous query
It would be about retrieving the information needed to manage the function
That's something entirely different, a read performed one time and only when there's actually something to do.

 

[The_Doc_Man]

At this point we are quibbling over semantics. If you have to ask "what messages did I miss" - whether asking once per session or once per message, , it is polling if your alternative is to require an interrupting event. Which is why I suggested that a mail-style notification method would work for you.

 

[DaveMD]

I think the difficulty in trying help on this is that there isn't enough information provided to understand the problem that you are trying to solve. For example:

• How quickly do you need the clients to be notified of changes (half a second, 5 seconds, 30 seconds, a minute).
• How many concurrent users (3, 10, 100, etc.).
• What is the nature of what you are trying to accomplish. Is it that if another user has a particular form open that you just want the notification so MS Access can refresh/requery the form so they see the updated information.
• Are the changes to SQL Server that are of interest only being made through the MS Access client program by end-users or do they also originate from other sources (SSIS data imports, etc.).
• It would be helpful to have some use-cases to better understand the needs here. For example, currently users are frustrated because x, y, z is happening because they are not always seeing the latest information in ____ instances.

Polling is very simple and can work well without the need for overly complex middleware (add-in, service broker, etc). In SQL Server, it is not at all unusual to have simple triggers on tables that update a LastModifiedDate (and optionally a LastModifiedBy) field anytime a record is changed. These can be queried very readily and efficiently by client applications to poll for changes. Even if the table is very large, a non-clustered index could be added on the LastModifiedDate field that includes the primary key that would make polling queries fast and a minuscule load on SQL Server.

If you are set against polling, I think you will be left with much more complexity -- such as developing a COM add-in to leverage query notifications, which would then require an entire new layer of complexity for things to go wrong. For example, not only developing the add-in, but then also needing to manage deploying that add-in to all client systems, dependency and configuration issues, etc.

 

[amorosik]

At this point we are quibbling over semantics. If you have to ask "what messages did I miss" - whether asking once per session or once per message, , it is polling if your alternative is to require an interrupting event. Which is why I suggested that a mail-style notification method would work for you.

Perhaps I'm not using the terms correctly because I'm translating from my native language to English
But the key point is that method 2, which I believe to be feasible, cannot be considered "polling," even if the external script were only used to "launch" the function within the Access code and the code then retrieved the information needed to process the request
This is because the information retrieval phase would only occur following the command provided by the external script
Furthermore, the external script would launch the internal Access function as quickly as possible after the data change event in the database occurred
So, I don't think it's a misunderstanding of the terms; it's a radically different procedure from traditional database polling, with a significant impact on performance if performed at high frequency, and a significant delay (a delay that would not always be the same) from the event if performed at low frequency.

 

[amorosik]

I think the difficulty in trying help on this is that there isn't enough information provided to understand the problem

And one of the information provided is the exclusion of periodic querying of the db server

Polling is very simple and can work well without the need for overly complex middleware

Yes sure
But I expressly excluded it from the possibilities

 

[Albert D. Kallal]

No, polling db is not acceptable

But I expressly excluded it from the possibilities

Well, you have this position on this matter - but people here do not!
(and that’s the problem here!)

We here have not agreed to that position of yours!

It may well be YOUR position on this matter, but it is not (yet) OUR positions here on this matter.

So, at this point in time, this is not a fact, but your opinion on this matter. Might be the only viable solution, but we don’t know that, and it’s not our opinion here that this is the only solution.

No, the important thing is to find a way of asynchronous communication between the database server and individual workstations

Is it? We don't know that just yet - might be better or other more practical solutions here.

Again, this is not everyone's view here on this matter.

We here are still open to alternatives – and all good developer’s should be as such.

No, the operations performed by the Access code are irrelevant

Again, that's your view on this matter - we not seeing things this way.

They could be operations that need to be completed quickly, or operations that can simply be displayed on screen and then the operator can check when they're at their workstation

Sure, they could be - I'm fine with above, but above information does not suggest, or preclude that only solutions are non polling.

And again, I'm 100% open to that you looking for a non polling solution. And it may well be the only practical solution.

And it may well you 100% believe that this is the only solution you are looking for!

Again, that's fine!

However, as I point out, that is NOT everyone’s else’s position here! We here are MOST open to alternatives!

You what this is called?

It called being open to advice and alternatives – and it ALSO called how you work with other developers….

And as I pointed out, you don't have to make a valid case as to why polling solutions are off the table, but again, until you do, then others here are going to be open to alternative approaches - hence that's the confusing here - these alternative ideas are not thus some language nor cultural issue.


Do you see where this is going?

The narrative is FAST becoming one of you not being open to any alternatives and accepting any other advice on this matter then, right?

In other words, other developer’s and people are going to have a different view and opinion(s) on this matter, and that's the source of confusing here - in other words, others here are willing to entertain alterative solutions, and I for one respect such advice. Advice here in public is "as is", and everyone is free to accept or reject such advice. And we ALSO here are free to reject or accept polling solutions....

Edit: I want to also add? There is nothing wrong you wanting an answer and pursing a non polling solution - none at all!
It just that others here see other roads - and they are not convinced that non polling is the only way to go here. This is not a big deal, but the above does explain why some "push back" is occurring here! And it's simply that others think that a polling solution is a possible solution here, despite this not being your goal.


R

Albert

 

[The_Doc_Man]

There is a potential path for this that I suggested and you have either ignored or rejected. I didn't see you respond to it. If I missed it, I'm sorry.

I recall you being this way a couple of times before this. Once was when we discussed whether you could have multi-threaded VBA using a Fork process API call. A more recent excursion was a discussion regarding network sockets. You get ideas in your head and can't shake them loose. You want Access to do things it wasn't really built to do. You get incensed when we express skepticism over the viability of what you want to do. OK, I get that. I feel the same way about my income taxes.

However, to summarize, you seem to want some sort of notification that goes to selected users when a certain action occurs based on info that is immediately available from SQL Server. You don't want polling or periodic querying. OK, I can see that you want this to be set up for immediate asynchronous transmission & response, because you don't want to overload the system with fast polling or delay response by slow polling.

You DO want that the notification will go to your list of users - immediately if they are already logged in, or at their login time if not. This notification will somehow cause them to do something different than they might do if no message is waiting.

The only part I don't know is this question: whether you need Access VBA to directly respond to the notice or whether there is some discretion on the recipient's part as to if, when, or how to respond, such that they might need some think time to respond.

If this needs to be a full closed-loop automation solution, you are asking a very BIG ask for Access because you would need some kind of network-based class object that has "With Events" so that you can trigger it into a background "wait" mode and wait for it to fire. But if the people who get the message potentially have decisions to make on receipt, what is wrong with using e-mail notification sent from some kind of triggered routine in SQL Server? Then run the mail client in the background on the PCs and let SQL Server send out stuff in a distribution list. As long as you don't need completely closed operation, WHAT IS WRONG WITH MAIL? It has exactly the notification profile that I've been able to glean from your posts.

 

[amorosik]

Well, you have this position on this matter - but people here do not!
(and that’s the problem here!)

We here have not agreed to that position of yours!
-----
It may well be YOUR position on this matter, but it is not (yet) OUR positions here on this matter.

Edit: I want to also add? There is nothing wrong you wanting an answer and pursing a non polling solution - none at all!
It just that others here see other roads - and they are not convinced that non polling is the only way to go here. This is not a big deal, but the above does explain why some "push back" is occurring here! And it's simply that others think that a polling solution is a possible solution here, despite this not being your goal.

R

Albert

I asked a question that seems pretty specific
There may be answers that address the issue
Or there may not be a solution to what i am asking
But what you write is "...but people here do not..." or "...It's just that others here see other roads..." has nothing to do with the problem posed or the possible solutions

 

[amorosik]

If this needs to be a full closed-loop automation solution, you are asking a very BIG ask for Access because you would need some kind of network-based class object that has "With Events" so that you can trigger it into a background "wait" mode and wait for it to fire

This one
"..you are asking a very BIG ask for Access.." If it had been simple, I wouldn't have asked .

 

[CJ_London]

I asked a question that seems pretty specific
There may be answers that address the issue
Or there may not be a solution to what i am asking

The issue is vaguely described in terms of ‘what happens next’ (user might have access open , might not, user might be away from desk, might not, users machine might be switched on, might not, might have a requirement for immediate action, might not, user might be using access, might not, the message might take direct action, might not) and specifically excludes polling as a solution then the answer is ‘there is not a solution’

 

[amorosik]

The issue is vaguely described in terms of ‘what happens next’ (user might have access open , might not, user might be away from desk, might not, users machine might be switched on, might not, might have a requirement for immediate action, might not, user might be using access, might not, the message might take direct action, might not) and specifically excludes polling as a solution then the answer is ‘there is not a solution’

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

 

[The_Doc_Man]

This one
"..you are asking a very BIG ask for Access.." If it had been simple, I wouldn't have asked .

But you HAVE avoided answering the other question. What is wrong with using MAIL to deliver your messages? It has EXACTLY the notification profile that you have identified and unless you have some incredibly specific terms in some contract, it would seem that you are trying to re-invent the wheel. We don't open an .ACCDB file with Word because that doesn't have the ability or the performance profile to handle the content. Similarly, you are asking Access to solve a problem ALREADY SOLVED by Outlook and numerous other mail clients.

 

[CJ_London]

What is wrong with using MAIL to deliver your messages?

my guess would be that the OP will say that outlook is polling the email host for new messages. You could use python or C# to poll the email host regardless of whether outlook (or access) is open or not and send a message to the systray. But that is still polling. SQL server has the ability to send an email (probably already been mentioned) but I've never used it and don't know if you can specify multiple users. Since it uses smtp, I don't see why not. Also don't know if that is limited to on-prem sql server

 

[amorosik]

Sorry, I missed your reply about email
There's nothing wrong with that. Even an email message would work fine
It would be enough to figure out how to get it received by the currently running Access process
Basically, the Access process should also contain a pop3 server
Is that what you're suggesting?
If so, then yes, this is also a possible method
It seems more complicated than the 2 method I suggested, which essentially involves an external local script that listens for messages sent by the database server
If a message arrives, it calls a function within the Access code, which obviously must be running
If it's not running, nothing happens.

 

[amorosik]

my guess would be that the OP will say that outlook is polling the email host for new messages. You could use python or C# to poll the email host regardless of whether outlook (or access) is open or not and send a message to the systray. But that is still polling. SQL server has the ability to send an email (probably already been mentioned) but I've never used it and don't know if you can specify multiple users. Since it uses smtp, I don't see why not. Also don't know if that is limited to on-prem sql server

No, using Outlook is out of the question; we'd be back to polling

 

[CJ_London]

told you @amorosik - what is your objection to polling? What is the real life situation you are trying to solve? Provide a real scenario where polling is not an option.

 

[The_Doc_Man]

OK, there's the rub... you aren't content for the user to see a message... your ACCESS app has to see the message. And there, you are re-inventing wheels. We are back to creating some sort of networked solution involving sockets. You are fond of learning English phrases of an idiomatic nature. Here's one for you - "painting one's self into a corner." You are inventing a protocol between trigger routines in SQL Server and client copies of MS Access on the PCs in the field. Now you would have to define network behavior by answering such questions as:

1. Does the SQL Server side of this have to maintain a notification queue that doesn't get emptied until every potential recipient is notified?
2. When an event triggers the data condition, does the SQL Server side have a list of recipients or is that list fluid?
3. Does the SQL Server side have to be notified when the Client side members individually log in? (Sounds like they would.)
4. Does the Client side have the right to reject a message if it is busy?
5. What are the ramifications of delays on the Client side and specifically, what is the Server supposed to do about it?

There are more questions, but I believe the label on the can you just opened reads "Acme Worms".

 

[amorosik]

told you @amorosik - what is your objection to polling? What is the real life situation you are trying to solve? Provide a real scenario where polling is not an option.

Polling is not an option when the initial request says "polling is not an option"
The real scenario is irrelevant to the problem's solution

 

[amorosik]

OK, there's the rub... you aren't content for the user to see a message... your ACCESS app has to see the message. And there, you are re-inventing wheels. We are back to creating some sort of networked solution involving sockets. You are fond of learning English phrases of an idiomatic nature. Here's one for you - "painting one's self into a corner." You are inventing a protocol between trigger routines in SQL Server and client copies of MS Access on the PCs in the field. Now you would have to define network behavior by answering such questions as:

1. Does the SQL Server side of this have to maintain a notification queue that doesn't get emptied until every potential recipient is notified?
2. When an event triggers the data condition, does the SQL Server side have a list of recipients or is that list fluid?
3. Does the SQL Server side have to be notified when the Client side members individually log in? (Sounds like they would.)
4. Does the Client side have the right to reject a message if it is busy?
5. What are the ramifications of delays on the Client side and specifically, what is the Server supposed to do about it?

There are more questions, but I believe the label on the can you just opened reads "Acme Worms".

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'."

 

[Edgar_]

PMFJI, the websocket solution does not care at all about any of these supposed problems, it only cares about who is connected to the websocket, and whoever is connected, it will receive the message.

You can then choose what to do with the message by using the event in code in any way you want because all you want is said event of the message and, possibly, some content that helps you identify what to do with it, programmatically.