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

14 Replies
sunny_talwar
Author

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

wdchristensen
Specialist
Specialist

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.

sunny_talwar
Author

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

sunny_talwar
Author

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

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