Help with Queries and codes

W

WSUHoss3

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
 
L

Larry Linson

WSUHoss3 said:
I am running an autoexec macro that runs some update queries after I run
Click to expand...
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

Please clarify... "after I run code that...". Do you mean the AutoExec runs
the code? AutoExec runs before you get a chance to open and run any code
manually. What is the code? I think there's more to this than I see in the
question.

The answer may be as simple as "run the queries from the code rather than
from the macro" but we'll need some information to make any valid
suggestion.

Larry Linson
Microsoft Access MVP
 
W

WSUHoss3

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.
 
W

WSUHoss3

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.
 
L

Larry Linson

Just about the only macros I use these days (and I rarely need the
functionality, so that is not very often) are AutoKeys macros. Apparently,
the macro does not wait for the Query to complete before moving on to its
next operation, as I would also have thought it should.

As Joseph Meehan suggested, I think you may be able to solve your problem by
executing the Queries from the code, rather than from the macro itself.

Larry Linson
Microsoft Access MVP



WSUHoss3 said:
Sorry, yes the autoexec runs 2 queries first that delete data in 2 tables,
Click to expand...
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.
 
W

WSUHoss3

I'll give that a try, when I run it in the code is there a way to ensure it has run before moving onto the next step?
 
W

WSUHoss3

I just tried running it in my code with the last module and it din't work. Am I perhaps doing it incorrectly?
 
W

WSUHoss3

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
 
W

WSUHoss3

Please somebody help me take a crack at this

WSUHoss3 said:
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.
Click to expand...
 
G

gandalf

It could be that Access has an write-delay of 5 seconds

you could try
PausePreview(30)
DoCmd.OpenQuery "Make ranking table"

with PausePreview

Public Sub PausePreview(pauseTime As Double)
Dim i As Long, start As Double
If Timer + pauseTime < (24 * 60 * 60) Then
start = Timer
Do
Loop Until Timer > start + pauseTime
Else
'loop would have run for 24 hours before exiting...
'could enhance to loop until either:
' A) (time > endTime), or
' B) (date >= endDate) and (time > endTime)
End If
End Sub


-----Original Message-----
Here is the code that I am running, I just set it on the
Click to expand...
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...
 
B

Brendan Reynolds

I've never used OpenQuery to execute an action query, I've always used
db.Execute (where db is a DAO database object) so I'm not sure how this
advice will apply to your situation, but I find that DbEngine.Idle often
helps with this type of timing issue. A little trial-and-error should reveal
whether it will help in your code, and where it is required.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


WSUHoss3 said:
Please somebody help me take a crack at this
Click to expand...
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.
 
Top