how to make a connection with an Access Database

Hi,

I want to transfert data with the Trigger "save after Document" to an Access DB ?

It's possible to make a connection in EJScript to an Access DB to another Server ?

If yes, could you me help to do this ?

Thanks

RE: how to make a connection with an Access Database

Hi Fabrice,

depending on your solution, this can be done in different ways:

- Cloud solution: Use HTTP Request to connect to an endpoint to transfer/fetch the data you want. It requires that you have a solution set up on the other side that we connect to.

- OnPrem Solution: You can either use HTTP Request as explained above or DBI to connect to an external database

This also depends on the scenario: What kind of data, how often, how much, etc.

Av: Simen Mostuen Iversen 12. mar 2020

RE: how to make a connection with an Access Database

Hi Simen,

I need to transfert Data to an defined Document Type.

I want to transfert Amount, Kundenr, Kundename... so 10 variable to an Access DB when an Document from Type "Order" (in my firm it's so).

I search any idea how i can transfert von SuperOffice to an Access DB.

I hope it's more detailled.

Thanks

 

Av: Fabrice Reithinger 12. mar 2020

RE: how to make a connection with an Access Database

Hi Simen,

I need to transfert Data to an defined Document Type.

I want to transfert Amount, Kundenr, Kundename... so 10 variable to an Access DB when an Document from Type "Order" (in my firm it's so).

I search any idea how i can transfert von SuperOffice to an Access DB.

I hope it's more detailled.

Thanks

 

Av: Fabrice Reithinger 12. mar 2020

RE: how to make a connection with an Access Database

This isn't something we can help you with here, this is probably something your local subsidiary should help with.

It's complex and requires:

1. Someone with scripting / coding skills

2. An Endpoint that SuperOffice can connect to to transfer data.

Av: Simen Mostuen Iversen 12. mar 2020

RE: how to make a connection with an Access Database

Ok...

I think the script could be interesting for other developpers which make a connection to an other Database..

I need only a little script how to do that..

Av: Fabrice Reithinger 12. mar 2020

RE: how to make a connection with an Access Database

Hi Fabrice,

I agree with Simen with his recommended solution with an HTTP EndPoint.

As far as I know, the main problem is that CRMScript don't have any support for accessing ODBC-EndPoints as, for example, the .NET framework have. That makes it technically impossible to access an Access-database from CRMScript.

SO Onsite - Call an exe-file
In the onsite version of SuperOffice, it's possible to call an exe-file with parameters, which might be a workaround. It could be a small .net application or powershell script that updates the access database with the added data.

But this kind of architecture involves so many other complexities and potential problems, so that is nothing that I would ever recommend anybody to use.

What complexities and problems, you might think then?

Well, I believe that an exe-call is executed on the SuperOffice server with the privileges of the application pool that the service script app folder is using. You don't want that app pool to have more access than it needs to execute the Service parts. More access to internal systems or folders leads to a potential security risk. I also believe that this architecture increases the risk for hanging processes, etc.

HTTP EndPoint
Calling an external HTTP EndPoint is a universal solution that leads to a more flexible architecture. This kind of solution could be used both in the onsite as well as online version of SuperOffice. So are you using an onsite version now, you don't need to rebuild the whole solution even if you were to migrate to the online version.

If these calls are made in a SuperOffice event (Trigger), like for example AfterSaveSale, you want the event to execute as fast as possible, so the user in SuperOffice doesn't need to wait several seconds for the GUI to give back control to the user.

For calls that may take some time, this is therefore also a good architecture. This because the EndPoint could return an accept for the call almost instantly, so SuperOffice can continue. The HTTP EndPoint solution could use a structure where it put all calls on a queue which it works through and executes in order. This solution would be responsible for adding the data to the access database.

The HTTP EndPoint could be built like a Windows Service that is run using a specific user that has the access needed to be able to access the access database. It could also be a website where the access to the access database is executed using a specific user with only that access. So you don't need to mix the privileges for the app pool running the website.

This is exactly the method SuperOffice is using for accessing the Document-folder (SOARC) when using the Web Client. We are using a so called Impersonate User, which has the read and write access to the SOARC-folder which is used for specific tasks, so the app pool user running the SuperOffice Website don't need write access, as that would mean a potential security risk.

This way you separate the privileges needed for the different parts of the complete solution. It is also much easier to build such solutions using the .NET Framework and it gives a cleaner and more flexible architecture.

As the main part of the solution isn't solved using SuperOffice specific knowledge, but rather knowledge in .NET or other technologies possible to build HTTP EndPoints in and access access databases, there is unfortunatley no "little script how to do that".

I believe the above was the reason Simen indicated that you probably would need a developer and that is isn't enought with only CRMScript-skills to solve the problem.

 

Av: Marcus Svenningsson 16. mar 2020

RE: how to make a connection with an Access Database

Hi Marcus,

thanks for your response.

Now i understood what Simon say.

I will look for a developper to explain my problem so that it can give me the code.

Thanks all 

Av: Fabrice Reithinger 16. mar 2020

RE: how to make a connection with an Access Database

ONLINE ONLY: You could use the Zapier integration to connect to your end-point instead of using CRMScript if you want to avoid programming. Using Zapier you can add the data directly to a spreadsheet in Google or Office 365 for example.

Av: Christian Mogensen 16. mar 2020