PocketBase - how to interface with Access code ?

[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 JavaScript runtime built on Chrome's V8 JavaScript engine.

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.

 

[Pat Hartman]

Pat says it needs ODBC for that. Well, doing a little search here:

@561414 ODBC drivers are how Access connects directly to any RDBMS that supports ODBC. The tables are linked. 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. But we have no idea what the actual objective is. The OP cannot seem to describe what he actually wants. All he does is argue about what he thinks is the solution to some mysterious objective which shall forever remain undefined when we try to explain to him how Access works with data. He is now arguing with you AND we're now up to post 62 without even a problem definition.

 

[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

 

[Pat Hartman]

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?

That isn't the way it would work. Form events run only when some action triggers the event. For example, the form saving a record (whatever prompts the save - either the user or Access) triggers the Before and then After update events. The AfterUpdate event only runs if the BeforeUpdate event is not cancelled (again, either by Access or the event code you write for validation).

If you want to use the form level event to trigger some action that is "seen" by other users, you can.
If you want an open application to "see" something done by other applications, you need to put the "message" in a table or a file. Then EVERY application MUST have a hidden form open with a Timer event that checks the "message" table/folder for a new message. The timer event isn't instantaneous and it sucks cpu cycles because Access has to keep interrupting other running processes to check for a "message". Essentially, you have every open app "polling" some shared resource. And then we have no idea what the process would be if Access isn't open at the time the message gets posted.

You can automate Access from outside by using OLE but that would be downright silly in this context - PLUS due to the way Windows works, you can't automate Access running on your neighbor's computer.

We still have no idea what process we are trying to model. The OP is just jerking us around. He's showing off and wants someone to argue with. You are wasting your time. He has no interest in understanding how Access might solve the mystery problem.

 

[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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

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.

 

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

 

[amorosik]

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.

Access without external libraries is not usable by anyone
Access with external libraries, allows you to have a listener
Do you agree on this or not?

I ask this because if we continue to discuss 'native Access' (which does not exist) we do not take a step forward
And then comes KitaYama who tells us that we go in circles