Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've read quite a number of posts in regard to writing back to the database and had it working when I hard-coded the values but when I changed to use variables, it does not work. When I try to run the code below using the variables, I get a "No update permissions!" error (exactly as the code says - just not sure what, exactly, I am doing wrong.
Details:
Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).
The Macro is as follows (the real code has an actual server listed in the Data Source= portion, left it out here):
sub DataInsert
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider='sqloledb';Data Source=<MyServer>;Initial Catalog='Rally';Integrated Security='SSPI';"
set rs=CreateObject("ADODB.recordset")
' Database Update
sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"
on error resume next
conn.Execute sql
if err <> 0 then
msgbox("No update permissions! " & err )
end if
conn.close
end sub
Thanks for any help you can provide.
Mark.
Mark Worthen wrote:
' Database Update
sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"
Mark.
Hello Mark,
One thing you may want to look into is whether the statement requires a listing of field names the values are being inserted into (QV VBA macros use this syntax)
sql="INSERT INTO mw_TestTable (Field1, Field2, Field3) VALUES (‘$(vTestChar)', ‘$(vTestInt)’,’$(vTestDate)’”
Where the variables defined? You cannot use QV variables directly in a macro. You need syntax like this:
set v = ActiveDocument.Variables("vTestChar ")
vTestChar = v.GetContent.String
Thanks Evan,
I tried adding the fields and still got the error. I also noticed your syntax was a bit different on the Values section so matched that as well and still get the No Permissions error.
Thanks.
Mark.
Thanks for your help Jonathan. That did the trick. Used your code to define all three variables and it works as expected/desired.
Much appreciated.
Mark.
Hello Mark,
That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable.
Hi Evan,
That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable.
That is where I thought I was. Tools; Edit Module. Entirely likely my ignorance is showing through again. Where is the VBA module as compared to where I was?
Thanks.
Mark Worthen wrote:
Details:Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).
Hi Evan,
That syntax is used in conjunction with DynamicUpdateCommand, which can be launched from the VBA module and doesn't require the intermediate steps of assigning a QlikView variable into a VB variable.
That is where I thought I was. Tools; Edit Module. Entirely likely my ignorance is showing through again. Where is the VBA module as compared to where I was?
Thanks.
Hello Mark,
Indeed it sounds like you're in the right place. If you use DynamicUpdateCommand, the VBA code can reference Qlikview variables without having to convert to VB variables first, but this is only writing back to the internal QVW. For an external DB connection, syntax converting each QV variable to a VB variable is the way to go when using the VB module.
However, if your goal is to writeback many QV variables into an external database, why are you using the VBA module as compared to a partial reload? For writeback, it seems in-script replacement of QV variables in conjunction with native DDL in non- SQL SELECT statements would be easier to form than building in the VB module.
For example, why is something like this off the table?
IF IsPartialReload() AND '$(vDBAction)' = 'INSERT' THEN
OLEDB CONNECT TO [Data Source=$(MyServer);Initial Catalog='Rally';Integrated Security='SSPI'];
SQL INSERT INTO mw_TestTable VALUES ('$(vQVVariable1)','$(vQVVariable2)', '$(vQVVariable3)');
END IF
Evan Kurowski wrote:
However, if your goal is to writeback many QV variables into an external database, why are you using the VBA module as compared to a partial reload? For writeback, it seems in-script replacement of QV variables in conjunction with native DDL in non- SQL SELECT statements would be easier to form than building in the VB module.
For example, why is something like this off the table?
IF IsPartialReload() AND '$(vDBAction)' = 'INSERT' THEN
OLEDB CONNECT TO [Data Source=$(MyServer);Initial Catalog='Rally';Integrated Security='SSPI'];
SQL INSERT INTO mw_TestTable VALUES ('$(vQVVariable1)','$(vQVVariable2)', '$(vQVVariable3)');
END IF
I google'd how to write back to the database and never found this as an option. More learning to do. That looks like a better option.
I'll have to study up on the IsPartialReload() function and go from there.
Thanks for the pointer.
Mark Worthen wrote:
I google'd how to write back to the database and never found this as an option. More learning to do. That looks like a better option.I'll have to study up on the IsPartialReload() function and go from there.
Thanks for the pointer.
The IsPartialReload() part is related to how much data you already have loaded within the same application as your DB update client.
Lets say you were working with an empty or near empty shell application with very little data, a .QVW designed to execute DDL only, your writeback statement is going to take about the same execution time for Full vs. Partial reload.
But in a scenario where you've loaded a large volume of data, and are connecting your update variables to field selections made against this large dataset, the extra time between each full reload transaction would start to feel cumbersome. (Full reload dumps out all the data, and starts anew, vs. Partial which doesn't erase any data, only performs supplementary actions).
Then as I saw mentioned earlier, a switch statement can be useful to organize and expand into a variety of DDL actions in an orderly manner.
OLEDB Connect[ToSomething];
SWITCH '$(vDBAction)'
CASE 'INSERT'
//INSERT SQL
CASE 'UPDATE'
//UPDATE SQL
CASE 'CREATE'
//CREATE SQL
CASE 'DROP'
//DROP SQL
END SWITCH