6 Replies Latest reply: Oct 24, 2017 11:04 AM by Sven Stachelhaus RSS

    Macro to manipulate database

    Sven Stachelhaus

      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

          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
            Sven Stachelhaus

            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.