2 Replies Latest reply: Oct 16, 2012 12:53 AM by Michael MA RSS

    How to get data from database using macro and present in table box?

      Hi all, 

      Recently i met a requirement and need to get data dynamically from database. Generally speaking we defined 2 list box , one for choosing the table name , the other one for selecting the column name need. Then we created a button label as 'Get Data' . When the button clicked , a table box would present the data dynamically.  That's kind of like the 'select  COL_NAME1,COL_NAME2 ...FROM TABL_NAME' , personally i think. Due to the forbidden for user to reload when published, i 'm considering to write a macro to pull data from database using dynamic sql. Now I wrote a section of vbscript and it works when testing in windows debuger . But it has problem when moving to QLikview. Could you please help to see what 's the problem?  Many Thanks !!!!



      sub selectfromdb

      Dim objCommand

      Dim objField

      Dim objRecordSet

      Set objConnection = CreateObject("adodb.connection")

      Set objCommand= CreateObject("adodb.command")

      Set objRecordSet= CreateObject("ADODB.RECORDSET")



      objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\M\book\Qclikview\qv\qv\Developer II\Chapter05\ExtraCredit_Chapter05\Datasources\QWT.mdb;Mode=Share Deny None"



      objCommand.CommandText = "SELECT  ShipperID, CompanyName as Shippers FROM Shippers"

      objCommand.ActiveConnection = objConnection



      objRecordSet.CursorLocation = 3



      'Set objRecordSet=objCommand.Execute

      objRecordSet.Open objCommand



      'Set intArrayLength=objRecordSet.RecordCount

      'msgbox objRecordSet.RecordCount



      Do until objRecordSet.EOF



        for each objField in objRecordSet.Fields


            msgbox objField.Name &"==="& objField.Value










      Set objCommand = Nothing

      Set objRecordSet = Nothing

      Set objConnection = Nothing

      end sub