Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Does anyone as and exemple of a macro updating a Database using ODBC ..
I saw some discussions about it , but I was not able to find a definite answer about it.
Thanks
Philippe
Hi,
We use to have a button, calling a macro function with the following code:
function ABC()
Dim objShell, WshShell
set objShell = CreateObject("WScript.Shell")
objShell.SendKeys "^+r"
end function
By clicking on the button, it will rerun your "Load" script, causing a reload to run to update your .qvw file.
But do note that this run on the Enterprise Version and will not work in the Server. In Server, you have to use the publisher to reload.
Above is accurate based only on our own finding, please let me know if it is not true.
Thanks in advance!
Regards,
Say Chao
Hi,
We use to have a button, calling a macro function with the following code:
function ABC()
Dim objShell, WshShell
set objShell = CreateObject("WScript.Shell")
objShell.SendKeys "^+r"
end function
By clicking on the button, it will rerun your "Load" script, causing a reload to run to update your .qvw file.
But do note that this run on the Enterprise Version and will not work in the Server. In Server, you have to use the publisher to reload.
Above is accurate based only on our own finding, please let me know if it is not true.
Thanks in advance!
Regards,
Say Chao
If you are trying to update a database, you need to understand VBScript in relation to ODBC connections (sorry, I don't). You need an ODBC connection configured, then can use that connection in your macro script using...
set conn = createobject("adodb.connection")
conn.open "dsn=mydsn;uid=myuser;password=mypassword;" // use correct values
the rest depends on what you want to do next. When you have written your code you can link it to a button.
Thanks a lot ! That works.
Philippe
Hey Philippe and others,
I was wondering how does it work. We want to export the snapshot to an external database (Access or SQL Server) from QlikView but have no clue how to go about it. Please give some hints.
Thanks in advance
Hey,
Basically, you have to write a macro (VB or Javascript) to do it.
You can use ODBC or OLEDB connection to your database.
You can find information of the connection by going on google and type ADODB (it is a kind of universal connector.
Using it you will just have to create your SQL sentence the same way you are doing it in other applications.
I could provide you with some example if you need.
Philippe
Hey,
Thanks for the response. Can you please provide a simple example of exporting a single list box to any database (i.e. Acess). I am able to do it from Excell to Access but not directly from QlikTech to Access.
Thanks a lot.
Kashif
Here is a sample of an update ...
Philippe
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
sub Connection
set conn=CreateObject("ADODB.Connection")
conn.Open "YOUR ODBC CONNECTION"
set rs=CreateObject("ADODB.recordset")
risque = getVariable("Change")
set inventory = ActiveDocument.Fields("codeinv").GetPossibleValues 'This field is inside a list box
for i = 0 to inventory.Count-1 ' To take all the values into account
'
' Update of the inventory table
'
sql="UPDATE invd3 SET "
sql=sql & "risqueqte='" & risque & "'"
sql=sql & " WHERE codeinv='" & inventory.item(i).Text & "'"
on error resume next
conn.Execute sql
if err <> 0 then
msgbox("No update permissions! " & err )
else
msgbox("Record " & nom & " was updated!")
end if
' End of the update
Next
conn.close
end sub
function getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
end function
You can use the variable to load the table (see the example below), however its not advisable to load table using Qlikview, it will table several minutes to just load 500-1000 rows.;
LET vOHNumRows = NoOfRows('Table');
LET vOHLoadRows = 0;
FOR I = 0 TO $(vOHNumRows) - 1
LET vField1 = peek('Field1', $(I), 'Table');
LET vField2 = Date(peek('Field2', $(I), 'Table'),'YYYY-MM-DD');
LET vField3 = peek('Field3', $(I), 'Table');
SQL INSERT INTO qv.DBTable (DBField1,DBField2,DBField13)
values ('$(vField1)','$(vField2)','$(vField)');
LET vOHLoadRows = $(vOHLoadRows) + 1;
Next
~Sachin