How Sql Server can interact with Microsoft Access (or other application) ? (3 Viewers)

[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.