Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT

Macro to update database

Hey Guys - I am trying to run this... but it doesn't do anything... Any hints on what I might be doing wrong

SUB UpdateRecord


'Save as ConnectDB.vbs

Dim strSQL

Dim strUsername

Dim strPassword

Dim snpData

Dim dbMyDBConnection

Dim Dimension       

Set snpData = CreateObject("ADODB.Recordset")

Set dbMyDBConnection = CreateObject("ADODB.Connection")

strUsername = "User"

strPassword = "Pass"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";"

dbMyDBConnection.Open

Dimension = "A"

strSQL = "UPDATE TABLE_NAME SET FIELD1 = 'YES' WHERE DIM = '" & Dimension & "';"

strSQLCommit = "COMMIT;"

snpData.Open strSQL, dbMyDBConnection

snpData.Open strSQLCommit, dbMyDBConnection

snpData.Close

dbMyDBConnection.Close

Set snpData = Nothing

Set dbMyDBConnection = Nothing

END SUB

marcus_sommer‌, tamilarasu

Tags (1)
1 Solution

Accepted Solutions

Re: Macro to update database

So this is what finally worked for me

'Save as ConnectDB.vbs

Dim strSQL

Dim snpData

Dim dbMyDBConnection

Dim vSelectionDim

Dim vSelectionTradeDate


SUB UpdateRecord

Set snpData = CreateObject("ADODB.Recordset")

Set dbMyDBConnection = CreateObject("ADODB.Connection")

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=Db;User Id=username;Password=pass;"

dbMyDBConnection.Open

vSelectionDim = ActiveDocument.Evaluate("DIM")

vSelectionTradeDate = ActiveDocument.Evaluate("Date(Date, 'MM/DD/YYYY')")

strSQL = "UPDATE APPPNLADMIN.TABLE_NAME SET FIELD1 = 'Yes' WHERE DIM = '" & vSelectionDim & "' and DATE = TO_DATE('" & vSelectionTradeDate & "', 'MM/DD/YYYY')"

strSQLCommit = "COMMIT"

snpData.Open strSQL, dbMyDBConnection

snpData.Open strSQLCommit, dbMyDBConnection


'snpData.Close

dbMyDBConnection.Close

Set snpData = Nothing

Set dbMyDBConnection = Nothing


END SUB

View solution in original post

14 Replies
Highlighted
wdchristensen
Valued Contributor

Re: Macro to update database

Hi Sunny,

The code looks good but the database could be locked by another transaction. You might try to execute the update code outside of a script just to verify that it isn't something on the database side.

Re: Macro to update database

This is a dummy table I created just for testing this out. One thing I did find was that all the Dims need to come before the Sub...

Also, I think the problem is something related to the connection string itself because this is where it have issues

snpData.Open strSQL, dbMyDBConnection

wdchristensen
Valued Contributor

Re: Macro to update database

snpData.Open I believe is only for selecting data. Maybe try:

snpData.Update strSQL, dbMyDBConnection

Re: Macro to update database

Nope, that did not work

wdchristensen
Valued Contributor

Re: Macro to update database

snpData.Execute strSQL, dbMyDBConnection

???

Re: Macro to update database

That did not work as well. It there a way to know if we at least had a connection? I am not sure if we even got connected or not.

wdchristensen
Valued Contributor

Re: Macro to update database

If you change strSQL from an “update” to a “select”, maybe you can count the number of items in the recordset.

Dim cntRecords

strSQL = "SELECT * FROM TABLE_NAME WHERE DIM =’A’"

snpData.Open strSQL, dbMyDBConnection

cntRecords =snpData.RecordCount

If cntRecords is greater than zero we know that the connection is working.

Re: Macro to update database

So, this is what I got...

msgbox 5

snpData.Open strSQL, dbMyDBConnection

cntRecords = snpData.RecordCount

msgbox cntRecords

msgbox 6

I get the popup 5, but it never goes to 6 or cntRecords. So, it has to be the connection, right?

wdchristensen
Valued Contributor

Re: Macro to update database

I agree, something is up with the connection.


dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=db;User ID=" & strUsername & ";Password=" & strPassword & ";" 


in your example you used "db" for the data source, is that valid DSN for the connection?