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

Tue, 16 Oct 2012 01:02:17 GMT

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

objRecordSet.MoveNext

Loop

objRecordSet.Close

objConnection.close

Set objCommand = Nothing

Set objRecordSet = Nothing

Set objConnection = Nothing

end sub

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

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

fosuzuki — Tue, 16 Oct 2012 03:11:41 GMT

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

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

Tue, 16 Oct 2012 04:53:17 GMT

Thanks ,it works now.

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

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

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

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