topic Re: Macro to manipulate database in QlikView

Macro to manipulate database

Anonymous — Fri, 29 Sep 2017 10:17:20 GMT

Hi folks,

I'm trying to achieve an approval tool, goal is to manipulate tables in SQL Server. Users shall enter some IDs in input fields where will be used as variables and transferd to a macro.

My Macro is looking like this:

sub MakeApproval

'Declare Variables

Dim ApprovalStatusID

Dim Approver

Dim Comment

Dim FileID

Dim VersionID

Dim SQL

Dim Update

Dim ApporvalID

'Set Variables with Input from QlikView Variables

ApprovalStatusID = ActiveDocument.Variables("ApprovalStatus").GetContent().String

Approver = ActiveDocument.Variables("Approver").GetContent().String

Comment = ActiveDocument.Variables("Comment").GetContent().String

FileID = ActiveDocument.Variables("FileID").GetContent().String

VersionID = ActiveDocument.Variables("VersionID").GetContent().String

'Get newest ApprovalID

SQL = "SELECT MAX(ApprovalID) ApprovalID FROM Planung.tbl_Approval"

'Set Connection

set conn = createobject("adodb.connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

'Open Connection

conn.open "dsn=xxx;uid=xxx;password=xxx;"

'Make new Approval insert

objRecordSet.Open "INSERT INTO planung.tbl_Approval (ApprovalStatusID, Approver, Comment, AprovalDate) VALUES ("&ApprovalStatusID&",'"&Approver&"','"&Comment&"',getdate())",conn

'Get newest Approval ID

objRecordSet.Open SQL, conn

'Loop through Approval Recordset

Do While NOT objRecordSet.Eof

ApporvalID = objRecordSet("ApprovalID")

objRecordSet.MoveNext

Loop

'Update tbl_Plan_Budget to make connection to newest Approval

conn.Execute("UPDATE planung.tbl_Plan_Budget SET ApprovalID = '"&ApporvalID&"' WHERE FileID = '"&FileID&"' AND VersionID = '"&VersionID&"'")

'Close connection

conn.close

set conn = Nothing

end sub

This macro works very fine on the server, even if I use the IE there I'm able to manipulate the database via this macro so all settings should be in place. Unfortunatley this is not working on my client and I don't know why. I have registred a dns on my test client with same settings as on server, client is using a IE with IE plugin setting.

I know there are troubles with using Macros in web mode but why is this working on my server in web mode?

All ideas are appreciated.

Many thanks in advance.

Kind regards,

Sven

Re: Macro to manipulate database

marcus_sommer — Fri, 29 Sep 2017 11:12:59 GMT

Do any macros work by your client with the IE plugin? For example a simple msgbox and a bit extended just a simple export to a file to check if system-access is granted or not. The macro-execution needs to be allowed on the user-level and depending on the settings it might don't occur any message.

Also various settings within the IE could prevent the macro-execution, for example the protected mode, disabling of ActiveX and probably some more. Further the use of proxy-servers, firewalls, group policies and similar stuff might prevent a successful database-call.

I'm not absolutely sure which macros/actions will work dependently on the used clients and the settings within the document and the qmc but I think it runs within the webview because it's yet the desktop client with a emulated website as UI and not an AJAX client like the normal browsers.

- Marcus

Re: Macro to manipulate database

Anonymous — Fri, 29 Sep 2017 11:38:13 GMT

Good point, unfortunatley even a simple macro what shows a msgbox does not work on my client. Maybe you are right, I could imagine a network setting is disabling macro usage in browser. Is there a way to check if macros are running at all in my client browser?

Thanks

Re: Macro to manipulate database

marcus_sommer — Fri, 29 Sep 2017 11:57:18 GMT

Pressing Ctrl + Shift + M within the IE plugin produced the following window:

and it need to be set to the first option.

- Marcus

Re: Macro to manipulate database

Anonymous — Thu, 05 Oct 2017 07:09:10 GMT

Hi Marcus,

I have checked and the first option was already set. I will ask the IT Infrastrucutre team her if there are any settings what blocks makro executions in IE.

Any other ideas?

Many thanks.

Re: Macro to manipulate database

marcus_sommer — Thu, 05 Oct 2017 07:15:35 GMT

Beside the user-settings the most likely reasons will be things like the protected mode within the IE and/or disabling of ActiveX. Helpful could be to add the access point to the trusted sites of the IE.

- Marcus

Re: Macro to manipulate database

Anonymous — Tue, 24 Oct 2017 15:04:09 GMT

Issue solved, I found out I haven't installed the IE plugin on my test client. The preferred client "internet explorer plugin" under the favorites and profile tab on access point took me to the assumption I have already installed the plugin. After I have finally installed the plugin I could use my Macro in the desired manner, I'm able now to perform any SQL commands to the database. Only thing left is to consider which credentials I should use in the dsn, more a securtiy question for the database team I guess.