PocketBase - how to interface with Access code ?

[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

 

[561414]

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

Node.js — Run JavaScript Everywhere

Node.js® is a free, open-source, cross-platform JavaScript runtime environment that lets developers create servers, web apps, command line tools and scripts.

nodejs.org

Download the latest version for your system. You will have to use javascript to use NodeJS.
After you have installed NodeJS, I suggest you get your hands on VS Code to work with NodeJS.
Then start a project in VS Code
Open a terminal, download a library to create a server, usually express
npm install express
Create the server with like 3 lines of code, one to import, another to instantiate express and another to listen to it
Then check how to communicate with access from there
Then check how to communicate with PocketBase from there
And done.
Again, that's if Access can react to external queries in real time. You can totally send data back and fort, but I don't know if it can react to that. It can read at specific times, like you've been told, but I don't know if it can react. That would be interesting to know.

 

[561414]

... Data is displayed and updated via forms, which BTW have the full range of VBA with which to send messages to other users via email and various other techniques, possibly through third party apps that support messaging. So, if personA makes a change that needs to trigger a message, the form can do that. In some cases, it might be possible to use a Data macro (ACE) or Trigger (other RDBMS) to do the notification...

@Pat Hartman It makes total sense, I was forgetting Access forms react natively to a lot of events.

So, in this case, if an external program were to make a change in some table, which event should be best to program for? BeforeUpdate, AfterUpdate, BeforeInsert, AfterInsert? Or perhaps a custom event? Maybe the external program sends a notification to the listener server, which in return issues something like "INSERT INTO tblDataUpdates ...", and the form should react to the change in the table?

EDIT:
Neither of the 4 events return anything when the insertion comes from the outside.

That's the core of this thread, I think. How do we make it react to external changes? (that is, things happening NOT through forms).

 

[amorosik]

Simple but slow road
Basically it is a question of inserting a function such as:
Public Function PocketBase_RX (byval command_received as string)
and invoking the function from an external program, for example using a vbs script would be like this:

PocketBaseHandler.vbs
--------------------------------------------------
Set MyMde = GetObject (mde_name)
Application MyMde.Run "PocketBase_RX", wscript.arguments (0)
Set MyMde = Nothing
--------------------------------------------------



Complex but fast road
In this case a communication channel is opened using a tcp socket, when any message arrive on the indicated port, is managed by the code inside the function wsTCP_OnDataArrival

 

[amorosik]

"...Then EVERY application MUST have a hidden form open with a Timer event that checks the "message" table/folder..."
NO - Maybe you still haven't figured out that this is what I want NOT TO DO

"..We still have no idea what process we are trying to model.."
I wrote it a first time and rewrote it for the second time
If I haven't made it clear then just let me know
If, on the other hand, I have explained myself, as it seems from the answers of the other users, then the problem of understanding is yours

If it were easier for you to understand a practical example, then just think of a chat like watsapp
Would you take a timer+db query to see the messages that come to you? Really?
Do you really want to tell me that you would launch a db query even if you don't know you have new messages?
It is obviously a useless waste of resources that the PC can use in another way
Unless we give the chat the name of 'turtle'
But... my procedure will be called 'rabbit', so i hope you will understand that i cannot use timers/polling, but other and more efficient methods

 

[amorosik]

You have provided what YOU think is the solution. We have no idea what the problem is.

I have never written what MY SOLUTION is
But I asked a question, as I currently don't have a solution
What I have written are the objectives to be achieved
Which is basically how to exploit the potential of PocketBase
And mainly the possibility for the server to notifying clients of the events that may arise from the database that the system is managing.

"..We have no idea what the problem is..."
WE who? Maybe YOU have not idea...

 

[amorosik]

The reason that watsapp works is because each side has a listener. Access is NOT listening unless you make it with a timer event. And we're up to 69 posts with no problem description.

@561414 please don't ask me any more questions. I'm not answering.

"..Access is NOT listening unless you make it with a timer event.."
NOT TRUE, my post second example is demonstration

"..And we're up to 69 posts with no problem description.."
But when you write WE, who exactly are you referring to?
I ask this because it seems to me that other users have understood it perfectly

"..@561414 please don't ask me any more questions. I'm not answering..."
It is enough that you do not answer
I write this because @ 561414 may not see this message

 

[Minty]

Your issue and I'm going to reiterate this, is that Access has NO NATIVE listening events that aren't internal in its own tables.
None. I think you have been told this at least three times, but seem insistent that we are all wrong.

You can run a timer event on a linked table, but it's a timer event, as I stated about 30 posts ago.
You seem to think you have a solution but can't make it work or seem unwilling to accept that it's the only way it will work.

I'm still confused by your desire to go down this route with a product that has a dozen competitors and none of which can do any more or less than any of the others. All of which nobody uses in the fashion you seem somewhat obsessed with, and solving a problem no one else sees as a problem!

 

[amorosik]

Your issue and I'm going to reiterate this, is that Access has NO NATIVE listening events that aren't internal in its own tables.
None. I think you have been told this at least three times, but seem insistent that we are all wrong.

You can run a timer event on a linked table, but it's a timer event, as I stated about 30 posts ago.
You seem to think you have a solution but can't make it work or seem unwilling to accept that it's the only way it will work.

I'm still confused by your desire to go down this route with a product that has a dozen competitors and none of which can do any more or less than any of the others. All of which nobody uses in the fashion you seem somewhat obsessed with, and solving a problem no one else sees as a problem!

What exactly do you mean by 'native access'?
If you look through the References you will see that the Vba is also a library added to the native development environment
If you need to communicate via socket you have to load MsWinsck.ocx
If you have to 'talk' to the web, WinHttpCom.dll is essential
I'm sure 100% of the users on this forum use additional libraries on EVERY program they make
So let me understand, why do you write about using native Access?
Native Access, without the use of external libraries, has no practical use

I do not have the solution and for this I am writing on this forum
I have indicated in the previous posts some communication modalities that could be used to reach the set goal

You write 'no one else sees as a problem', ok may be
I find this to be a problem, and I would like to try to fix it using Access
Is it better this way?

 

[ebs17]

and I would like to try to fix it using Access

You can do it. You understand your topic and have worked your way into it.
What are you waiting for? Should someone else let off steam in new territory that no one needs yet and therefore have practically no experience with, but which you suspect could work?

Using libraries and external solutions is not a problem. But what you should understand: "Access has NO NATIVE listening events".
You can delete a file in the file system from within Access. No problem, active action. However, if someone deletes a file, Access does not automatically find out about it, it would have to actively ask itself.

So when news comes from outside, it's somewhere on the shelf. To use them, they must be actively picked up. If you want to get Access to do something in real time, you have to control Access programmatically, i.e. apply a command to the Access file. Note: A message is not an order.

 

[amorosik]

You can do it. You understand your topic and have worked your way into it.
What are you waiting for? Should someone else let off steam in new territory that no one needs yet and therefore have practically no experience with, but which you suspect could work?

Using libraries and external solutions is not a problem. But what you should understand: "Access has NO NATIVE listening events".
You can delete a file in the file system from within Access. No problem, active action. However, if someone deletes a file, Access does not automatically find out about it, it would have to actively ask itself.

So when news comes from outside, it's somewhere on the shelf. To use them, they must be actively picked up. If you want to get Access to do something in real time, you have to control Access programmatically, i.e. apply a command to the Access file. Note: A message is not an order.

"..You can do it.....What are you waiting for? .."
If I knew for myself how to do it, I would have done it by now
If I ask in a forum of Access enthusiasts, it is clear that I currently do not know how to do it
The thing I hope is to find someone who has enough knowledge to complete the task, and who has the time and willingness to help on this post
And if you review previous posts, someone is already there

"..Using libraries and external solutions is not a problem.....Access has NO NATIVE listening events"
'Access native' does not exist in nature
What we all use on a daily basis is Access+libraries
And so if we assume that Access+libraries is the usable environment, then thinking about Access+msWinsck.ocx
We can say that Access can have events connected to the outside world

If we made a library dedicated to intercepting file/directory changes in the file system, we could also have that as an event manageable by the vba code
This is just to give an example

 

[KitaYama]

No offence but this thread is a joke.
The way the OP behaves, it's going in circles, not even a simple step forward after 74 posts.

 

[amorosik]

It's nice to see you have fun
What is the preferable number of posts within which to make progress?

 

[NauticalGent]

No offence but this thread is a joke.
The way the OP behaves, it's going in circles, not even a simple step forward after 74 posts.

Reminds me of another member we have dealt with recently.

 

[KitaYama]

Reminds me of another member we have dealt with recently.

Exactly.

 

[CJ_London]

Access on it's own can't do what you want, neither can excel, word or any of the other office apps

Using powershell you could check for new files in the windows notification folder,

powershell - check folder for new files

social.technet.microsoft.com

then use code to do something with access such as add a record

Is it possible to update a row in MS Access from PowerShell?

I have the following PowerShell code which adds a new line of data into a MS Access database (based on user input) and works perfectly. if ($NewAccounts ="Y") { $cursor = 3 $lock = 3 $Ado ...

stackoverflow.com

But that required pocketbase to be able to send windows notifications.

And before you ask, no, there is not a powershell library to add to Access.