Solved

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

Posted on 2004-08-23
23
1,034 Views
Last Modified: 2012-06-27
I am using an MS Access front end to Oracle. My append and update queries to Oracle are running horrendously slow so I'm trying the advice I have found in here at EE. The following code is what I am trying, exactly as I have it in my module. Any help suggestions? It bombs on the command "currentdb.execute" with an error "cannot execute a select query":


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.SQL = "INSERT INTO JSHL_WO_LD_TEST (WONUM, LDKEY, LDTEXT, REPORTDATE) _
       VALUES ('" & rs(0) & "', '" & rs(1) & "', '" & rs(2) & "', '" & rs(3) & "');"
  CurrentDb.Execute ("jshq_update_ldtext_oracle")
  rs.MoveNext
 
Wend


0
Comment
Question by:joelhoffman
  • 14
  • 7
  • 2
23 Comments
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 50 total points
ID: 11874151
CurrentDb.Execute (qd.SQL)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 11874208
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
0
 

Author Comment

by:joelhoffman
ID: 11874441
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?
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 450 total points
ID: 11874879
Try this change:

Dim rs As DAO.Recordset

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 ('" & rs(0) & "', '" & rs(1) & "', '" & rs(2) & "', '" & rs(3) & "');"
  rs.MoveNext
Wend

Did you recently change your code to include the parenthesis around the SQL statement for CurrentDB.Execute?  The parenthesis (in VB) indicate that a return value is expected, or that a method is being used a function.  The Execute method does not return a value.

As far as the 'weird text' in your parameters, it should work out just fine unless your text can include single-quote symbols.  You can try a quick fix for that particular problem by using the following example.

rs(0) = replace(rs(0),"'","''")
0
 

Author Comment

by:joelhoffman
ID: 11875136
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
0
 

Author Comment

by:joelhoffman
ID: 11875380
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
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 450 total points
ID: 11882021
Your speed problem is not related to how the query is executing, or how you pass the parameters to it.  You are creating a recordset, then moving record by record, completing an INSERT query for each.  That takes time.  At a speed of roughly 100 records per second, I'd say it is executing rather quickly.  :)

Since you are not doing any decision making on the records returned by your SELECT query (jsht_LONG_DESCRIPTION), it might be better to handle the INSERT as a bulk transaction, like this:

INSERT INTO JSHL_WO_LD_TEST (WONUM, LDKEY,LDTEXT, REPORTDATE) SELECT * FROM jsht_LONG_DESCRIPTION

This would take all 831 records found in that query and INSERT them as a group.  That should take considerably less time than doing 800+ separate queries.  A couple considerations: 1) Make sure you have the same fields in the query that you do in the INSERT field list.  4 fields, no more, no less, no differences.  2) Make sure you want ALL the records from the query to be pushed to the other table.  Other than that, you're well on your way.  :)

One other thing: remove the declaration for qd and its assignment to the QueryDef object.  
0
 

Author Comment

by:joelhoffman
ID: 11888225
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!
0
 

Author Comment

by:joelhoffman
ID: 11931361
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
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11931624
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?
0
 

Author Comment

by:joelhoffman
ID: 11933402
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:Steve Bink
ID: 11934624
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.
0
 

Author Comment

by:joelhoffman
ID: 11953580
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!
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11954465
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.
0
 

Author Comment

by:joelhoffman
ID: 11955903
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.
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 450 total points
ID: 11958229
I'm in Miami, and so don't really have the time right now to give you a proper example.  If someone else has not posted something more useful by then, I'll try to get back to you after the storm passes.

ADO is essentially the same as DAO, just with slightly different parameters for the methods, and the absolute necessity of a user-built connection.  A Connection object will be needed for anything else to work.  You can substitute a valid connection string for an actual Connection object with certain items (like recordsets), but I usually recommend creating the object anyways.  It is easier to change one connection string of a global object than 100 connection strings of all the local shortcuts you use.  Look in the Object Browser (F2 from the VB code window) for ADODB.Connection.  Make sure you pick the ADODB version for any objects you are looking up...DAO has the same object names with some slight differences.

Once you have the Connection created, you can use it with any Command, Recordset, etc., to contact the target DB.  The hardest part is the connection string, and there are plenty of sites out there to help you build them correctly.  When I have a little more time I'll come back and give them to you.  :)
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 450 total points
ID: 11990584
Ok, here's a great code example of how to use ADO.  This also shows two examples of how to run queries, one with a command object, the other from a connection object.  You can look up the particulars of the parameters for each in the object library.  For help with connection strings, check out this link:

http://www.able-consulting.com/ADO_Conn.htm

It has examples for most of the common data providers you can use, including both sets for Oracle.

    Dim Cnxn As ADODB.Connection
    Dim cmdChange As ADODB.Command
    Dim strCnxn As String
    Dim strSQLChange As String
    Dim strSQLRestore As String
   
     ' Define two SQL statements to execute as command text
    strSQLChange = "UPDATE Titles SET Type = 'self_help' WHERE Type = 'psychology'"
    strSQLRestore = "UPDATE Titles SET Type = 'psychology' WHERE Type = 'self_help'"
   
     ' Open connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';Initial Catalog='Pubs';Integrated Security='SSPI';"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn
   
     ' Create and execute command object
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = Cnxn
    cmdChange.CommandText = strSQLChange
    cmdChange.Execute
   
    ' Use the Connection object's execute method to
    ' execute SQL statement to restore data and trap for
    ' errors, checking the Errors collection if necessary
    Cnxn.Execute strSQLRestore, , adExecuteNoRecords
0
 

Author Comment

by:joelhoffman
ID: 12016756
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.
0
 

Author Comment

by:joelhoffman
ID: 12016965
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?
0
 

Author Comment

by:joelhoffman
ID: 12016985
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.
0
 

Author Comment

by:joelhoffman
ID: 12019700
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?
0
 

Author Comment

by:joelhoffman
ID: 12022296
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?
0
 

Author Comment

by:joelhoffman
ID: 12022393
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:

http://www.experts-exchange.com/Databases/MS_Access/Q_21104484.html

I'd appreciate your continued help,

Joel
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now