Automatically Refresh a form when table data changes externally, table macro perhaps?

[bignose2]

Hi,
Is there anyway to get a form to refresh when a record is added to a table.

Basically I have a python program to get caller ID from incoming phone calls (Gave up trying with VBA Tapi) & it updates the db table via odbc.

Works great, table is updated but I have a form that just sits there & would love for it to automatically refresh when a call comes in.

Just discovered Table Macros, I suspect & cannot see the ability to interact with forms but is there something I have missed that could automatically trigger something that then be used to refresh a form.

I imagine a similar situation if a difference user was changing data on the back end, so not just my unusual use of an external odbc update.

Will use timer if I have too, but not keen, I don't know how resource hungry they are (I do use but on checking stuff once every few minutes or hours)
For this caller I will have checking every half second or perhaps quicker, call comes in I want to see almost immediately, need to just look & cannot be pressing a key to refresh.

Perhaps if no option, can you advise ifusing timer could be an issue?

Thanks I/A

 

[Edgar_]

WebSockets respond to external stimuli. The legacy browser control supports them with IE11 emulation, and the Edge control should too. The process is as follows:

1. Load web browser control with WebSocket listener.
2. Python script writes to table via ODBC.
3. Python script triggers the WebSocket.
4. Web browser control responds, refreshing your form.

 

[bignose2]

Many thanks for such a fast response ,sounds ideal, will look into it.

Tiny bit cautious about using another external process, just in case in the future the web socket changed, not always in a position to revisited coding the DB.
I may be talking rubbish as I don't know about the web sockets, just my thoughts.

It's what seems like ocx depreciation that has got me on a coding path I do not really have time for but to get my program functioning as it use too.

 

[DickyP]

The real question is how do you launch your Python program? If you launch it from Access then then you can refresh/requery your table as the Python program finishes. Just have to ensure that the program finishes before you update. (Use ShellWait rather than Shell to launch it.)

 

[bignose2]

Currently manually, that was next on my list.

Will be run from access & start the python program at start of day (not sure what process yet) & it will sit in the background, it picks up incoming phone call & updates the table.
phone is only going every few minutes at most, potentially 4 or 5 one after the other so will have to be aware of table updates & will look into your shell/shell wait comments, thanks.
Not sure what will happen if at the same time but will be just reading from the table in access so no write conflict but you have made me think.

As much as I said prefer not too I am leaning towards timer, e.g. every 0.5 seconds, simple form in the background doing just that one thing, to check if it has changed from previous value so hopefully not resource hungry, no refreshing of form, flickering etc. if no change in data.
Any changes will then update my main form.

 

[MarkK]

You can call back to the FE from a table macro if you wrap the function call in Eval(). Consider this screen shot..

So there's a 'Public Function CurrentUserID() As Long' in the FE, and this macro runs it, and sets the value of the field. This is a split Db, the table's in the BE.

One thing though is when this macro runs the record is not saved yet, so if you use this to requery the UI, start a timer and wait till the record is committed first.

 

[CJ_London]

0.5 seems a bit too frequent. What triggers the update? When the phone rings? If someone is calling how long would they let it ring before cancelling the call? If the phone is answered how long might a call last? I would have thought 5 or 10 seconds would be sufficient?

 

[DickyP]

The real question is how do you launch your Python program? If you launch it from Access then then you can refresh/requery your table as the Python program finishes. Just have to ensure that the program finishes before you update. (Use ShellWait rather than Shell to launch it.)

Follow on - just realised that ShellWait isn't a VBA function but one in the wsShell Object. Code that does the same in VBA attached.

 

[bignose2]

You can call back to the FE from a table macro if you wrap the function call in Eval(). Consider this screen shot..

View attachment 118257

So there's a 'Public Function CurrentUserID() As Long' in the FE, and this macro runs it, and sets the value of the field. This is a split Db, the table's in the BE.

One thing though is when this macro runs the record is not saved yet, so if you use this to requery the UI, start a timer and wait till the record is committed first.

That is ideal, thanks very much.
working well, just have to as you say work on the timing for the update but great that I don't have to use timer.

 

[bignose2]

That is ideal, thanks very much.
working well, just have to as you say work on the timing for the update but great that I don't have to use timer.

Bit premature, I was testing on changing the table manually within access. Thought I had also done via python but seems not. Python odbc objects as I guess coming across something a bit alien in the table. Will investigate further but does not seem to OK

Not as access issue of course.
pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] The function 'Eval' is not valid for expressions used in data macros. (-20324) (SQLExecDirectW)")

 

[bignose2]

The real question is how do you launch your Python program? If you launch it from Access then then you can refresh/requery your table as the Python program finishes. Just have to ensure that the program finishes before you update. (Use ShellWait rather than Shell to launch it.)

Python is run from access using shell & is left running all day.
When a call comes it, it updates the access table with just that one caller ID at that time & subsequent calls.
So the table is up to date but the form is not updated & I would not normally manually refresh.
If someone is near the PC or walking past, the phone rings, they can see who is calling without interaction.
Consequently this needs to be quite quick as often we will decide whether to answer or not so would have preferred instantly as if a push message but timer, I think really 1 or 2 seconds max. As this is on one controlling source PC, Not too slow as I already have a timer on another FE's that detect changes & this is another couple of seconds so potentially 5 or 6 seconds & might be too late before the AM takes over. This use to work v.well with my old tapi OCX.

 

