Update / Delete oracle record using 0040

gareth_stevens

New Member
Joined
Mar 25, 2015
Messages
1
Hi,

I am trying to update or delete records in an oracle table using 0040.

I can connect, insert and retrieve data, but I am having difficulty updating or deleting rows.

I currently have the following vba code;

Sub update()
'Declare variables
Dim OBJSession As OraSession
Dim OBJDatabase As OraDatabase
Dim OraDynaset As OraDynaset

Dim sid As String
Dim username As String
Dim pass As String
Dim sql As String




Sheets("BANK_UPDATE").Select ' ensures correct sheet is selected for update


' define database paramters
sid = "dbname"
username = "dbuser"
pass = "dbpassword"


'Create the OraSession Object.
Set OBJSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OBJDatabase = OBJSession.OpenDatabase(sid, username & "/" & pass, 0&)


OBJDatabase.Parameters.Add "ROWID", "", 1


LROWS = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count ' column used as ref for list


For lngrow = 2 To LROWS


MyColumnA = Range("A" & CStr(lngrow)).Value




If MyColumnA = "" Then Exit For






With OBJDatabase
.Parameters("ROWID").Value = MyColumnA


.CreateSql ("DELETE from table WHERE ROWID = : ROWID")




End With


Next




Set OBJDatabase = Nothing
Set OBJSession = Nothing


End Sub

---

I am looking to delete all records from a table where the rowid is in column a, my sheet looks like;
ROWIDACCOUNT_NOBANK_CODECURRENTRY_DATEAS_ATAMOUNT
AACq5tAASAAB+BbAAD9920-00-00GBP24/03/201510.30.49902.5
AACq5tAASAAB+BbAAB9920-00-00GBP24/03/201510.27.29176567.01
AACq5tAASAAB+BbAAC9920-00-00GBP24/03/201510.27.293531.24
AACq5tAASAAB+BbAAA9920-00-00GBP23/03/201510.27.299480.96
AACq5tAASAAB+BfAAC9920-00-00GBP24/03/201510.27.293531.24
AACq5tAASAAB+BfAAB9920-00-00GBP24/03/201510.27.29176567.01
AACq5tAASAAB+BfAAA9920-00-00GBP23/03/201510.27.299480.96
AACq5tAASAAB+BfAAD9920-00-00GBP24/03/201510.30.49902.5
AACq5tAASAAB+BdAAD9920-00-00GBP24/03/201510.30.49902.5
AACq5tAASAAB+BdAAC9920-00-00GBP24/03/201510.27.293531.24
AACq5tAASAAB+BdAAA9920-00-00GBP23/03/201510.27.299480.96
AACq5tAASAAB+BdAAB9920-00-00GBP24/03/201510.27.29176567.01

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top