currentdb.execute returns error "cannot execute a select query"

or try this

Dim qd As QueryDef
Dim rs As DAO.Recordset

Set qd = CurrentDb.QueryDefs("jshq_update_ldtext_oracle")
Set rs = CurrentDb.OpenRecordset("jsht_LONG_DESCRIPTION")

rs.MoveFirst

While Not rs.EOF

   qd.Parameters("param1") = rs(0)
   qd.Parameters("param2") = rs(1)
   qd.Parameters("param3") = rs(2)
   qd.Parameters("param4") = rs(3)
  CurrentDb.Execute ("jshq_update_ldtext_oracle")
  rs.MoveNext
 
Wend

CurrentDb.Execute (qd.SQL)

That worked until I evidently hit some "weird" characters in the LONG data field, LDTEXT. Then it bombed. But at least the query was executied.

I tried your parameters code, thinking that it might better handle the ascii characters in LDTEXT; however, I got the error message, "Run time error 3265: Item not found in this collection."

Any suggestions on either of those issues?

routinet:

So with your suggestion, I get the error: "Run-Time Error '3020': Update or Cancel update without AddNew or Edit" Here's what I think I should have with your suggestion:

Dim qd As QueryDef
Dim rs As DAO.Recordset

Set qd = CurrentDb.QueryDefs("jshq_update_ldtext_oracle")
Set rs = CurrentDb.OpenRecordset("jsht_LONG_DESCRIPTION")

rs.MoveFirst

While Not rs.EOF
    rs(0) = Replace(rs(0), "'", "''")
    rs(1) = Replace(rs(1), "'", "''")
    rs(2) = Replace(rs(2), "'", "''")
    rs(3) = Replace(rs(3), "'", "''")
   
  CurrentDb.Execute ("INSERT INTO JSHL_WO_LD_TEST _
      (WONUM, LDKEY, LDTEXT, REPORTDATE) VALUES _
      ('" & rs(0) & "', '" & rs(1) & "', '" & rs(2) & "', '" & rs(3) & "');")
  rs.MoveNext
 
Wend

Oops, I re-read your question and got it to work - awesome! Thanks! It still took 8 minutes to append only 831 rows to my Oracle table from my Access table. Is there any other way to make this faster? I'm not using any indexes on these test tables while I try to improve the speed. I still would like to get YZlat's parameter lines to work and see if that would help.

Here's my final code that is working:

Dim qd As QueryDef
Dim rs As DAO.Recordset

Set qd = CurrentDb.QueryDefs("jshq_update_ldtext_oracle")
Set rs = CurrentDb.OpenRecordset("jsht_LONG_DESCRIPTION")

rs.MoveFirst

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), "'", "''") & "');"

  rs.MoveNext
 
Wend

routinet:

Thanks! I'm in training this week and hopefully we'll get some time to try that out. If I don't this week, I will do it first thing next week and close out this thread. Thanks again!

O.K. here's what I have and it took 5 minutes to load only 831 records with only 4 fields:

==================
Sub append_ld_to_oracle()

CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST (WONUM, LDKEY,LDTEXT, REPORTDATE) SELECT * FROM jsht_LONG_DESCRIPTION;"

End Sub
==================

Any other ideas on speed?

Thanks,

Joel

I think that will be as fast as you can get it done.  Between communication, different data providers, and the data itself, I do not believe you will push a lot more performance out of that.  Then again, I'm not real experienced in optimizing queries.  :)

Are any of those particularly long text fields?  What is the data size of each record?

Here is the table structure:

----------------------------------------- -------- -----
WONUM                                          CHAR(12)
LDKEY                                             CHAR(40)
REPORTDATE                                  DATE
LDTEXT                                          LONG
----------------------------------------- -------- -----

The LDTEXT field can be rather long, but not the majority of the time.

What exactly do you mean your LDTEXT field can be rather long?  You are stating it is of data type "long", which is numeric and takes up a set number of bytes for storage.  If it's the equivalent of a memo/note field, it is most likely the cause of the slowness.  Other than that, your record length is not even 70 bytes.  That would be, at the upper limit, around 60-70k you need transferred (800 records x 70 bytes each) from Oracle.  Not much at all, and certainly not enough to justify a five minute wait.  

