Macro to manipulate database

[Anonymous]

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

[marcus_sommer]

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

[Anonymous]

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

[marcus_sommer]

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

and it need to be set to the first option.

- Marcus

[Anonymous]

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.

[marcus_sommer]

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

[Anonymous · Accepted solution]

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.