set querydefs pauses

[Brad P]

Using the following code in a Timer event that runs every 2 minutes.

Set db = CurrentDb
Set qryAlarm = db.QueryDefs("qry_AlarmMonitor")
qryAlarm.Parameters(0) = Forms![frm_StaffInitials]![Initials]
Set rstAlarm = qryAlarm.OpenRecordset(dbOpenDynaset)


The 2nd line pauses for a few seconds, causing the whole application to
freeze. But any key or mouse sequences carried out during this 2-3 second
freeze resumes once the timer event is complete. This pause can be very
annoying. The underlying table has 50,000+ records. However, if the table
has 5-8000 records, there is no noticeable pause. Any suggestions?

Thanks

 

[Michel Walsh]

Hi,

Change the scope of qryAlarm so you don't need to "open" it each time
you loop through. That last two lines are what are all what is required to
refresh the recordset.


Hoping it may help,
Vanderghast, Access MVP

 

[Brad P]

Hi,

Change the scope of qryAlarm so you don't need to "open" it each time
you loop through. That last two lines are what are all what is required to
refresh the recordset.


Hoping it may help,
Vanderghast, Access MVP

Thanks for your reply....but once the Timer event is finished, will the
recordset sill be open? I guess as long as I don't close it perhaps.

I'll try it
Thanks
Brad

 

[Michel Walsh]

Hi,


Again, it depends of its scope. If you dim it for the form scope, it
will survive the event, but if you dim it in the even subroutine, it will
(should) die once the event ends.


Vanderghast, Access MVP