PocketBase - how to interface with Access code ?

[amorosik]

Back in #13, I got this out of the OP

It would seem that the only interest in Pocketbase is because it has some mysterious feature that the OP thinks he needs.

The functionality is not at all mysterious
That's what any mqtt server (or broker as everyone calls it) do, and even some db server do
The fact that this feature is not present among the classic db server systems does not make it less useful
Finally, I would ask you to avoid silly sentences like "..feature that the OP thinks he needs .." add no useful information in this discussion

 

[amorosik]

Does ANYONE even know if Pocketbase supports ODBC????

I don't think the OP has any pressing need to use Pocketbase or Firebase. I think he wants whatever he uses to somehow "notify" the user of some event.

We don't know what the FE will be, we don't know the purpose of the notification or even if it has to be real time. We know virtually nothing about the actual requirements.

"..I don't think the OP has any pressing need to use Pocketbase or Firebase.."
Second time

 

[amorosik]

As we have all told you, ACE has data macros and SQL Server (and pretty much all other RDBMS) has triggers. Either should be able to "notify" the user. What you have never told us us what EXACTLY you want for notification or what should cause it or does it need to be real time.

You are obsessed with Pocketbase and haven't even determined if it supports ODBC because if it doesn't, you're posting in the wrong forum because you won't be using an Access FE.

"..You are obsessed with Pocketbase and.."
Third time

 

[Minty]

It appears to only support various SDK's as a web-based interface for the data, or supports a REST API and is an embedded SQLite backend. So my take is that it won't support natively an ODBC style connection.

So unless someone builds an ODBC connector Access is out.
It's designed primarily for web clients.

 

[amorosik]

Dear Pat Hartman, it may be that I have expressed myself badly
English is not my native language
And therefore in order to avoid my problems of correct communication, I re-propose the initial question:
------------------------------------------------
Does anyone in the forum know how to use Microsoft Access, I'm talking about front-end only, in combination with PocketBase for data storage? I ask this thing because PocketBase, like the very famous Firebase from Google, seems to allow you to have a notification system from the database to the clients who have subscribed to the desired events, and being OpenSource it can also be installed locally, obtaining a completely system ' in-house '
------------------------------------------------
This was the original question, and it still is

That said, I would encourage you to use less offensive terms when writing about other people's business

The fact that there is CURRENTLY no odbc driver available does not mean that it is impossible to communicate between Access code and PocketBase
It just means that there is no odbc driver available FOR NOW
Even if it were available, it would not allow for autonomous communication from server to client
If it is easier for you to think of a db server that has an odbc driver then let's talk about Firebird
It also has an 'event' management that allows you to increase the effectiveness of the userinterface-database system as a whole
And therefore on all posts try to replace PocketBase with Firebird
The substance does not change, via odbc each client cannot '' subscribe '' to the events that the db server can be programmed to intercept
This is to say that the presence of the odbc driver does not change the substance of the matter

Lastly, a piece of advice, try to broaden your horizons, because from what you write it seems that leaving the client-server confort-zone is a mortal sin to be avoided at any cost
On the contrary, there is a whole world out there (and soon there will also be Mars), just waiting for us to build new systems, new projects, solve new problems, with increasingly effective tools
One of which, i think, is PocketBase

Peace and smile (and love)

 

[CJ_London]

@Minty

notification of data change is going to involve some form of timer event in an Access front end.

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.

 

[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