Database write-back on Accesspoint

[#1]

All - I have a nice QVW which performs database writeback using macros and ADO.  I have enabled System Access and made my macro permissions loose on the Server.  My QVW works on Desktop but when you get to server it does not write back to the database or update anything.  I click it on AJAX and it doesn't do squat.  My macro is below:

Public Sub UpdateAllocation

    Application.WaitForIdle

    On Error Resume Next

    Err.Clear

    Set vErr = ActiveDocument.Variables("vMacroErr")

    dim sConn, oConn, oCmd

    Set oConn   = CreateObject("ADODB.Connection")

    Set oCmd    = CreateObject("ADODB.Command")

    'Parameters

    Set pId     = CreateObject("ADODB.Parameter")

    Set pGlName = CreateObject("ADODB.Parameter")

    ' Capture input variable values

    Set vHidId        = ActiveDocument.Variables("vHidId")

    Set vInpGlName    = ActiveDocument.Variables("vInpGlName")

    ' Connect to database

    sConn = "a string I know works on the server"

    oConn.Open sConn

    ' Run command with parameters

    oCmd.ActiveConnection = oConn

    oCmd.CommandText = "UPDATE SomeTable SET GLNAME=? WHERE Id=?"

    oCmd.CommandType = 1

    oCmd.Prepared = TRUE   

    Set pId     = oCmd.CreateParameter("Id", 3, 1, 4, vHidId.GetContent.string)

    Set pGlName = oCmd.CreateParameter("GlName", 129, 1, 50, vInpGlName.GetContent.string)

    oCmd.Parameters.Append pGlName

    oCmd.Parameters.Append pId

    oCmd.Execute

    oConn.Close

    Set oCmd = Nothing

    Set oConn = Nothing

    If Err.Number <> 0 Then

        vErr.SetContent "Error in " & Err.Source & ":  " &  Err.Description, true

    Else

        vErr.SetContent "Successfully updated allocation:  " & vHidId.GetContent.string, true

    End If

    ActiveDocument.Fields("Id").Clear

End Sub

[jerrysvensson]

Could be lots of things.

First:

Ajax macros run serverside. Do the QVS service account has access to the database?

Be aware of that all your users will that account when adding data to the database when you get it working.

I would use variables to debug the script in Ajax. Since you can't use MsgBox.

[#3]

Jerry - Thanks for your response...Interesting thoughts, but I am:

1) Logging into the database in my connection string using a db uid/pass
2) Catching errors and writing to variable vErr and putting it on the page

Any other way to catch responses, etc?

[jerrysvensson]

Error handling in VBscript is very limited unfortunately.

Have you checked the "Allow unsafe macro execution on server " in the security tab in the management console?

[#5]

Yep.  Unlocked all things "safety related".

[advait_thakur]

Hello Matthew,

  I have achieved this in QV version 9 with the help of macro and EDX triggers, my business users were able to writeback to database from access point. Macros are enemies here and as Jerry said VB scripts are really limited.

What version of QlikView are you using ?

By the way bWiseWriteBack extension is available now with Qlik and it will allow you to writeback to Database.

I am providing you the link and I believe it will be easy for you without macro. I will recommend you to utilize this tool.

bWise Writeback extension for Qlikview

I hope this is helpful and informative.

Regards

Advait

[#7]

Actually ... adding some additional "write out to variable" indicates that the macro doesn't run at all..  I put a set variable statement at the very top and it does not get called.  I confirmed that setting a variable can be done in another macro.

[#8 · Accepted solution]

Figured it out.  Had to remove:    Application.WaitForIdle

Still not sure why I added that code or even needed it!

[#9]

Hi Matthew,

I am trying to implement the write back functionality in my project.

I have to update the feedback provided by user once the given task is done.

Dashboard is pulling feedback data from excel (.xlsx) file and updating into the same file.

I have used your code and updated the connection string

I am using following connection string:

xlFile="F:\Others\Comments.xlsx"

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlFile & ";Extended Properties='Excel 12.0 Xml;HDR=No';"

above code is giving me error "Provider cannot be found. It may not be properly installed."

Thanks in advance.