About the fastest I have been able to achieve adding records into an Oracle table through Visual Basic ADO is about 1-2 records per second. I've tried various "tweakings" to improve performance. Does anyone have a success method that they would be willing to share?

Here is the essence of my code:

madoRecordSet.CursorLocation = adUseClient
madoRecordSet.CacheSize = 100
madoRecordSet.Open "UPLOAD_TABLE", madoConnection, adOpenStatic, adLockOptimistic
For lngCell = 2 To lngMaxCell
strFIELD1 = Range("C" & lngCell).Value
strFIELD2 = Range("H" & lngCell).Value
madoRecordSet.AddNew
madoRecordSet("FIELD1") = strFIELD1
madoRecordSet("FIELD2") = strFIELD2
madoRecordSet.Update
next lngCell

I'm using some VBA scripts behind an Excel spreadsheet, to allow users to upload their worksheets into some Oracle tables, and would appreciate anyone who has been down this road sharing a tip or two.

Thank you,
David Sacker

Don't know if you can work with the connectionin VBA. If you can, and you're still tweaking your code, you might try something along the lines of:

for cellRowNo = cellRow1 to cellRowMax
strSQL = "INSERT INTO tableName (field1Name, field2Name) _
VALUES (cell1Value,cell2Value)"
cnn.execute strSQL
next cellRowNo
cnn.execute "commit"

The real problem is having to return to the worksheet, finding the row and selected columns, and reading the cell values each time you go around the loop. Why not read the target cells into an array and loop through the array?

To vududoc,

I really didn't get any noticeable improvement via an array. However, since my original question, I have discovered the beauty of Oracle's OLE DB connection rather than ADO, which has given me about 8 times (yes, 8 times) the throughput.

Let's pretend we want to insert the cell values in columns A and B up into an Oracle database table. Here is the essence of what I'm doing now (what to substitute should be obvious):

Public Sub Kick_Ass_Method()
Dim OraDatabase As Object
Dim OraDynaSet As Object
Dim OraSession As Object
Dim lngRow As Long
Dim strFIELD1 As String
Dim strFIELD2 As String

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase ("my_database", "userid/password", 0)
OraDatabase.Parameters.Add "FIELD1", "", 1
OraDatabase.Parameters.Add "FIELD2", "", 1
Set OraDynaSet = OraDatabase.CreateDynaset("select FIELD1, FIELD2 from MY_TABLE", 0)

For lngRow = 1 To 100
strFIELD1 = Range("A" & CStr(lngRow)).Value
strFIELD2 = Range("B" & CStr(lngRow)).Value
With OraDatabase
.Parameters("FIELD1").Value = strFIELD1
.Parameters("FIELD2").Value = strFIELD2
.ExecuteSQL "insert into MY_TABLE values(:FIELD1,:FIELD2)"
End With
If lngRow Mod 10 = 0 Then
DoEvents
End If
Next lngRow

OraDynaSet.Close
OraDatabase.Close
OraSession.Close
Set OraDynaSet = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub

I've done some "trench digging" to learn this, but this really is an awesome connection method.

Thanks for responding to my original question.

David

PS: Anyone with questions about this, I'm more than happy to share the wealth of what I've dug out.

I am trying to get some data from Oracle using Excel. I am faced with the problem of my querystring being complex and > 255 characters.
I am trying to use CreateDynaset (variant,&0) . Though the variant holds my entire query, I am not able to execute as only 255 chars are read.

Else can I do a Nested Dynaset. First I create the Dynaset and then from the Dynaset use another query on the first dynase?

Regards

Anand

I never have a limitation (255 or otherwise) on an OraSession.CreateDynaset ... are you doing a CreateDynaset through some other method?

Also, rule out that you're not truncating your string via some code, property or method limitation that you may be executing prior to the CreateDynaset command.

And yes, OraSession works great in VBA (in response to a comment further above). I use it more than any other method now.

I hadn't been on here in awhile and thought I'd share another method of inserting data into a table. You can always write it to a text file, then invoke SQL Loader. I load approximately 2000 records per second with SQL Loader, so if large data inserts are the objective, you really can't beat it. It's worth the extra coding care.

thankyou Dsacker..

I am trying to create an inventory from our main database into an excel sheet and this has about 400 tables data moved to excel. i am trying to get data out of Oracle into Excel.

But its got to be tailored for different requirements so i have a macro which i run which generates the query based on the request form i get .
Some queries tend to be long due to several where conditions and also length of table name is large. So I used aliasing ..but still the query stored in the variable truncates the query after 255 characters. I changed this to a variant variable. Still no luck. Hence the request. Thank you for your time and advice .

Regards

Anand