When to requery Form after running Update query?

[deko]

I'm a little nervous about slamming my database with a dozen Update queries
in a loop that all modify RecordSources of open forms. Will the use of
DoEvents and/or a Sleep function ameliorate any risk involved in doing this?
Should I include a Requery in the loop after executing each query?

For example:

For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
Next

I have about a dozen queries that I want to run in a loop like this, all
updating (mainly deleting records from) the different tables that are part
of (i.e. included in the queries that are) the RecordSource for currently
open forms.

Should I do this:

For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
DoEvents
Forms("frmMain").Requery
Next

What about this:

Private Declare Sub Sleep _
Lib "kernel32" ( ByVal dwMilliseconds As Long )
For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
Forms!frm0.Requery
DoEvents
Sleep 500
Forms("frmMain").Requery
Next

Other options?

Thanks in Advance.

[Wayne Morgan]

The DoEvents in the loop is probably a good idea, but won't cause the form
to show the changes. If you run an Update Query, the DoEvents may let the
form's auto refresh run to show those changes, but deleted or added records
won't show until you requery. One exception to this may be that the refresh
of the form will cause a deleted record to show as #Deleted.

The Sleep function will cause Access to pause and release CPU time to other
applications that may be running, it won't help within Access.

Depending on how long it takes to run all of the queries, I would probably
just do one requery after exiting the loop.

If these queries are dependent on each other (i.e. you want them all for
complete or roll the whole thing back if one of them fails), you may want to
consider wrapping them in a transaction. This may also help with the form
since the changes won't actually be committed to disk until you commit the
transaction, essentially writing all of the changes at one time.

--
Wayne Morgan
Microsoft Access MVP
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:y8******************@newssvr13.news.prodigy.c om...

I'm a little nervous about slamming my database with a dozen Update
queries
in a loop that all modify RecordSources of open forms. Will the use of
DoEvents and/or a Sleep function ameliorate any risk involved in doing
this?
Should I include a Requery in the loop after executing each query?

For example:

For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
Next

I have about a dozen queries that I want to run in a loop like this, all
updating (mainly deleting records from) the different tables that are part
of (i.e. included in the queries that are) the RecordSource for currently
open forms.

Should I do this:

For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
DoEvents
Forms("frmMain").Requery
Next

What about this:

Private Declare Sub Sleep _
Lib "kernel32" ( ByVal dwMilliseconds As Long )
For each varQry in Array("qryDeleteOldTransactions", _
"qryDeleteClient Acct", etc., etc.)
db.Execute varQry
Forms!frm0.Requery
DoEvents
Sleep 500
Forms("frmMain").Requery
Next

Other options?

Thanks in Advance.

[deko]

> The DoEvents in the loop is probably a good idea, but won't cause the form

to show the changes. If you run an Update Query, the DoEvents may let the
form's auto refresh run to show those changes, but deleted or added records won't show until you requery. One exception to this may be that the refresh of the form will cause a deleted record to show as #Deleted.

The Sleep function will cause Access to pause and release CPU time to other applications that may be running, it won't help within Access.

Depending on how long it takes to run all of the queries, I would probably
just do one requery after exiting the loop.
Sounds good. From what you've suggested, I'm considering this:

For Each varQry in Array([UpdateQry list])
db.Execute varQry
DoEvents
Sleep 100
Next
Forms("frmMain").Requery

The Updating in question does not need to be quick - just safe and
reliable - so I figured I'd put the DoEvents and Sleep in there. The only
reason I thought I needed to Requery the form after executing each Update
query is because I thought I might run into a "the data has changed" error
or some other record locking issue - since I am updating the RecordSource of
an open form multiple times. But if I only need to Requery once, that's
fine with me.
If these queries are dependent on each other (i.e. you want them all for
complete or roll the whole thing back if one of them fails), you may want to consider wrapping them in a transaction. This may also help with the form
since the changes won't actually be committed to disk until you commit the
transaction, essentially writing all of the changes at one time.

At this point, the queries are not dependent, but thanks for the heads up.

[Wayne Morgan]

I would remove the Sleep, it will only slow things down. As stated, Sleep
will allow CPU cycles for other programs, it won't help within Access itself
and the DoEvents will do both.

--
Wayne Morgan
MS Access MVP
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:hC*****************@newssvr14.news.prodigy.co m...

The DoEvents in the loop is probably a good idea, but won't cause the
form
to show the changes. If you run an Update Query, the DoEvents may let the
form's auto refresh run to show those changes, but deleted or added

records

won't show until you requery. One exception to this may be that the

refresh

of the form will cause a deleted record to show as #Deleted.

The Sleep function will cause Access to pause and release CPU time to

other

applications that may be running, it won't help within Access.

Depending on how long it takes to run all of the queries, I would
probably
just do one requery after exiting the loop.

Sounds good. From what you've suggested, I'm considering this:

For Each varQry in Array([UpdateQry list])
db.Execute varQry
DoEvents
Sleep 100
Next
Forms("frmMain").Requery

[deko]

> I would remove the Sleep, it will only slow things down. As stated, Sleep

will allow CPU cycles for other programs, it won't help within Access itself and the DoEvents will do both.

10-4. There's no sense in slowing things down for no reason.

Thanks for the help!