Update and external Database with a Macro.

[#1]

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

[#2 · Accepted solution]

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

[#3]

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.

[#4]

Thanks a lot ! That works.

Philippe

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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