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

[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

 

[amorosik]

"...So, accept my apologies..."Are you kidding?
It's clear that those who respond and spend their time trying to help you don't need to apologize, ever
It's a matter of understanding which path to take, and it's not always easy to explain (at least for me)
And I also understand that my desire not to explain in detail what the requested solution will do can be irritating, but I believe the ability to abstract a problem is important to avoid getting distracted by trivial details
And so, to get back to the problem, I believe the local script option (Python, TWBasic, C#, etc.) that runs a function within the Access code is the most realistic, both in terms of simplicity of construction and low impact on the Access procedure code
Something like:

import socket
import win32com.client

def chiama_access(comando):
    try:
        access = win32com.client.GetObject("c:\erp2\erp2_x64.accde")
        access.Run("Ricevi_Comando", comando)
 
    except Exception as e:
        print("Errore nella chiamata ad Access:", e)

def start_server():
    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    s.bind(("0.0.0.0", 12345))
    s.listen(10)
    print(f"Server TCP in ascolto sulla porta 12345...")

    while True:
        conn, addr = s.accept()
        data = conn.recv(1024).decode("utf-8").strip()
        print("Ricevuto:", data)
        chiama_access(data)
        conn.close()

if __name__ == "__main__":
    start_server()

I don't know if it works like this, but the idea is this: the SQL Server will send a message like "ID_ARTICOLI=ABC123" to the PC 192.168.100 on port 12345
The script on pc 192.168.1.100 will react by receiving the message and launching the function within the Access code
We need to add a check to see if the Access code is running or not, and other things as a safety net in case the program isn't found or to avoid the usual trivial problems, but this is the basic structure

 

[amorosik]

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.

Public Ricevi_Comando(byval command as string) as string
Is a function inside a standard module of the erp2_x64.accde program
We're freeing the poor worms in the can .
The listener is the one posted above or similar
No, there are no local tables, everything is on the database server.

 

[xavier.batlle]

​

After some research, I came across that it would be possible to execute a remote instance of MS Access Using PowerShell, so you could execute that instance using /cmd command to execute whatever task you wanted.
It's not a piece of cake, but it seems it's a real possibility (I've never tried it)

 

[amorosik]

After some research, I came across that it would be possible to execute a remote instance of MS Access Using PowerShell, so you could execute that instance using /cmd command to execute whatever task you wanted.
It's not a piece of cake, but it seems it's a real possibility (I've never tried it)

"..execute a remote instance of MS Access Using PowerShell.."
What you mean exactly?
You say that if on pc1 I have my Sql Server and therefore the script that sends the message, and on pc2, pc3, pc4 I have my erp2_x64.accde program with the Access runtime, is it possible from the script on pc1 to start running (on pc2's memory) the erp2_x64.accde program?
If it were possible, then the local listener script on PC2/PC3/PC4 would also be unnecessary

 

[xavier.batlle]

According chatGPT it's possible, you'll have to research the topic.
This is some of the information I've found:

A) Start MS Access remotely via PowerShell Remoting (background)​

1. Enable remoting on the remote PC (once)​

On the remote computer (as Administrator):

Enable-PSRemoting -Force


This:

• Starts the WinRM service
• Opens the firewall for remoting
• Configures listeners

If they’re not in a domain, you may need to add the remote host to TrustedHosts on the local machine:

Set-Item WSMan:\localhost\Client\TrustedHosts -Value "REMOTEPC" -Force


(Replace REMOTEPC with the computer name or IP.)

---

2. From your PC: open a remote session​

$cred = Get-Credential # user on the remote machine
$session = New-PSSession -ComputerName REMOTEPC -Credential $cred

---

3. Start MS Access (or another app) on the remote machine​

Example for MS Access with a given database:

Invoke-Command -Session $session -ScriptBlock {
$accessPath = "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"
$dbPath = "C:\Data\MyApp.accdb"

Start-Process -FilePath $accessPath -ArgumentList "`"$dbPath`""
}

Notes:

• Adjust Office16 to your version (could be Office15, Office14, etc.).
• If .accdb is already associated with Access, you can also do:
  
  Start-Process -FilePath "C:\Data\MyApp.accdb"
  This will run Access on the remote machine, but in a non-interactive session (no window on the logged-in user’s desktop).

 

[amorosik]

I add that it seems possible to start a program on a remote PC also using psexec which is a utility of the super famous Sys Internals suite

@echo off
REM Avvio remoto di erp2_x64.accde su PC2 con parametro

set REMOTE_PC=PC2
set USER=NomeUtente
set PASS=Password
set ACCESS_PATH="C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"
set FILE_PATH="C:\erp2\erp2_x64.accde"

REM Parametro passato al programma
set PARAM=%1

REM Avvia il programma su PC2
psexec \\%REMOTE_PC% -u %USER% -p %PASS% %ACCESS_PATH% %FILE_PATH% /cmd %PARAM%

 

[cheekybuddha]

The problem with the /cmd switch for this situation is that is does not work if the target accdb is already running.

One possibility is that you can have a db specifically waiting for the /cmd that opens, connects to the user frontends, performs the required actions and then shuts down again to wait for the next call from the SQLServer.

However this is a clunky solution and will probably take several seconds from start to finish - by which time you may as well use polling.

 

[The_Doc_Man]

Public Ricevi_Comando(byval command as string) as string
Is a function inside a standard module of the erp2_x64.accde program

Thank you. I forgot when doing my web search that your first language isn't English. Also, I don't have the ERP libraries so I would not have known about them beforehand.

It is a task with many pitfalls ahead of you, if my "software engineering" gut feeling is still sensitive to difficult problems. Before my Navy job, in the early 1980s, I worked for an oil-and-gas industry company dealing in pipeline control systems. I was one of the lead designers who analyzed major customer requests, so I used to be sensitive to jobs that had hidden engineering costs. Your request got me twitching due to that sensitivity.

It looks like you have settled on an approach. I wish you luck on this.

 

[amorosik]

.... by which time you may as well use polling....

What? Do you want to harm me?

 

[amorosik]

The problem with the /cmd switch for this situation is that is does not work if the target accdb is already running.

Are you saying that /cmd isn't usable if the remote location already has an active Access instance?
Yeah, that could be a problem; it makes remoting useless
Do you think /cmd is also unusable if the command is started from the local PC, for example, from a batch file?

Hey guy, what mean "..Easy peasy lemon squeezy..." ???

 

[sonic8]

So it all seems to be based on this 'service broker'
Yes, that's what I was looking for. I'll try to look into it now.

Did you look into it and if yes, what are your conclusions? (If this was already mentioned in the thread, I must have missed it.)

Compared to some of the other suggestions in this thread, this approach appears to me as much less complex and fragile.


I didn't use Service Broker/Queues in practice yet. But from my cursory review of the documentation I conclude:

I acknowledge and share your aversion to polling. However, Service Broker and its queues were specifically designed to be polled. The queues will also contain much less data than the actual SQL Server tables, so polling would be much less resource intensive. Polling is the intended use of Service Broker Queues.

You could also run a WAITFOR SELECT/RECEIVE statement using ADO and thus delegate any polling that might happen to SQL Server internal functionality. In your Access application you would just listen for the ExecuteComplete event of the ADO.Connection. So, no polling on your end.