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?
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 !!!!
----------------------------------------------------------------------------------------------------------------------------------------------------------
Dim objCommand
Dim objField
Dim objRecordSet
Set objConnection = CreateObject("adodb.connection")
Set objCommand= CreateObject("adodb.command")
Set objRecordSet= CreateObject("ADODB.RECORDSET")
objCommand.ActiveConnection = objConnection
objRecordSet.Open objCommand
'msgbox objRecordSet.RecordCount
'balalal
msgbox objField.Name &"==="& objField.Value
next
objRecordSet.MoveNext
objRecordSet.Close
objConnection.close
Set objRecordSet = Nothing
Set objConnection = Nothing
end sub
Did you select the options 'System Access' in Tools > Edit Module > Requested Module Security?
Best SEO agency in mumbai
ReplyDeletebuy gamming glasses online
reviews on best radar detectors
Unblocked extratorrent proxies 2019
Best baby Headphones 2019