How to connect MS Access database to server project

CloverDX Platform
1

Hello,

I converted my local project which uses ms access as source database connection(mdb file) to server project. How should be the jdbc path written, so the server can find the access database in data-in folder of the project?

2

Hi Peter,

It seems that the JDBC driver used for accessing MS Access needs an absolute path (means ${DATAIN_DIR} won’t work as it is a relative path). You can still use a Clover parameter, but it has to contain an absolute path to your database file (the reason being that file access in this case is not managed by Clover, but the JDBC driver directly).

3

Hello,

Let me, please, add some information to this matter.

The easiest way to achieve the above described approach is to use the toAbsolutePath function in the definition of a dynamic parameter which can be then pasted into the JDBC URL for MS Access.

To utilize this function in the CloverDX Designer, please, do as follows:

  1. Create a parameter and in the […] menu, click on the Convert to dynamic
    439×208 19 KB
  2. The Value definition window will open. To define the Absolute path to the .mdb file from the data-in folder we insert one line of CTL code to prepared function.
    function string getValue() {
    return toAbsolutePath(getParamValue(“DATAIN_DIR”));
    }
    First resolve the data-in folder URL inside the project, for that use getParamValue, because the URL is in the default parameter called DATAIN_DIR.Then wrap that piece of code into the toAbsolutePath function as mentioned earlier.
  3. Now we can use the ABS_PATH_DATAIN parameter in the JDBC URL.

And all should be good to go.

Note:
Validate connection button in the CloverDX Server environment will fail because the dynamic parameter will validate locally. In runtime the parameter will work properly.

4

Thanks a lot. Problem with this is, I cannot generate metadata from designer. So after I found out the absolute server path, I used this hardcoded. Next problem I get is, the designer tries to pass admin as username even I specify no username. I will try with empty string as parameter and see if it works. Or do you perhaps have some other idea about the username? When using local mdb file, no problem with username.

5

Hi Peter,

I tried to replicate your situation and I was unsuccessful. I created the MS Access connection to the password-protected .mdb file. If the password is correct, everything works, despite the Username field being empty or filled with a random string.

So, to find out more about this issue, could you please provide some information about the situation?

  1. Execution Log from the graph, mainly the part with the connector.
  2. Screenshot of the connection, and how it is created.
  3. Screenshot of the graph in Designer so we know what components you are using and can try to replicate it.

Ideally, you could provide that information for both CloverDX Server and CloverDX Designer environments. So, we could figure out, what are the differences in execution on both sides.

Ideally, run your graph both within a local and a server project and provide the above-listed pieces of information for both. So, we could figure out, what the differences in executions on both sides are.

Thank you in advance, Tom.

6

I should have mentioned that I do not have password protected mdb file. Username and password are empty.

7

Hi Peter,

this scenario was also tested and worked properly in our environment. Moreover, it shouldn’t have any effect on the fact that you are experiencing different behavior on a Local project and a CloverDX Server project. In that case, could you, please, provide the information requested above, so we can look closely at the issue?

Best regards, Tom.