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?
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) _
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?
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
.Parameters("FIELD1").Value = strFIELD1
.Parameters("FIELD2").Value = strFIELD2
.ExecuteSQL "insert into MY_TABLE values(:FIELD1,:FIELD2)"
If lngRow Mod 10 = 0 Then
Set OraDynaSet = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
I've done some "trench digging" to learn this, but this really is an awesome connection method.
Thanks for responding to my original question.
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?
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.
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 .