You can try a little experiment to find out where the lag is taking place.  First, use a SELECT statement to pull the recordset and record how long it took.  Second, put all the information you want to INSERT into a temporary local table (in Access), run your INSERT from the temp table to the final table, and record how long that took.  This will not give you exact execution times, but it should give you a general idea how long each step of the process is taking.  If the majority of the delay is in the second test, there's not anymore you can do.  That is the time Access requires to move those records into a table.  If the delay is on the first test, have your IT department check out the network.  There may be a bottleneck somewhere that is causing latency, or it could even be the server is using too many resources to respond quickly.  Network Monitor (or similar 'snooping' drivers) should be able to give you more information.

The LDTEXT field in Oracle and Access is a MEMO field. "Rather Long" means that it sometimes has a couple thousand characters in it but most often runs about 400 characters. I did some timing tests:

WITH THE MEMO FIELD:

--- INSERT statement as last defined above takes 5' 30"
--- "SELECT * FROM JSHL_WO_LD_TEST;" takes 1' 15"

WITHOUT THE MEMO FIELD:

--- INSERT statement as last defined above takes 5' 10"
--- "SELECT * FROM JSHL_WO_LD_TEST;" takes 0' 5"

It's amazing that with no memo field and only 3 columns, 831 records, it still takes over 5 minutes to execute the Insert statement. I'll have to send these statistics to our DBA.

Is there any other way of loading into Oracle that could be faster? How in the world do these folks load millions of records if Oracle is so slow? I think I could file papers in a filing cabinet faster than this example!

I was considering the Memo field to be the culprit, but apparently not.  Have you tried a benchmark with a 1-column INSERT?  I doubt you will see much better performance, but it would be interesting to compare.

One other path you can try is to redo the code to use ADO instead of DAO.  With DAO, if you are not contacting a JET object, you have to go through ODBC.  That always heavily increases execution time.  Oracle should have direct ADO connectivity, so you can bypass the middleman entirely.  Try the same benchmarks again with using an ADO connection.  If you need a code example, say the word.

I definitely need a simple example of using ADO with the queries above. I'm new to the Oracle world but would love to try anything to get a speed increase. Thanks! I sent the time trials above to our DBA but he hasn't gotten back with me yet. I don't think he's an "Access to Oracle" kind of guy though so he may not be of much help.

O.K. I'll give the ADODB a try but it'll take me some time to translate what I need to do from the code. Thanks a TON for the suggestion. I'll close this thread as soon as I have some ADODB results and comments to add. It might take me a few days though especially with the weekend coming up.

I looked in the Object Browser and didn't see anything remotely resembling ADODB in the "Classes" window. I did a search through all libraries for ADODB and nothing was found. Does that mean I'm out of luck with ADODB?

Nevermind: I needed to select ADODB from the Libraries list. I'm not sure why my search through all libraries didn't find ADODB but I've got it now.

Here is the code I'm attempting now:

======================
Dim Cnxn As ADODB.Connection
    Dim cmdChange As ADODB.Command
    Dim strSQL As String
   
    ' Define SQL statement to execute as command text
    strSQL = "INSERT INTO rrcfat.WO_LD_TEST_TBL (WONUM, LDKEY, REPORTDATE, LDTEXT) SELECT * from jsh_long_desc;"
   
    ' Open a connection using a DSN and individual
    ' arguments instead of a connection string.
    Set Cnxn = New ADODB.Connection
    Cnxn.ConnectionTimeout = 30
    Cnxn.Open "oxyt", "rrcfat", "rrcfat"
   
    ' See if I opened the connection
    MsgBox "cnxn state: " & Cnxn.State
   
     ' Create and execute command object
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = Cnxn
    cmdChange.CommandText = strSQL
    cmdChange.Execute

    ' Close the connection
    Cnxn.Close

    ' See if I closed the connection
    MsgBox "cnxn state: " & Cnxn.State

======================

When I run the above, I get an error:

Run-time error '-2147217865 (80040e37)':

[Oracle][ODBC][Ora]ORA-00942: table or view does not exist


Remember: the table "jsh_long_desc" is a local MS Access table. Will the SQL going through ADO be able to correctly reference my local Access table to insert from?

As I play more with ADO, I think I'm seeing another issue: special characters. Do I have to modify the INSERT command in some way to handle special characters like carriage returns, tabs, %, /, #, etc. When I executed the Insert command with DAO, it worked fine. Maybe that is part of my speed degradation? special characters?

routinet:

As soon as you see this, let me know. I've gotten a lot of help from you, along with the solution to my initial question, so I'm going to close this thread. If you could follow this speed/ADO conversation to a similar thread I have:

https://www.experts-exchange.com/questions/21104484/Append-to-Oracle-too-slow.html

I'd appreciate your continued help,

Joel