Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More

Re: Macro to update database

I just made that up for sharing, but I am using the database name for DataSource....

wdchristensen
Valued Contributor

Re: Macro to update database

I think issue is that you are using the database name where the DSN would go. Maybe something like:

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

or create a DSN with the name db.

Re: Macro to update database

I am not sure what the ServerName would be....  but I did create DSN and that did not help. But having done some more test, I think the connection string might be working... because if I change db to something else... it doesn't go past the connection string. So, seems like something else is a problem. Checking with another query

Re: Macro to update database

Just saw that I was using the semi-colon which might have been causing some issue for the query itself... now that SELECT works... moving to update next

Highlighted

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