When to requery Form after running Update query?

When to requery Form after running Update query?

Post by deko » Thu, 27 Jan 2005 19:19:10


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.
 
 
 

When to requery Form after running Update query?

Post by Wayne Morg » Thu, 27 Jan 2005 20:06:06

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

 
 
 

When to requery Form after running Update query?

Post by deko » Thu, 27 Jan 2005 20:59:09

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

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.

to

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

When to requery Form after running Update query?

Post by Wayne Morg » Thu, 27 Jan 2005 22:07:37

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
 
 
 

When to requery Form after running Update query?

Post by deko » Thu, 27 Jan 2005 22:26:27

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

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

Thanks for the help!