topic Macro save to access through server (AccessPoint) in QlikView

Macro save to access through server (AccessPoint)

kim_rormark — Mon, 05 Sep 2011 11:22:20 GMT

Hi all,

I have created a macro that saves some information to a access database. The macro itself works on the local system, but when i try to use it through the server - eg. AccessPoint (with AJAX or IE plug-in) it will still want to save to the access-db on my local machine, not on the server.

Is it possible to do this, or am I stuck?

Regards,

Kim Rørmark

Macro save to access through server (AccessPoint)

Miguel_Angel_Baeyens — Mon, 05 Sep 2011 12:01:08 GMT

Hi Kim,

It depends on how you have coded the folders (using UNC paths or network mapped drives), but you also have to take into account what does the macro do exactly (calling a VBS file to insert the info, calling a SQL INSERT or UPDATE directly).

Can you post some code so we can check it?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Macro save to access through server (AccessPoint)

kim_rormark — Mon, 05 Sep 2011 12:06:41 GMT

Hi,

This is one of the macros used:

strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=db\SLA-db.mdb"

Set conn = CreateObject("ADODB.Connection")
conn.Open strConnect

sql =_
"SELECT * FROM CustomerInfo where CustomerCode= '" & ActiveDocument.evaluate("ibvCustToChange") & "'"

Set RS = conn.Execute(sql)

So you what you see here is that the Data Source is in a folder "db" that resides in the same folder as the qvw.

My problem here is that it want to use a file on my local computer, not on the server (as I hope to get it to do).

Would a walkaround be to make a VBS-file do it for me? Won't it just look for the VBS-file on my local system instead, like it does with the access-file?

Macro save to access through server (AccessPoint)

Miguel_Angel_Baeyens — Mon, 05 Sep 2011 14:18:00 GMT

Kim,

Make sure you are granting system access to the macro. To do this, go to the Module Edit dialog (Ctrl + M) and on the right part set "System Access" and "Allow System Access".

Second, open the file locally on the server, and make sure the connection string works. Try creating a new OLE DB connection string in the server pointing to your MDB file. Paste the result in your document.

Third, make sure macros are allowed in the server, go to the QlikView Enterprise Management Console, System, Setup, Security and check both Allow Macro and Allow Unsafe Macro Execution.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Macro save to access through server (AccessPoint)

kim_rormark — Tue, 06 Sep 2011 07:17:02 GMT

Hi Michael,

The macro do have "System Access" and "Allow System Access".

I have also tried to open the file locally on the server, and when opened there everything seems just fine.

The macros are also allowed through the QV Enterprise Management Console, both Allow Macro and Allow Unsafe Macro.

The problem seems to be that when I open a QlikView-application, either through the AccessPoint (Ajax, IE-plugin) og through the QlikView Desktop (by connecting to the server) the macros are executed locally instead of on the server itself. Have you ever created a macro that read from/wrote to a file from the server, that worked?

Macro save to access through server (AccessPoint)

m_woolf — Tue, 06 Sep 2011 12:26:06 GMT

It doesn't look like your Data Source contains a complete path to the database.

Re: Macro save to access through server (AccessPoint)

kim_rormark — Tue, 06 Sep 2011 12:46:08 GMT

No, in that example it didn't. Thats because it was located in a folder called "db" that was in the same folder as the qvw. That is not the problem, I have tried complete paths also.

I have found a workaround (not that secure though), by mapping the database to a servername.

Eg. \\SERVER\db\SLA-db.mdb where the SERVER is the servername of the server that QlikView Server is installed on. And of course I shared that folder onto the network.

If someone has a better(good) solution for this, please shout it out!