Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
sunny_talwar

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

1 Solution

Accepted Solutions
sunny_talwar
Author

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
wdchristensen
Specialist
Specialist

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.

sunny_talwar
Author

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
Specialist
Specialist

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

snpData.Update strSQL, dbMyDBConnection

sunny_talwar
Author

Nope, that did not work

wdchristensen
Specialist
Specialist

snpData.Execute strSQL, dbMyDBConnection

???

sunny_talwar
Author

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
Specialist
Specialist

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.

sunny_talwar
Author

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
Specialist
Specialist

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?