Ok.....I give. I was trying to avoid this as I have received some excellent help/tips from ajetrumpet on speeding up my code. I added SQL statements in place of entire tables where I can. However, this thing is still taking 8 hours to run! I have tried to pare down the code as much as possible. Can someone please take a look at this and give me some times on speeding up the execution? the table "TblOracleEmp" is a linked table that exists on a server, but normal access to the table is as quick as if it were local. All other tables are local tables. Thank you in advance...
First loop:
Code:
Do While Not rs3.EOF
Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
rs3.MoveLast
rs3.MoveFirst
vEmplID = rs3("A$EMPLOYEE_NUMBER")
vDate = rs3("DATE_IN_JOB")
vLocation = rs3("LOCATION")
vPayType = rs3("PAY_TYPE")
Call funAuditTrail(vEmplID, vLocation, vDate, vPayType)
rs3.MoveNext
Loop
Function funAuditTrail:
Code:
mySQLrs = "SELECT * FROM tblAudit WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs = db.OpenRecordset(mySQLrs, dbOpenDynaset)
mySQLrs = "SELECT * FROM TblOracleEmp WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs1 = db.OpenRecordset(mySQLrs, dbOpenDynaset)
rs1.MoveLast
rs1.MoveFirst
rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
rs1.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
If rs.RecordCount = 0 Then
If vCurrPayType = "Monthly Salary" Then
'Execute SQL Statement referenced in next block of code
rs.Close
rs1.Close
Exit Function
End If
Else
rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
vLocation = rs("LOCATION")
vPayType = rs("PAY_TYPE")
vAppendDate = rs("RecordUpdateDate")
While Not rs.NoMatch
If vAppendDate < rs("RecordUpdateDate") Then
vAppendDate = rs("RecordUpdateDate")
End If
rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
Wend
While Not rs.NoMatch
vPosition = rs("JOB_NAME")
vPositionDate = rs("DATE_IN_JOB")
vCurrAppendDate = rs("RecordUpdateDate")
If vCurrAppendDate >= vAppendDate Then
If vPositionDate < vCurrPositionDate Then
'Execute SQL Statement referenced in next block of code
End If
If vLocation <> vCurrLoc Then
'Execute SQL Statement referenced in next block of code
End If
If vPayType <> vCurrPayType Then
'Execute SQL Statement referenced in next block of code
End If
End If
rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
Wend
End If
rs.Close
rs1.Close
SQL Code:
Code:
vDOWNLOAD_DT = rs1("DOWNLOAD_DT")
vACTUAL_TERM_DATE = rs1("ACTUAL_TERM_DATE")
If IsNull(vACTUAL_TERM_DATE) Then
vACTUAL_TERM_DATE = #1/1/9999#
End If
vPAY_CHANGE_DATE = rs1("PAY_CHANGE_DATE")
vDATE_IN_JOB = rs1("DATE_IN_JOB")
vTHIS_PERIOD_START_DATE = rs1("THIS_PERIOD_START_DATE")
vDATE_OF_HIRE = rs1("DATE_OF_HIRE")
vEMPLOYEE_NUMBER = rs1("A$EMPLOYEE_NUMBER")
vFULL_NAME = rs1("A$FULL_NAME")
vJOB_NAME = rs1("JOB_NAME")
vPAY_CHANGE_REASON = rs1("PAY_CHANGE_REASON")
vBusiness_Title = rs1("Business_Title")
vSUPERVISOR_FULL_NAME = Nz(rs1("SUPERVISOR_FULL_NAME"))
If vSUPERVISOR_FULL_NAME = "" Then
vSUPERVISOR_FULL_NAME = "N/A"
End If
vASSIGNMENT_STATUS = rs1("ASSIGNMENT_STATUS")
vLocation = rs1("LOCATION")
vPERSON_TYPE_USAGE = rs1("PERSON_TYPE_USAGE")
vPAY_TYPE = rs1("PAY_TYPE")
mySQL = "INSERT INTO tblAudit (DOWNLOAD_DT, [A$EMPLOYEE_NUMBER], [A$FULL_NAME], ASSIGNMENT_STATUS, DATE_OF_HIRE," & _
"JOB_NAME, LOCATION, PERSON_TYPE_USAGE, SUPERVISOR_FULL_NAME, THIS_PERIOD_START_DATE, ACTUAL_TERM_DATE," & _
"PAY_TYPE, PAY_CHANGE_DATE, PAY_CHANGE_REASON, DATE_IN_JOB, Business_Title) VALUES (#" & vDOWNLOAD_DT & "#, " & vEMPLOYEE_NUMBER & "," & _
"'" & vFULL_NAME & "', '" & vASSIGNMENT_STATUS & "', #" & vDATE_OF_HIRE & "#, '" & vJOB_NAME & "', '" & vLocation & "'," & _
"'" & vPERSON_TYPE_USAGE & "', '" & vSUPERVISOR_FULL_NAME & "', #" & vTHIS_PERIOD_START_DATE & "#, #" & vACTUAL_TERM_DATE & "#," & _
"'" & vPAY_TYPE & "', #" & vPAY_CHANGE_DATE & "#, '" & vPAY_CHANGE_REASON & "', #" & vDATE_IN_JOB & "#, '" & vBusiness_Title & "')"
CurrentDb.Execute mySQL, dbFailOnError
I know this is a lot and I thank you for any tips/insight on speed of execution you can provide.