Deleting records in oracle tables through vb

[zzAdm]

Hye,
I have written a program to transfer data from one oracle server to another, as part of my code i loop through each recordset deleting the records so that i can then add the new data in, the problem is this is a very slow process, can anyone think of a quicker way, an example of the code I am using is below:

orars.movirst
do until orars.eof
orars.delete
orars.update
orars.movenext
loop

Any ideas??

 

[Tranman]

Hi,
If you are using the ADODC, you can use the Recordset.Delete
method, along with the Recordset.filter property to do mass deletes. This is a kind of awkward way to do it, but it will serve your purposes.

The preferred way to do it would be to set up an ADODB Connection object, then just use the Execute method to run a SQL command. The following is code I use to delete from Oracle:

Private Sub DelDupes()
Dim strAccKey As String
Dim strDelSQL As String
Dim conKARS As New ADODB.Connection

strAccKey = "2001"
strAccKey = "'" & strAccKey & "%'"

'Open the connection to KARS
conKARS.Open "Provider=MSDAORA.1;User ID=KARS;Password=FORTOPS;Data Source=KARSTEST;Persist Security Info=True"

strDelSQL = "DELETE FROM KARS.ACCIDENTS WHERE ACCIDENT_KEY LIKE " strAccKey
conKARS.Execute strDelSQL, , adCmdText + adExecuteNoRecords
conKARS.Execute "COMMIT", , adCmdText + adExecuteNoRecords
'(don't forget to commit)
Good luck,
Paul

 

[wnpoohbear]

hi, I was wondering if you can help me. I created some tables using microsoft access and it has quite a few primary as well as foreign keys. I am using visual basic to access this database. I know that the "recordset.delete" method should be deleting the rows that I have selected but it does not seem to work. I know that there is a problem with the constraints. What I am trying to do is a simple program just for fun but this program is driving me crazy.

I am trying to make a interface that allows me to navigate through the tables in my database. I can not even seem to get any of the movefirst, movenext, movelast, moveprevious methods to work. I keep running into the problems of both EOF and BOF.

I also have a save button and a delete button.

I am wondering if anyone has a sample program or source code that may be similiar to this.

Thanks to whom has read this message and I appreciate you taking your time to read this and hope to hear from you soon