Append to Oracle too slow
Posted on 2004-08-23
I am using an Access database front end to an Oracle back end. I create a temporary table for data within MS Access and then want to append that data to the Oracle table. The code I am currently using works, but take 6 minutes to append only 800 records! If anyone has any ideas on speeding this up, I'd sure be interested. I'm also analyzing and computing statistics on the Oracle side just about every time I do something just to try and help the speed.
I have set up two test tables to get this functioning before I go with live data. The table in Oracle is called JSHL_WO_LD_TEST. The table in Access is called jsht_LONG_DESCRIPTION. I'm not a programmer so please forgive my naming conventions. I'm using the following code to append from Access to Oracle:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("jsht_LONG_DESCRIPTION")
While Not rs.EOF
CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST _
(WONUM, LDKEY, LDTEXT, REPORTDATE) VALUES _
('" & Replace(rs(0), "'", "''") & "', '" & Replace(rs(1), "'", "''") _
& "', '" & Replace(rs(2), "'", "''") & "', '" & Replace(rs(3), "'", "''") & "');"