troubleshooting Question

Extremely long runtime for Access query, suppressing error msgs

Avatar of OGSan
OGSanFlag for United States of America asked on
Microsoft Access
3 Comments1 Solution398 ViewsLast Modified:
Hi, Experts -
We are experiencing very long run-times with a query that ties up nearly all of our PC's resources so that we can't do anything else but wait until this Access make-table query finishes.  This usually is 6-7 hours later...!  What's worse, whoever kicks off the execution must respond to error messages issued periodically over the duration of the run.  AUGH!

The query is using ODBC connectivity to link to Oracle databases that are part of an old JDEdwards ERP system.  There are approximately 7 million rows in the database and for each row, the query must:
Rename the 112 columns that it extracts,
Reformat any date fields found using a couple of custom-built functions,
Then write the row out to a new table.
PLUS...as it is doing its thing on each row, there are occassional "Type Mismatch" errors that issue a pop-up box message - to which we click the "END" button and the query continues on its merry way.

We will be migrating the execution of this job onto a server (waiting for the server to be configured with the ODBC connectivity to the JDEdwards system) - but we want to suppress all messages - even the error messages - and just let the query run to completion and create the table.  We currently have the query tied to the click of a button, with the code behind as follows:
Private Sub btn_F4801T_Click()

On Error GoTo Err_btn_F4801T_Click

DoCmd.SetWarnings False

    Dim strQryName As String
    strQryName = "Dump Work Order Master Tag Table - Table Build"
    DoCmd.OpenQuery strQryName, acNormal, acEdit

DoCmd.SetWarnings True

Exit_btn_F4801T_Click:
    Exit Sub

Err_btn_F4801T_Click:
    MsgBox Err.Description
    Resume Exit_btn_F4801T_Click
    
End Sub

QUESTIONS:  How can we suppress ALL messages and how can we get this query to run more quickly?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros