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

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

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



Comments

Post a Comment