PocketBase - how to interface with Access code ?

[ebs17]

I now understand the request something like this: For example, a new data record is created in the backend. Events take place that can trigger follow-up actions, but within the backend. Connected frontends are not aware of this. This is the usual case.

Now the point is that one of the follow-up actions is a message to the outside, to the server where the backend is located. This immediately sends this message to registered clients, so that these clients can react to it in real time.

About collaboration: An Access file is not a service (not a running program), but a simple dumb file. It does not react independently to information from outside, unless a timer is running internally, which is waiting for a message at a defined interface in order to be able to convert it into an action.

 

[amorosik]

@Minty

Not necessarily - if the nature of the app is for users to continually open/close forms or select new records you can use things like the form open, close and current events. If they are not happening then presumable the user is not using the app. But repeat, it does depend on the nature of the app.

@amorosik We do understand what you are asking but (I at least) do not understand what the facts are around

'autonomous communication from server to client'
'allows you to increase the effectiveness of the userinterface-database system as a whole'

What does this actually mean?
What sort of notification? a message? an image? a recordset?
How is a notification sent?
Where is it sent? client email? client subscription account? clients IP address?
How is it received by the user? - using windows notifications perhaps?
What is expected to happen when the message is received? update/add a record?

Finally, can you provide an example where one rdbms (not firebird/pocketbase) receives notifications from firebird/pocketbase, how it receives them and what does it do with them.

I think there is some confusion
When I talk about Firebase I mean this
When I write about Firebird I mean this

Both are database servers, although very different from each other
The second, Firebird, is a classic db server like MsSql, MySql, Oracle and similar, and has the classic odbc driver to access data
I gave the example of Firebird because it is one of the few db servers that provides natively a system for managing events, on the db server you set the events that can be 'seen' and then each client subscribes to one or more events among those available
For example, suppose that the events have been activated on the db server:
1- "insert on ARTICLES table"
2- "modify on ARTICLES table"
3- "delete on ARTICLES table"
At this point clientA could subscribe to event 1, while clientB could subscribe to event 1,2,3
If any software procedure present on clientB were to modify the ARTICLES table then the db server, once the transaction was completed, would send a notification to clientA (and also to clientB)
This is the basic operation, as I have already written above it is like a trigger on SqlServer that sends an email to clientA
Except that the mechanism of notifications is much more efficient in terms of resources and performance

When I write 'autonomous communication from server to client' I mean that the communication of the intercepted event STARTs from the server and is addressed to the clients who have subscribed to the specific event
This never happens on a classic client / server system, it is always the client that initiates communication
As for the example, you can see my post # 22 where I recommend reading a pdf, in which it is explained how the communication between db server and client takes place

 

[amorosik]

Most people don't have years to wait for a solution.
It is the ODBC driver that lets Access work with Pocketbase as the BE to get and put data.
It is the BE (Pocketbase, ACE, SQL Server, etc.) that has the functionality you are looking for to send notices to the clients. Even Access forms can send notifications AND they have all of VBA to use as well as windows API's so forms have the most flexibility of all but you won't even consider them as an option. Since you won't tell us what form you want those notifications to take, it is really hard to move on. Both ACE and SQL Server (and probably many other RDBMS options) support at least email notifications. But we don't know if that is sufficient for your requirement. Without knowing exactly what the requirement is, we cannot tell you what other RDBMS might solve the problem if you want to stick with Access as the FE.

You are posting in an Access forum, therefore, you are at least thinking about using Access (the RAD tool) as the FE. Access runs on Windows. If you don't want to run on a client/server platform, you need to look for a different FE than Access. I wasn't by any stretch of reality telling you that Access was the only correct solution. All along, I have been telling you that given your interest in only Pocketbase and not ACE and not SQL Server and not the functionality available with Access forms, Access is not going to work as a FE for you.

I'm sure there is but PocketBase and Access do not run in the same circles so you are probably going to have to make a choice. Are you committed to an Access FE? Are you committed to a Pocketbase BE?

I understand language can sometimes be an issue. Your English is better than that of a lot of native speakers so I didn't pick up on that.

A little advice to you.
1. if you have a language problem, tell us early and we will simplify our explanations so the translator has less trouble.
2. Always answer the questions asked of you. We were up to post #11 before I got you to tell me why you were trying to marry Access with Pocketbase. And we still have no idea HOW you want the notifications to happen. NOT the technical method as I stated earlier.
3. Now we're at post #40 and no where closer to helping you because you are not answering questions.

"..It is the ODBC driver that lets Access work with Pocketbase as the BE to get and put data.."
No, this is completly wrong
And I don't understand why you persist in thinking that without odbc you cannot read or write data on a db server
You seem to want to deny something that is obviously true

"..You are posting in an Access forum, therefore, you are at least thinking about using Access (the RAD tool) as the FE.."
Yes of course, if I had wanted to interface Erlang with PocketBase I would have written on a forum dedicated to Erlang
And again yes, of course, my intention is to use Access as a front-end but connected to data that is ALSO present on PocketBase
I honestly don't understand what's hard to understand