[Edgar_]

Based on my experience, data macros do not respond to external changes, such as writing to a table through a Python script. The WebSocket approach was suggested because the Python script can trigger the updates via the WebSocket in real time and the browser control can react to those changes by running an event procedure, also in real time.

Think of it as a chat application where the Python script writes a "Hello" message and the browser control notifies you about the salutation. You choose what to do with it from the event procedure, in this case, you'd just refresh.

 

[bignose2]

OK thanks,

Think will look into the WebSocket route but a brief google has no info on how to do that in Access so will have to investigate.

Used web control before but not as a websocket.

Thanks again

 

[Edgar_]

WebSockets are servers that need to be deployed somewhere. You can write your own, or let ChatGPT do it for you. Since you're already using Python, you could potentially add the WebSocket feature to the server that is currently managing your calls. I write mine with NodeJS, but there is an easy route that I have written about in this forum: Firebase Real Time Database. When your browser is connected to it, any update done to the data is notified in real time and the browser control can react to it.

Implementation example:

Who keeps Access opened 2 ?

What exactly you mean with "..then pass in your 'pippo' application name in the connection string and use that to identify the relevant sessions..." ? Include "...;Application Name=Pippo;..." in your connection string in the Access application. The column MON$ATTACHMENT_NAME in the...

www.access-programmers.co.uk

 

[Galaxiom]

Is there anyway to get a form to refresh when a record is added to a table.

Just a correction to your terminology.

A Refresh of a form updates the values on changed records but does not show new records.
For that you need a Requery.

Incorrect terminology can lead to bewildering behaviour on this.

 

[tvanstiphout]

it picks up incoming phone call

What phone system are you using? If a business class phone system, I may have a possible solution.

 

[bignose2]

Hi,
It is business class'ish. NEC SL1100 but may be changing sooner rather than later so probably would not look to that.

Websocket does seem the way to go (worst case slower'ish timer)
I should be able to figure all the regular VBA programming, refresh, requery etc. its is more the VBA listening side of a webbrowser, websocket which is new to me & found very little on but guessing will not be complicated & once I have time to look sure so won't be a problem.

Python has lots of info so sure will get the server & sending sorted on that side.

It may be I have to move to a cloud based IP system soon anyway so all this may be fairly irrelevant, I was wondering how that will all work as they must have systems that use the equivalent TAPI to match calls to a database but the company trying to get me to move to IP were not sure how that would work so holding of until UK BT force the issue or the NEC breaks down & actually the NEC can still work on an IP system but being old think may be time to move to cloud then anyway. I digress!!

 

[Pat Hartman]

Is there anyway to get a form to refresh when a record is added to a table.

No. The record was not part of the recordset when the form opened. To see new items, you must requery and that will force Access to save the current record if it is dirty. Keep in mind that this repositions the form to a new record and if you are controlling this from outside the form, I'd be verrrrrrrrrrrry careful about disrupting an update operation in process. Much as I dislike timers, the websocket method seems very dangerous in this instance. The timer runs inside your form so it can tell if your form is dirty and you can avoid the requery until after the update is finished.

 

[Minty]

If the table where these inserts are occurring has a Autonumber ID, you could simply record the Max ID at time x (load open, no idea which event is suitable) and compare it to the current MaxID via a timer event on the form.

If it's greater than the last time you checked then you now know the data has changed in the background and can do something.
I think that's simple unless I've missed something.

 

[Edgar_]

All right, since you're using Python, I had DeepSeek write a WebSocket for me and it came up with this code:

Spoiler: Python code

from websocket_server import WebsocketServer

clients = []

def new_client(client, server):
    print(f"New client connected: {client}")
    clients.append(client)

def client_left(client, server):
    print(f"Client disconnected: {client}")
    clients.remove(client)

def message_received(client, server, message):
    print(f"Received message from client: {message}")
    # Broadcast the latest message to all connected clients
    for c in clients:
        server.send_message(c, message)

# Pass the host and port as tuple
server = WebsocketServer(port=9955, host='127.0.0.1')
server.set_fn_new_client(new_client)
server.set_fn_client_left(client_left)
server.set_fn_message_received(message_received)
print("WebSocket server is running on ws://localhost:9955")
try:
    server.run_forever()
except Exception as e:
    print(f"Error running server: {e}")

Now all you have to do is run it, I'm already attaching it to this post, though.

BUT the idea is to integrate it into MS Access. For that, the first thing you need to do is enable browser emulation by adding MSAccess.exe key with a value of 11001 in HKEY_CURRENT_USER\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION from the Registry Editor.

By the way, going back to the default version of the legacy browser control is just a matter of either removing or renaming that key above. Anyway, once that is set, you can download the attached files. It's just these files:

1. Extract the files
2. Run Start WebSocket.bat
3. Open Sample.accdb and Form1 inside
4. Open test_page.html with any browser
5. Write messages

If you set it up correctly, it should share messages in real time, like in the following video:

Expect issues, this is a proof of concept. For example, in a production environment, you would have to deploy it as a secure server, and that requires at least a self signed certificate. If others will use it, you would have to modify the WebSocket code to account for that and then deploy it to some URL that the others can access.

Remember that WebSockets are a mature type of technology, Chat services and other programs utilize it to communicate with you in real time and you can integrate them as easy as this.