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

[amorosik]

How can SQL Server send a "message" to an Access procedure when a certain condition occurs in the data?
For example, suppose we have an ARTICLES table that, in addition to other fields, also has ARTICLES_ID as the primary key
Suppose the condition is "any change to a record"
Suppose the article with ARTICLES_ID=12345 is modified by a user connected to the SQL Server data.

The question is: how can we "notify" the executing Access procedure that the ARTICLES table at the row ARTICLES_ID=12345 has been modified?
Polling db is not acceptable, i would like to understand (if it's possible) how to send a push message from the database server to the program

 

[arnelgp]

from Copilot answer.
you can add "trigger" to your sql server when a record is modified or added.
like creating a record on the audit trail table on your sql server.

on ms access part, again from CoPilot:
MS SQL Server cannot directly “push” notifications into Access when a linked table changes. Access is fundamentally a client application, and its linked tables are just views into SQL Server data. There’s no built‑in event pipeline between SQL Server and Access.

Workarounds
Here are the practical patterns people use:

1. Polling / Timestamp Columns
Add a LastModified column in SQL Server (updated via trigger).

In Access, periodically query for rows where LastModified > LastCheckTime.

This simulates “notification” by checking for changes.

2. Audit / Change Tracking Tables
Use a trigger to insert into a “ChangeLog” table whenever data changes.

Access queries the ChangeLog table to see what’s new or modified.

This is more efficient than scanning the whole table.

3. SQL Server Change Tracking / CDC
SQL Server has Change Tracking and Change Data Capture (CDC) features.

Access can query these system tables to detect changes.

This is more advanced but gives you row‑level change info.

4. External Notification (Service Broker / Email / API)
SQL Server can use Service Broker or send an email alert when data changes.

Access can’t directly consume Service Broker, but you could build a middle layer (e.g., a small .NET service) that listens for SQL events and then updates Access or notifies the user.

5. Access Form Logic
If you only care about notifying users while they’re in Access, you can add a timer event to a form that periodically checks for changes in SQL Server and pops up a message.

 

[The_Doc_Man]

I agree with arnelgp on what he has written. I will add a different view.

Windows has a rule regarding process isolation. This security-based rule was mandated by the U.S. Government back in the 1990s, no exceptions allowed if you wanted to continue selling to the government. Early versions of Windows did not comply with the rule. That is because early Windows still sat "on top of" MS-DOS as a layered product. Windows NT was the first true Windows operating system that did NOT sit on top of DOS, and it enforced the isolation rule in a way that a DOS system never could. OK, that's the history. Now the effect of the above...

Windows wants every process to start off as though it had the whole machine to itself, to the point of not even knowing whether someone else's tasks are running - or even if the single user triggered multiple open windows - not tabbed, but independent windows. You have to use API calls or library functions that hide API calls in order to find out about your host system's activities. No process can interact with another process... at least, not initially.

The loophole is that you CAN program ways for two processes to interact. The hard part is that Windows assumes you DON'T want to do that, so it is extra work if you do. You have to therefore DESIGN a communication feature into what you are building - and communication is a usually a two-way street, so both sides of this inter-process communication have to be considered. In simple terms, you have to design a protocol that operates between the SQL server and the Access application, other than the ODBC or equivalent protocol for query execution.

arnelgp's comments touch on methods you might consider using for this protocol. Given that you are interested in knowing when changes are made, this implies either that you will be available and online during working hours so that you can immediately see notifications, or you have to be content to check logs revealing those changes.

 

[GPGeorge]

How can SQL Server send a "message" to an Access procedure when a certain condition occurs in the data?
For example, suppose we have an ARTICLES table that, in addition to other fields, also has ARTICLES_ID as the primary key
Suppose the condition is "any change to a record"
Suppose the article with ARTICLES_ID=12345 is modified by a user connected to the SQL Server data.

The question is: how can we "notify" the executing Access procedure that the ARTICLES table at the row ARTICLES_ID=12345 has been modified?
Polling db is not acceptable, i would like to understand (if it's possible) how to send a push message from the database server to the program

In addition to the suggestions arnelgp offers, you should consider whether you can implement a Power Automate Flow that reacts to a SQL Server table trigger or other event and sends a notification that Access can receive.

 

[sonic8]

I think this is intended way by Microsoft to solve your problem.

Working with Query Notifications - SQL Server Native Client

Query notifications allow you to request notification during a time-out period when the underlying data of a query changes in SQL Server Native Client.

learn.microsoft.com

 

[Edgar_]

Just remember that web browser controls can send notifications through websockets. If the client used to update the record is Access, you can set it up so that this instance of Access sends a message to other clients once the record is updated. Any other Access clients connected to the same websocket via a web browser control will get the notification and can react to it in real time, without needing to poll. I've posted proofs of concept of this for a while.

Now, if your database is behind a server that responds to requests, the server could react to this too, and you could also use the websocket approach to push the notification.

 

[Albert D. Kallal]

I think the REAL question is what do you want to occur when that data changes?
And who, and how and why did that data get changed?

So, you could I suppose setup a database trigger, and if that change occurs, then "more" or additional data processing could occur, including that of say a email being sent out.

However, this really begs the question - who and how did the data get changed in the first place?

It's really at THAT point in time that some email, or additional processing should occur. Not some kind of "magic" event occurring all of it's own accord.

In other words, more thought and effort to the actual problem being solved here is required.

It always seems nice to try some catch all solution, but then we are ignoring the how and why that event and action occurred in the first place.

The why and when DOES matter here, and that why and when needs to be defined. We are in effect trying to skip basic system analysis here - and we should not.


R
Albert

 

[Edgar_]

The why and when DOES matter here, and that why and when needs to be defined.

Indeed, it's not the same if the record was affected from access, a terminal command, or external client. Identifying the source of the modification is the first step, and it's there where you have to add the code to do additional stuff, if such environment allows it.

 

[Isaac]

In addition to the suggestions arnelgp offers, you should consider whether you can implement a Power Automate Flow that reacts to a SQL Server table trigger or other event and sends a notification that Access can receive.

+1 on this, OP should look into.
Power Automate or SSIS that looks for changes or looks for a notification record produced by a sql server trigger. Quite simple.

 

[amorosik]

arnelgp's comments touch on methods you might consider using for this protocol. Given that you are interested in knowing when changes are made, this implies either that you will be available and online during working hours so that you can immediately see notifications, or you have to be content to check logs revealing those changes.

"..this implies either that you will be available and online.." no

If the operator is available when the ARTICLES table is modified, the system sends the message to the workstation, the operator can react and perform the required operations
If the operator is not present at that time, nothing happens; the message arrives and is displayed if the workstation is turned on.

 

[amorosik]

from Copilot answer.
you can add "trigger" to your sql server when a record is modified or added.
like creating a record on the audit trail table on your sql server.

on ms access part, again from CoPilot:
MS SQL Server cannot directly “push” notifications into Access when a linked table changes. Access is fundamentally a client application, and its linked tables are just views into SQL Server data. There’s no built‑in event pipeline between SQL Server and Access.

Workarounds
Here are the practical patterns people use:

1. Polling / Timestamp Columns
Add a LastModified column in SQL Server (updated via trigger).

In Access, periodically query for rows where LastModified > LastCheckTime.

No, polling db is not acceptable

 

[amorosik]

In addition to the suggestions arnelgp offers, you should consider whether you can implement a Power Automate Flow that reacts to a SQL Server table trigger or other event and sends a notification that Access can receive.

Okay, this could be a possibility

 

[amorosik]

I think this is intended way by Microsoft to solve your problem.

Working with Query Notifications - SQL Server Native Client

Query notifications allow you to request notification during a time-out period when the underlying data of a query changes in SQL Server Native Client.

learn.microsoft.com

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.

 

[amorosik]

Just remember that web browser controls can send notifications through websockets. If the client used to update the record is Access, you can set it up so that this instance of Access sends a message to other clients once the record is updated. Any other Access clients connected to the same websocket via a web browser control will get the notification and can react to it in real time, without needing to poll. I've posted proofs of concept of this for a while.

Now, if your database is behind a server that responds to requests, the server could react to this too, and you could also use the websocket approach to push the notification.

I don't quite understand this one
Suppose there are three workstations, PC1, PC2, and PC3
If PC1 modifies a record in the item table, how would PC2 and PC3 be informed?

 

[amorosik]

I think the REAL question is what do you want to occur when that data changes?
And who, and how and why did that data get changed?

So, you could I suppose setup a database trigger, and if that change occurs, then "more" or additional data processing could occur, including that of say a email being sent out.

However, this really begs the question - who and how did the data get changed in the first place?

It's really at THAT point in time that some email, or additional processing should occur. Not some kind of "magic" event occurring all of it's own accord.

In other words, more thought and effort to the actual problem being solved here is required.

It always seems nice to try some catch all solution, but then we are ignoring the how and why that event and action occurred in the first place.

The why and when DOES matter here, and that why and when needs to be defined. We are in effect trying to skip basic system analysis here - and we should not.


R
Albert

No, the important thing is to find a way of asynchronous communication between the database server and individual workstations, a system to alert workstations when something happens to the data.
Even how the data is processed isn't important; each workstation could react differently

 

[Edgar_]

I don't quite understand this one
Suppose there are three workstations, PC1, PC2, and PC3
If PC1 modifies a record in the item table, how would PC2 and PC3 be informed?

Think of it like a group chat. PC1 says hello, PC2 and PC3 see it because they are connected to the same chat window. A chat service is a Websocket too. The 3 workstations must have a web browser control connected to the same websocket. And on message, all workstations react at the same time. You send the message programmatically when an update or anything else is detected. That message is the push notification.

 

[The_Doc_Man]

I don't quite understand this one
Suppose there are three workstations, PC1, PC2, and PC3

If PC1 modifies a record in the item table, how would PC2 and PC3 be informed?

If the operator is available when the ARTICLES table is modified, the system sends the message to the workstation, the operator can react and perform the required operations
If the operator is not present at that time, nothing happens; the message arrives and is displayed if the workstation is turned on.

No, polling db is not acceptable

This is similar to the "tree falls in the forest" conundrum. If nobody was listening, is notification even necessary?

The only thing I can see here that might come close to this combination is if you set up a mail message to a distribution list naming each user who might need to know of the changes. That has the behavioral property that your mail is received very shortly after someone logs in, and messages are HELD for offline systems UNTIL they log in. Mail isn't QUITE a polling scheme, though one could argue that Post Office Protocol DOES involve asking for messages to be sent in sequence, kind of like polling.

If you have Classic Outlook, you can even trigger it to read mail messages, perhaps if they have a specific subject line. Even with New Outlook, though you can't automate that, you at least can notify your users asynchronously.

 

[Albert D. Kallal]

No, the important thing is to find a way of asynchronous communication between the database server and individual workstations, a system to alert workstations when something happens to the data.
Even how the data is processed isn't important; each workstation could react differently

Well, at some fundamental level, actually it does matter.

Since if it did not, then you would not be asking this question, would you? In other words, a big reason exists for this goal – and that goal is 100% centered on some data being changed. As I stated, if it did not matter, then you would not have a question nor goal here, would you?
So, yes, it HAS to matter!


Now, having stated the above, and assuming you analyzed the workflow and requirements here?

You could build such a system, but SQL server out of the box does not have this ability. However, SQL stored procedures can be T-SQL code, or you can call/run .net code in place of a T-SQL stored procedures.

For developers that don’t like t-sql, or you need more complex code? Then writing SQL stored procedures in .net code is often a really great solution. And this means .net opens up the .net framework to SQL server.

So, things like ensuring additional ports are not blocked by the server running SQL server (or your company network – often those new hardware “security” switches block ports).

And this solution would mean hosted SQL server options are off the table (since you would have to setup and install some .net code on that server – and open additional ports).

And on the Access side?

You would still need to build some moving parts. Either using VBA + win sockets, or have VBA call some .net code (that allows VBA to “sink” events from .net class into VBA – it’s not hard to do this if you comfortable building .net code and consuming it from Access). I write and use .net code from MS-Access near every day - and we moved a lot of our VBA code out into a .net business object so both the web site, and MS-Access can share the same complex code base).

And on the Access side?

Any un-handled error going to blow out that global object (that’s setup to trigger events when data is received from the server). So, you have to setup startup code in Access to setup that network socket and setup events that trigger VBA code.


All of the above is possible. And thus this would depend on how comfortable you are with .net, and consuming .net code from Access.

On the other hand, a simple hidden form with a timer on startup would also do the trick here, and in fact, it would work for any type of database or server you are using – even hosted ones.

You, or your users would not even care or have to know about this hidden form – and it’s existence would not effect the general operation of your existing application now.

So, once again, if the goal is a message or notification - why do you actually care how this goal is achieved?

Either road going to require moving parts in the Access client side of things.

So, SQL server is a database engine and system – it’s not designed for push notifications. But, as noted, such a setup is possible if you were willing to write and setup .net code on both ends.

R

Albert