PocketBase - how to interface with Access code ?

[amorosik]

PocketBase seem to me like the new Firebase system, a db in realtime with many advantage over classic client-server system
Has anyone managed to use it in conjunction with the Microsoft Access work environment?

 

[ebs17]

Approaches could be derived from the FAQ:

PocketBase uses embedded SQLite (in WAL mode) and there are no plans for supporting other databases.
...
Additionally, a great companion app when you need SQLite replicas is Litestream.

 

[amorosik]

I guess you are not aware that Access is not a database engine. Access is a RAD (Rapid Application Development) tool that creates a GUI that can access ANY RDBMS that supports ODBC. It works in a client/server environment. It will not run on a phone unless you use Citrix or RDP and run the Access app in a browser.

The RDBMS is NOT Access, it is Jet for .mdb files and ACE for .accdb files. And you can also use Jet or ACE in any environment that supports ODBC although when paired with Access, the interaction is integrated and does not require ODBC.

Much of the confusion is caused because Access is used as the GUI for Jet/ACE just as SSMS is the GUI for SQL Server. Also, Access the RAD tool, uses Jet or ACE to store it's FE objects and code. So the relationship is symbiotic. Access needs Jet/ACE to store its objects although NOT to store its data and Jet/ACE need Access to provide a GUI even though they have a fully functional DAO code interface.

Ehhh ???

What does all this talk have to do with the question?
When we talk about Firebase do you know what I mean?
It is a system, owned by Google, which allows you to store information like a classic db, but above all it has a mechanism to 'warn' the client that something has changed.
And this is an extremely more effective functioning of the classic polling as it is possible to do with any client / server system
It seems that PocketBase can realize the same functionality
I wanted to understand if any of the Access users of this community had already tried to use it in conjunction with PocketBase

 

[amorosik]

Approaches could be derived from the FAQ:

Thanks for the answer, but that's not what I asked for
What PocketBase uses to store information is not relevant
The important thing is whether the mechanism of the events generated by PocketBase can be exploited to 'warn' the clients (who have subscribed to these particular events) of the change of state of the db

 

[CJ_London]

So I think the answer is that no one here has any experience with using pocketbase so cannot answer your question.

The important thing is whether the mechanism of the events generated by PocketBase can be exploited to 'warn' the clients (who have subscribed to these particular events)

This is a different question and again, I don't know the answer. Access reacts to its own events, not external ones. To make that work would perhaps required a chain of events along the following lines

• pocketbase has an event and generates a 'marker' of some sort
• access polls pocketbase on a regular basis (every minute/hour/whatever) perhaps using REST to detect new markers
• on detecting a new marker it triggers code to do whatever is required (and presumably sends an acknowledgement to disable or delete the marker as well)

 

[amorosik]

So I think the answer is that no one here has any experience with using pocketbase so cannot answer your question.



This is a different question and again, I don't know the answer. Access reacts to its own events, not external ones. To make that work would perhaps required a chain of events along the following lines

• pocketbase has an event and generates a 'marker' of some sort
• access polls pocketbase on a regular basis (every minute/hour/whatever) perhaps using REST to detect new markers
• on detecting a new marker it triggers code to do whatever is required (and presumably sends an acknowledgement to disable or delete the marker as well)

I agree on complete line
But "..access polls pocketbase on a regular basis.." is the hell of every programmer
Polling is a really bad method compared to an event-driven management, it makes the system unresponsive with respect to external stimuli and unnecessarily consumes cpu resources
This is why I was hoping someone had already got their hands on PocketBase

 

[amorosik]

You asked how to use Firebase with Access. Since you described it a database, I gave you the answer in that context. If Firebase supports OBDC, you can link to it but "Access" the FE is still a client/server application and only runs in the cloud using applications that will run the Access App in a browser such as Citrix and Remote Desktop

"..You asked how to use Firebase with Access.."
No, i asked how to use PocketBase with Access

"..Since you described it a database.."
Firebase IS a database
If you disagree you can write to Google and ask to change the writing they are using

"..If Firebase supports OBDC, you can link to it but "Access" the FE is still a client/server application.."
In any client / server system it is the client who sends commands or queries the server, the server just responds
Can you explain to me how you would, via odbc, send commands from the db server to the client without the client having requested anything?
Because this is the functionality that Firebase and PocketBase have more than the classic db

"..and only runs in the cloud using applications that will run the Access App in a browser such as Citrix.."
And what does this have to do with the initial request?

 

[amorosik]

What's with the attack? Chill.

In the context of an Access forum, what exactly does that statement mean if you are not asking how to link to Pocketbase or Firebase? Also, since they are used with web apps, I pointed out that Access doesn't run in the cloud without help.

