Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Writeback via macro in Qlikview

I am having some issues with writing back from a dashboard back into my database.  I understand all the issues involved, but the business unit is really demanding the functionality for one of our dashboards, and are forcing my hand. 

I am successful in doing a writeback via ODBC when I have the dashboard open on my desktop.  However, it is not working when I do the same in Internet Explorer (we are using the IE plugin, if it helps).  I created the ODBC driver on our QV server already, but maybe I configured something incorrectly?  Any help or guidance is appreciated!

The error I receive is "Macro parse failed. Functionality was lost DBWriteBack Error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.


Sub DBWriteback

dim sConn, oConn
Set oConn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
sConn = "dsn=test;Uid=Test;Pwd=Test;"

oConn.Open sConn

set val=ActiveDocument.Fields("Team1").GetPossibleValues
set val2=ActiveDocument.Fields("Team2").GetPossibleValues

for i=0 to val.Count-1
SQL_Str = "INSERT INTO QVTable (Team1, Team2) VALUES ('" & val.Item(i).Text & "', '" & val2.Item(i).Text & "')"
'Msgbox (SQL_Str)
'Connection.Execute SQL_Str
objRecordSet.Open "INSERT INTO QVTable (Team1, Team2) VALUES ('Team1_05','Team2_05')",oConn


next

oConn.Close
Set oConn = Nothing

End Sub


8 Replies
marcus_sommer

It seems that there is not the right driver installed/available, see: Re: The specified DSN contains an architecture mismatch between the Driver and Application.

- Marcus

Anonymous
Not applicable
Author

Hi Marcus,

Thank you for the quick reply, but I still need some more guidance as I do not see how this solves my issue.  The test connection works fine when I try to test it, and I have the ODBC set up as both 32 and 64 bit (and have tried deleting one and the other both to see if that would work as well).  What is referenced in the link you provided seems to also be slightly different, since I believe the link references the load script and what I am trying to do is via a macro.  Any more thoughts on things I can try?

andrey_krylov
Specialist
Specialist

Hi Ilya. If I understand correctly, then it's not be possible to write data back straight from access point. First you have to save it to the server (with ServerSideExportEx) and then write to the database.

marcus_sommer

I think there is not much difference if a database is accessed from the script or from the macro - in both cases it's needed that the right driver is called and a proper sql-statement is transferred from the execution-tool over the driver to the database.

If you are executing this macro within the access point in the IE plugin it will be called from this local user and the local machine and not from the server environment. This means the driver must be installed on the local machine and the user/network needs the proper access rights to the database.

- Marcus

andrey_krylov
Specialist
Specialist

Hi Marcus, and in fact, I think you're right. I've never thought about it but it should work. The only thing it's maybe not always convenient, and yet...

Anonymous
Not applicable
Author

Hi Marcus,

I am trying to understand the driver point you have made several times now.  I have the 13.0 Microsoft SQL server driver installed an am using it on my desktop.  When I test the web version via the IE plugin, I am using the same exact driver.

Andrey seems to be giving me a different answer saying it is not possible to write back via Access Point.  Trying to understand the right recommended approach for this.  I  can investigate the ServerSideExportEx approach that is mentioned, but any more guidance is appreciated. 

andrey_krylov
Specialist
Specialist

Hi, Ilya. As I said you export a chart to the server via macro. Here's mine

function Export(){

  var vChart = ActiveDocument.GetSheetObject("CH00")

  vChart.ServerSideExportEx( "C:\\QlikView\\XLS\\XlsFile.xls", ";", 5 ) //0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF

}

I'm sorry for javascript, you can translate it to vb easily. There I have a script that monitors a folder and in my case sends files to emails. Here's a part of this vbscript


Function Monitor()

  Dim strDrive, strDir, strExt, strAction, WMIService, Events, ReceivedEvent, XLString, Email, ExcelFile, Subject

  strDrive = "c:"

  strDir = "\\QlikView\\XLS\\"

  strExt = "xls"

  strAction = " "

  Set WMIService = GetObject("winmgmts:\\.\root\CIMv2")

  Set Events = WMIService.ExecNotificationQuery _

  ("SELECT * FROM __InstanceOperationEvent WITHIN 5 WHERE " & _

  "TargetInstance ISA 'CIM_DataFile'" & _

  " AND TargetInstance.Extension = '" & strExt & "'" & _

  " AND TargetInstance.Drive = '" & strDrive & "'" & _

  " AND TargetInstance.Path = '" & strDir & "'")

  Do While(True)

  Set ReceivedEvent = Events.NextEvent

  Select Case ReceivedEvent.Path_.Class

  Case "__InstanceCreationEvent": strAction = "Creation"

  Case "__InstanceDeletionEvent": strAction = "Deletion"

  Case "__InstanceModificationEvent": strAction = "Modification"

  End Select

  If (strAction = "Creation" or strAction = "Modification") Then

     ===========Do Something===============

  End If

  Loop

  End Function


marcus_sommer

If I understand you right than you tried it with the desktop client and the IE plugin on the same machine - is it also the same user? This meant the same drivers are available but you don't specify a certain driver within your macro and therefore it might fail: Accessing Data with ADO.

Further you need to check if any macro is executed within the IE plugin - a simple msgbox should show it (and using a few of them with err.number and err.description will be quite helpful to find the error) and the export of any object to excel/csv will show if it has also system-access.

Another check should go to the various security settings - most of them are globally but there are also a few IE specific ones, like the protected mode or the various ActiveX/Javascript settings (ensuring that the access point is belong the trusted sites is usually enough for it).

Also you should look if the IE traffic goes over any proxy-server and disable it for the test. Further try at first a read-access to the database and not a write-access.

- Marcus