Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

Tags (1)
1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

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

Did you select the options 'System Access' in Tools > Edit Module > Requested Module Security?

2 Replies
fosuzuki
Valued Contributor II

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

Did you select the options 'System Access' in Tools > Edit Module > Requested Module Security?

Not applicable

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

Thanks ,it works now.

Community Browser