Help with code and queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running an autoexec macro that runs some update queries after I run code that manually updates portions of the tables. The problem is that the update queries are running before the code is finished. Is there anway to slow down one or the other? Thanks

Sorry, yes the autoexec runs 2 queries first that delete data in 2 tables, then it runs a query that imports data from a linked table to one of the tables that was cleared out. After that it proceeds to run 6 modules that do the same thing with different variables (they rank each one of my colums based on the criteria of each). After that it runs one more update query to take all that information and put it in a new table and runs one final module to rank all the prior rankings based on weighted averages. The problem I'm running into is that it doesn't seem to update the tables in time before the last update query runs so it displays all zeros for rankings. I know it works because I can go step by step slowly and have it work correctly. I hope this makes more sense. Thanks for your help.


Here is the code that I am running, I just set it on the form Load event and it is still to quick...

Private Sub Form_Load()
DoCmd.SetWarnings (Warningsoff)
DoCmd.OpenQuery "Delete coach"
DoCmd.OpenQuery "qry_Delete Rankings"
DoCmd.OpenQuery "Update Coach"

'1call
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![1 Call Resolution %]
Rank = 1
Do
If (rstB![1 Call Resolution %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
rstA![1 Call Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'absences

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Absences %]
Rank = 1
Do
If (rstB![Absences %]) < points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
rstA![Absence Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close



'Adjustment Accuracy
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Adj Accuracy %]
Rank = 1
Do
If (rstB![Adj Accuracy %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF

rstA![Adj Acc Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'Compliance Ranking
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Compliance %]
Rank = 1
Do
If (rstB![Compliance %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![Compliance Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close


'CRT Ranking

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Agent CRT]
Rank = 1
Do
If (rstB![Agent CRT]) < points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![CRT Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close


'QA National

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Average GetRReal Quality Score]
Rank = 1
Do
If (rstB![Average GetRReal Quality Score]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF

rstA![QA Nat Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'QA Ops

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Average GetRReal Ops Score]
Rank = 1
Do
If (rstB![Average GetRReal Ops Score]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![QA Ops Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

For x = 1 To 1000000
Next x

DoCmd.OpenQuery "Make ranking table"

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach Rankings", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach Rankings", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Total Points]
Rank = 1
Do
If (rstB![Total Points]) < points Then
Rank = Rank + 1
If Rank <= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close



End Sub
 
Please somebody help me take a crack at this

WSUHoss3 said:
I am running an autoexec macro that runs some update queries after I run code that manually updates portions of the tables. The problem is that the update queries are running before the code is finished. Is there anway to slow down one or the other? Thanks

Sorry, yes the autoexec runs 2 queries first that delete data in 2 tables, then it runs a query that imports data from a linked table to one of the tables that was cleared out. After that it proceeds to run 6 modules that do the same thing with different variables (they rank each one of my colums based on the criteria of each). After that it runs one more update query to take all that information and put it in a new table and runs one final module to rank all the prior rankings based on weighted averages. The problem I'm running into is that it doesn't seem to update the tables in time before the last update query runs so it displays all zeros for rankings. I know it works because I can go step by step slowly and have it work correctly. I hope this makes more sense. Thanks for your help.


Here is the code that I am running, I just set it on the form Load event and it is still to quick...

Private Sub Form_Load()
DoCmd.SetWarnings (Warningsoff)
DoCmd.OpenQuery "Delete coach"
DoCmd.OpenQuery "qry_Delete Rankings"
DoCmd.OpenQuery "Update Coach"

'1call
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![1 Call Resolution %]
Rank = 1
Do
If (rstB![1 Call Resolution %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
rstA![1 Call Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'absences

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Absences %]
Rank = 1
Do
If (rstB![Absences %]) < points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
rstA![Absence Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close



'Adjustment Accuracy
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Adj Accuracy %]
Rank = 1
Do
If (rstB![Adj Accuracy %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF

rstA![Adj Acc Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'Compliance Ranking
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Compliance %]
Rank = 1
Do
If (rstB![Compliance %]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![Compliance Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close


'CRT Ranking

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Agent CRT]
Rank = 1
Do
If (rstB![Agent CRT]) < points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![CRT Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close


'QA National

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Average GetRReal Quality Score]
Rank = 1
Do
If (rstB![Average GetRReal Quality Score]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF

rstA![QA Nat Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

'QA Ops

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Average GetRReal Ops Score]
Rank = 1
Do
If (rstB![Average GetRReal Ops Score]) > points Then
Rank = Rank + 1
If Rank >= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![QA Ops Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close

For x = 1 To 1000000
Next x

DoCmd.OpenQuery "Make ranking table"

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach Rankings", dbOpenDynaset)
Set rstB = dbs.OpenRecordset("Coach Rankings", dbOpenDynaset)

last = 1
rstA.MoveFirst
Do
rstA.Edit
rstB.MoveFirst
points = rstA![Total Points]
Rank = 1
Do
If (rstB![Total Points]) < points Then
Rank = Rank + 1
If Rank <= last Then
last = Rank + 1
End If
End If
rstB.MoveNext
Loop Until rstB.EOF
'End If
rstA![Ranking] = Rank
rstA.Update
rstA.MoveNext
Loop Until rstA.EOF


rstA.Close
rstB.Close



End Sub
Click to expand...
 
WSUHoss3 said:
I am running an autoexec macro that runs some update queries after I run code that manually updates portions of the tables. The problem is that the update queries are running before the code is finished. Is there anway to slow down one or the other? Thanks

Sorry, yes the autoexec runs 2 queries first that delete data in 2 tables, then it runs a query that imports data from a linked table to one of the tables that was cleared out. After that it proceeds to run 6 modules that do the same thing with different variables (they rank each one of my colums based on the criteria of each). After that it runs one more update query to take all that information and put it in a new table and runs one final module to rank all the prior rankings based on weighted averages. The problem I'm running into is that it doesn't seem to update the tables in time before the last update query runs so it displays all zeros for rankings. I know it works because I can go step by step slowly and have it work correctly. I hope this makes more sense. Thanks for your help.


Here is the code that I am running, I just set it on the form Load event and it is still to quick...

Private Sub Form_Load()
DoCmd.SetWarnings (Warningsoff)
DoCmd.OpenQuery "Delete coach"
DoCmd.OpenQuery "qry_Delete Rankings"
DoCmd.OpenQuery "Update Coach"

'1call
Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
Click to expand...
[snip]

Try using the Execute method instead of OpenQuery:

Private Sub Form_Load()
Dim dbs As Database
Dim rstA As Recordset

Set dbs = CurrentDb()
dbs.Execute "Delete coach"
dbs.Execute "qry_Delete Rankings"
dbs.Execute "Update Coach"

'1call
Set rstA = dbs.OpenRecordset("Coach", dbOpenDynaset)
, , .

Be sure to read up on the Execute method in Help. It has
additional options that may be useful to you, particularly
the dbFailOnError option and the fact that it sets the db
property RecordsAffected.
 
Back
Top