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
Dim strQryName As String
strQryName = "Dump Work Order Master Tag Table - Table Build"
DoCmd.OpenQuery strQryName, acNormal, acEdit
QUESTIONS: How can we suppress ALL messages and how can we get this query to run more quickly?