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.

 

[Pat Hartman]

Has anyone managed to use it in conjunction with the Microsoft Access work environment?

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.

 

[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

 

[Pat Hartman]

What does all this talk have to do with the question?

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

 

[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?

 

[Pat Hartman]

What's with the attack? Chill.

Has anyone managed to use it in conjunction with the Microsoft Access work environment?

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.

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

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.

 

[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?

 

[Pat Hartman]

So, you are intending to run Pocketbase on your LAN? Go for it. Does it support ODBC? If so, Access can link to it.

 

[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

 

[Pat Hartman]

How does PocketBase "inform" the user?

Are you using Access as your FE now? What is the BE? You could probably do that with a trigger in SQL Server. Assuming the trigger could send email. Data Macros in ACE are very limited in what they can do and I doubt they can send email.

We can always be more helpful if you tell us what you actually want to do rather than asking us how to fix what you KNOW to be the solution. We still don't know enough to really help.

 

[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

 

[Pat Hartman]

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??

 

[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.

 

[Pat Hartman]

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.

 

[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