"..I'm sure there is but PocketBase and Access do not run in the same circles.."
Yes sure, actually Access and PocketBase run on different circle
But the question was asked to 'break the circle' and broaden our horizons
We were born explorers, not growers of our own backyard

"..A little advice to you..."
Thank you very much, I will consider

"...We were up to post #11 before I got you to tell me why you were trying to marry Access with Pocketbase..."
My answer is post # 13, maybe you haven't read it
"The function that interests me to understand if it can be exploited is the possibility for PocketBase to 'inform' the clients that something has changed on the db It would allow you to have an 'event' handling of some database changes, WITHOUT using polling"

".. And we still have no idea HOW you want the notifications to happen.."
As to HOW it should happen, I have no idea, any system that is efficient and fast is fine
If you need to IMPOSE a notification method then let's say a tcp socket message would be fine
I think it's one of the fastest ways to communicate between two applications

 

[amorosik]

I now understand the request something like this: For example, a new data record is created in the backend. Events take place that can trigger follow-up actions, but within the backend. Connected frontends are not aware of this. This is the usual case.

Now the point is that one of the follow-up actions is a message to the outside, to the server where the backend is located. This immediately sends this message to registered clients, so that these clients can react to it in real time.

About collaboration: An Access file is not a service (not a running program), but a simple dumb file. It does not react independently to information from outside, unless a timer is running internally, which is waiting for a message at a defined interface in order to be able to convert it into an action.

"..This immediately sends this message to registered clients, so that these clients can react to it in real time..."
Yessss, this is the central point

"..does not react independently to information from outside, unless a timer is running internally,.."
No no, this is like a 'polling' only via timer
What I would like to obtain is an event reading system (on a client station) that allows the application to react in the way that the programmer deems appropriate
For example, a communication channel via socket tcp intended to 'receive' the notifications sent by the db server

 

[amorosik]

I understand that in this discussion, the real problem to overcome is the habit that Access programmers have of 'thinking' everything like the classic client / server system.
But just get out of the classic office applications for a moment, for example just think of the ubiquitous whatsapp to understand that an event management is the ONLY WAY to have a great reactivity and a minimum use of resources.
For those who work on computer hardware, they know very well the 'interrupt' mechanism, when a communication is active, for example on the rs232 port, the cpu is not cycling waiting to see the characters enter from the serial port, but when a character arrives it is activated a signal that 'informs' the CPU of the work to be done
The same mechanism of the news is present on applications such as whatsapp and in all chats in the world
The same mechanism is used by Firebase (Google) to inform clients that something has changed and therefore, if they want, they can perform actions Firebase is a very effective system, very fast, but owned by Google and for a fee

Now there is PocketBase which, according to what I see written, seems to offer similar features
But it is open-source, can be installed locally, and does not pay a penny
What reason not to try to exploit it?

 

[ebs17]

What reason not to try to exploit it?

What answer did you get from the developers of PocketBase?

As I said, Access is not a service, but a simple file and not intended to wait for messages from outside and to react to them independently. It is now 30 years old and has remained at this level in the sense mentioned.

Side thought: Usual database work is not only the insertion of a record, but mass data processing. 17,000 records instead of one record, that's news fireworks.
I think you need to look for a different development environment for your client frontend. There are also more modern ones.

 

[amorosik]

What answer did you get from the developers of PocketBase?

As I said, Access is not a service, but a simple file and not intended to wait for messages from outside and to react to them independently. It is now 30 years old and has remained at this level in the sense mentioned.

Side thought: Usual database work is not only the insertion of a record, but mass data processing. 17,000 records instead of one record, that's news fireworks.
I think you need to look for a different development environment for your client frontend. There are also more modern ones.

There are several ways to receive communications via the rs232 port
And also by activating a tcp server it is possible to receive communications
They are all communications example that come from outside, and that Access is able to manage even without polling
I understand that it was not born to do this, but the fact of being able to extend the range of commands and features available using external libraries also makes it 'able' to perform these operations as well

 

[ebs17]

You shouldn't let your beliefs take you away. Carry out the plan you describe. Much luck.

 

[CJ_London]

Would seem you asked a similar question 2 1/2 years ago of firebase which you say here has the notification functionality you want to make use of

How to connect Access to Firebase Realtime Db ?

I have a procedure created with Access 2013 which must read / write on a Firebase database Is there any example of code that I can use as a basis for development?

www.access-programmers.co.uk

Appreciate that thread was about reading/writing to firebase - were you successful in getting that to work? And have you explored using notifications from firebase?

 

[amorosik]

Would seem you asked a similar question 2 1/2 years ago of firebase which you say here has the notification functionality you want to make use of

How to connect Access to Firebase Realtime Db ?

I have a procedure created with Access 2013 which must read / write on a Firebase database Is there any example of code that I can use as a basis for development?

www.access-programmers.co.uk

Appreciate that thread was about reading/writing to firebase - were you successful in getting that to work? And have you explored using notifications from firebase?

