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

            'balalal

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

        next

        objRecordSet.MoveNext

       

       

      Loop

      objRecordSet.Close

      objConnection.close

       

       

      Set objCommand = Nothing

      Set objRecordSet = Nothing

      Set objConnection = Nothing

      end sub

      --------------------------------------------------------------------------------------------------------------------------