Again, I point out that this is an Access forum and Access in this context is a RAD tool It is NOT a database engine. Jet and ACE are database engines. It is Access, the RAD tool that uses ODBC to send commands to FireBase or PocketBase NOT the other way around. Therefore, you can use Access the RAD tool with Firebase and Pocketbase provided they support ODBC but you can't use Access on the web. ODBC connections to web databases are possible but painfully slow because ODBC is not optimized for this purpose. It works very well over a LAN but not so much over a WAN although Azure shows some promise.

So, an "Access work environment" implies you want to use Access, the RAD tool as the FE and the "Access work environment" is client/server, not web.

In these few lines, the word WEB appears four times and the word CLOUD once
Why?

You are assuming one thing, the use of Access via web/cloud, which I have never requested
What I asked, is how to use PocketBase with Access
It seems like a clear question to me
What do you say?

 

[amorosik]

So, you are intending to run Pocketbase on your LAN?

Yes, all in lan
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

 

[amorosik]

I don't know the mechanism that PocketBase actually uses to notify clients of events
But I am well acquainted with Google's Firebase environment for Android or iOs based applications
And on Firebase the client, in addition to performing the usual queries or writes, can subscribe to some "events" that could happen on the data
For example "every write on the Clients table", and when the event really happens then the subscribers are alerted with a notification
Events which the client can manage in the way it deems appropriate
Yes, to understand how it works, it is a kind of trigger + email sent from the server to the client subscribers of the event
But with practically instantaneous responsiveness

 

[CJ_London]

Polling is a really bad method compared to an event-driven management, it makes the system unresponsive with respect to external stimuli and unnecessarily consumes cpu resources

@MajP is our class module expert here. You can add events to a class module which might replace polling - suspect it all depends on what pocketbase does by way of notification.

 

[amorosik]

I'm not asking about the technical method. How does the USER know something got updated? Does someone tap him on the shoulder? Does he get an envelope in the mail? an email? a popup message? a phone call??

The HOW to show the user that data has been changed is the task of the program, of the code that you put in Access
For example, if user A is reading the information in the ARTICLES table, and user B is inserting a new record in the ARTICLES table, as soon as the storage on the db has been completed, the PocketBase system notifies all subscribers, including it is also the station of user A, and the code running on user A station, brings up a button of the type UPDATED DATA EXECUTE REFRESH
In this way user A is informed that the ARTICLES table has been modified and to view the current version he must press the indicated key

The crucial point is that the code running on user A station can subscribe to some interesting events
Then, WHAT the code will do (also IF the code will do), the programmer decides

 

[amorosik]

@MajP is our class module expert here. You can add events to a class module which might replace polling - suspect it all depends on what pocketbase does by way of notification.

Ok, then we hope MajP can see this 3d and can help us somehow

 

[amorosik]

Not sure how polling is relevant in this case. Although I did do it many years ago in a mainframe environment but we had a limited number of clients to interrogate. Are you talking about each user computer polling each table to see if there has been a change? Doesn't sound like a good plan.

It is not possible to achieve the performance of a Firebase type system using continuous database interrogation
We are talking about delays of a few milliseconds between the data change on db and the notification that arrives on the client
This thing is essential for example for a chat systemwith many client connected
But getting it via data polling would be out of the question

 

[amorosik]

If you want an example of a database server capable of handling events and communicating them to clients, you can take a look at Firebird
At page 7, a clear description
A tcp socket is open between client and server (in addition to the channel used by the client to send commands to the server), and used exclusively to allow the server to send to the client notifications of subsequent events

 

[Minty]

You are still looking at some sort of polling if the data isn't stored in an Access table.

You can run a Data Macro on an Access table that can fire on a Data change, this could notify a user.
As soon as you involve storage outside of Access you have to poll that external data to see the change.

There's no magic bullet I'm aware of that can get you away from that. Access has no TCP handling I'm aware of.

 

[MajP]

Ok, then we hope MajP can see this 3d and can help us somehow

Sorry, this is well beyond my skillset. I would know how to trap another office application's event through automation, but no idea how you would create the interface to pocketbase.

 

[CJ_London]

If you want an example of a database server capable of handling events and communicating them to clients

Sql server has events of that nature and ACE has data macros. Creating an event on data change/add/delete/whatever is not the issue. I would say it is common to most rdbms's. The issue is how that is communicated to the client.

The OP is interested in pocketbase, no one else is, so it is for the OP to determine how that event is communicated.

 

[Minty]

@Minty Do you KNOW if a data macro can send an email or PM? I thought they were very limited. I haven't used them because most of my BE's are SQL Server rather than ACE so I haven't had a reason to dig in.

I'm in a similar boat Pat, but assume that they can call some code to do something like an email or similar.
TBH any (vaguely instant) notification of data change is going to involve some form of timer event in an Access front end.

 

[amorosik]

Sql server has events of that nature and ACE has data macros. Creating an event on data change/add/delete/whatever is not the issue. I would say it is common to most rdbms's. The issue is how that is communicated to the client.

The OP is interested in pocketbase, no one else is, so it is for the OP to determine how that event is communicated.

"..The issue is how that is communicated to the client..."
Yessss, this is the point