Macro to update database

[sunny_talwar]

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‌

[sunny_talwar]

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

[wdchristensen]

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]

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]

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 · Accepted solution]

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