I tried to follow the information provided but I was unable to obtain the reading of the events from server to client
Which is the peculiar characteristic of this type of system

 

[CJ_London]

What I was asking was whether you were able to connect to the data in some way from Access

 

[amorosik]

Sure, you can read and write, but always at the command of the client (vba code if you are using Access)
And this is useless, because we already do it with the classic db servers
What I would like to see is the entry of the notification 'modified database - modified Articles table ' without the client doing anything

 

[CJ_London]

Clearly you don't want to be specific, so I'm going to drop out. Good luck with your search

 

[amorosik]

Clearly you don't want to be specific, so I'm going to drop out. Good luck with your search

Thank you for the good wishes
But I don't understand when you write ".. Clearly you don't want to be specific .."
You seem to have fully answered what you asked
Or I didn't understand exactly what you asked for

 

[Pat Hartman]

Given your expert level expertise with Access and infinite knowledge of how it interfaces with relational databases I don't know why you even had to ask for help. Good luck. I'm out too.

 

[amorosik]

Given your expert level expertise with Access and infinite knowledge of how it interfaces with relational databases I don't know why you even had to ask for help. Good luck. I'm out too.

You wrote something that doesn't make sense
If my knowledge was infinite, it would not have been necessary to ask the question in this forum, don't you think?
I'm sorry that not being able to answer a question irritates you
But perhaps the question posed has no solution at all
Or there is a solution, but the forum participants do not know it

 

[ebs17]

You didn't understand the difference between file and service, at least there was no feedback despite the repetition.

The more I think about it, I'm convinced that Access never ingests and processes messages from outside in real time. What I could imagine as a principle is that a service is installed on the client that can play this receiver and in turn triggers reactions in the Access front end. This, in turn, is not an Access topic and is therefore new territory for usual Access, which you have to tread yourself. As you have noticed, there is obviously no experience with your wish.

 

[561414]

Late to the toxic party, folks.

Dear amorosik, I'm interested in knowing why you want Access to react to a change in PocketBase.

In Firebase, as you know, you use the SDK kit to create the connection that a change in the data has happened. I just read some of the PocketBase docs, and it says it also has an SDK for NodeJS, so I guess you can build a NodeJS application to receive PocketBase notifications of data change. Your NodeJS application could, using that notification event, execute a SQL statement where the Access Front end will (perhaps) react to in real time.

In short: User is using the Access Front end -> Someone or something modifies the PocketBase back end -> PocketBase notifies the NodeJS app -> The NodeJS app sends a query to the Access Front end -> Access reacts to it and notifies your User.

That's if Access can react to it, which I do not know for sure. Pat says it needs ODBC for that. Well, doing a little search here:

odbc - npm search

www.npmjs.com

You can see that there are packages that claim to interface with it, so I would suggest you run your tests for that. Still, I'm not sure why you want Access to do this, would you clarify? I do these sorts of connections for fun, so it would be interesting to try.

 

[amorosik]

You didn't understand the difference between file and service, at least there was no feedback despite the repetition.

The more I think about it, I'm convinced that Access never ingests and processes messages from outside in real time. What I could imagine as a principle is that a service is installed on the client that can play this receiver and in turn triggers reactions in the Access front end. This, in turn, is not an Access topic and is therefore new territory for usual Access, which you have to tread yourself. As you have noticed, there is obviously no experience with your wish.

It is not essential to have a 'service' type program to receive characters from an rs232 port
It is not essential to have a 'service' type program to receive communication on an open tcp socket
And both the examples described above can also be obtained with a classic Access program
So why do you write that "..Access never ingests and processes messages from outside in real time .."?
Similarly, if the two examples above can be created by Access program, why do you write that we are not 'in topic' ?

 

[amorosik]

Late to the toxic party, folks.

Dear amorosik, I'm interested in knowing why you want Access to react to a change in PocketBase.

In Firebase, as you know, you use the SDK kit to create the connection that a change in the data has happened. I just read some of the PocketBase docs, and it says it also has an SDK for NodeJS, so I guess you can build a NodeJS application to receive PocketBase notifications of data change. Your NodeJS application could, using that notification event, execute a SQL statement where the Access Front end will (perhaps) react to in real time.

In short: User is using the Access Front end -> Someone or something modifies the PocketBase back end -> PocketBase notifies the NodeJS app -> The NodeJS app sends a query to the Access Front end -> Access reacts to it and notifies your User.

That's if Access can react to it, which I do not know for sure. Pat says it needs ODBC for that. Well, doing a little search here:

odbc - npm search

www.npmjs.com

You can see that there are packages that claim to interface with it, so I would suggest you run your tests for that. Still, I'm not sure why you want Access to do this, would you clarify? I do these sorts of connections for fun, so it would be interesting to try.

Yes, that's what could be done If PocketBase 'speaks' currently only via NodeJs
Then with that environment it is necessary to realize the 'receiver' of notifications
This system will then have to pass the information to the code present in the Access procedure
Except, not knowing where to get my hands on NodeJs, I don't know where to start