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;
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
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;
ROWID | ACCOUNT_NO | BANK_CODE | CURR | ENTRY_DATE | AS_AT | AMOUNT |
AACq5tAASAAB+BbAAD | 99 | 20-00-00 | GBP | 24/03/2015 | 10.30.49 | 902.5 |
AACq5tAASAAB+BbAAB | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 176567.01 |
AACq5tAASAAB+BbAAC | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 3531.24 |
AACq5tAASAAB+BbAAA | 99 | 20-00-00 | GBP | 23/03/2015 | 10.27.29 | 9480.96 |
AACq5tAASAAB+BfAAC | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 3531.24 |
AACq5tAASAAB+BfAAB | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 176567.01 |
AACq5tAASAAB+BfAAA | 99 | 20-00-00 | GBP | 23/03/2015 | 10.27.29 | 9480.96 |
AACq5tAASAAB+BfAAD | 99 | 20-00-00 | GBP | 24/03/2015 | 10.30.49 | 902.5 |
AACq5tAASAAB+BdAAD | 99 | 20-00-00 | GBP | 24/03/2015 | 10.30.49 | 902.5 |
AACq5tAASAAB+BdAAC | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 3531.24 |
AACq5tAASAAB+BdAAA | 99 | 20-00-00 | GBP | 23/03/2015 | 10.27.29 | 9480.96 |
AACq5tAASAAB+BdAAB | 99 | 20-00-00 | GBP | 24/03/2015 | 10.27.29 | 176567.01 |
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>