Have you tried using a command object with the prepared property set to true?
Not sure if the Oracle provider support the "Perpared" property. If it does
it make make your insert faster.
If not it may not make much improvement.

Look in the help file for ADO Command object or Prepared Property

Basically, you create a connection to Oracle and create a command object
with required parameters
Then inside your loop you change the parameter values then run the Execute
method.

Here is some sample code.
NOTE: the parameter types may not match what your field data type are.
You may have to tweak them to get it right before it will work.

Dim sSql As String
Dim mCmdInsert As ADODB.Command

sSql = "INSERT INTO tblName ( ID, Name ) Values (?,?)"

Set mCmdInsert = New ADODB.Command
With mCmdInsert
.CommandType = adCmdText
.CommandText = sSql
.Parameters.Append .CreateParameter("ID", adInteger, adParamInput)
.Parameters.Append .CreateParameter("Name", adVarChar, adParamInput,
25)
' this ADODB connection object (mCon) would have been created earlier
Set .ActiveConnection = mCon
.Prepared = True
End With

'Here is the loop
For I = 1 To 5000
With mCmdInsert
.Parameters("ID").Value = I
.Parameters("Name").Value = sTagName
.Execute
End With
Next

Kevin M.


"Steve" <stevepyn@hotmail.com> wrote:
>
>What version of Oracle are you using? Also, what does your connection string
>look like? I work with Oracle and ADO and haven't come across any significant
>problems with performance like you are describing. Try executing the same
>statements in a tool like Golden or Toad. if these are taking a considerable
>amount of time, then the problem is likely your Oracle configuration. I

can't
>help in this area, it's the realm of the DBA. If golden is executing

the
>statements quickly but ADO isn't, then it's likely a problem with your connection
>string. I recommend using the OLEDB provider from Microsoft (MSDAORA) since
>I've personally found it a bit faster than the Oracle provided one. (Our
>initial performance tests were with 8i, we've had no need to re-test with
>later versions.)
>
>Post your connection string. Oracle is certainly not "slow" when configured
>and accessed properly. :]
>
>Steve.
>
>
>"Manuel De Leon" <manuel@codetel.net.do> wrote:
>>
>>I really can't figure out why Oracle seems to be MUCH slower than Access

>or
>>SQL Server, using ADO.
>>
>>If I insert 5000 records like this on Access or SQL Server, the time is

>mesured
>>in seconds:
>>For I = 1 To 5000
>>rst.AddNew
>>rst.Fields("Id").Value = I
>>rst.Fields("Name").Value = "John"
>>rst.Update
>>Next
>>
>>If I try the same on ORACLE the time is mesured in minutes !!!
>>
>>Isn't ORACLE supposed to be a **** of a database?
>>
>>PS. I have made A LOT of testing using the cnn.Execute "INSERT ..." vs

the
>>rst.AddNew and using the recordset always outperforms executing an insert.

>