M
Maury Markowitz
I had to solve this problem twice now, and I'll share my solutions
with you all, maybe it will save someone some time in the future.
I have a moderately expensive query connected to a form will all sorts
of conditional formats and such. The data under the form can be
changed by any one of our users, and those changes need to be
reflected into everyone's copy fairly quickly - 5 seconds is good
enough. Now the obvious solution would be to use a timer and call
refresh, but this form is too expensive to do this, a dozen people
calling the form every 5 seconds will tie up the server.
The first time I solved this I used a timer handler that had a copy of
the recordset's SQL, as text. When the timer fired I would WHILE over
the forms RecordsetClone and write down the maximum modifiedAt (a
column in the db). I would then modify the WHERE clause of the SQL
that I new the form used... "SELECT MAX(modifiedAt) FROM ... WHERE
modifiedAt > '" & theNewestRecord & "'" If that returned no rows, then
nothing had changed, and I didn't need to do the Refresh. Very good!
But more recently a number of users have asked for little tweaks to
the display, selecting different rows or ordering them differently.
Originally I used SQL text and tied that to Me.RecordSource, but as
the requests got more complex the number of different paths though the
sql got messy. So I translated these into stored-procs functions
instead, and tied Me.RecordSource to one of them.
But that left me with a quandary. The SQL for the form could be any
one of a number of different cases, so my update-check would have to
have the same cases. This was ugly. So I found another solution - not
perfect, but it seems to work OK. What I do is this...
checkSql = "SELECT isnull(MAX(a.modifiedAt), 0) as modifiedAt FROM ("
& Me.RecordSource & ") a WHERE a.modifiedAt>'" & lastUpdate & "'"
The downside to this approach is that it has to run the whole query
and then filters out all but one of the rows. It would be much better
if that WHERE was inside the brackets, not outside, but that would
require the
Does anyone have another solution to the problem that's even better? I
was looking at Filter on the RecordsetClone, but didn't get very far.
Maury
with you all, maybe it will save someone some time in the future.
I have a moderately expensive query connected to a form will all sorts
of conditional formats and such. The data under the form can be
changed by any one of our users, and those changes need to be
reflected into everyone's copy fairly quickly - 5 seconds is good
enough. Now the obvious solution would be to use a timer and call
refresh, but this form is too expensive to do this, a dozen people
calling the form every 5 seconds will tie up the server.
The first time I solved this I used a timer handler that had a copy of
the recordset's SQL, as text. When the timer fired I would WHILE over
the forms RecordsetClone and write down the maximum modifiedAt (a
column in the db). I would then modify the WHERE clause of the SQL
that I new the form used... "SELECT MAX(modifiedAt) FROM ... WHERE
modifiedAt > '" & theNewestRecord & "'" If that returned no rows, then
nothing had changed, and I didn't need to do the Refresh. Very good!
But more recently a number of users have asked for little tweaks to
the display, selecting different rows or ordering them differently.
Originally I used SQL text and tied that to Me.RecordSource, but as
the requests got more complex the number of different paths though the
sql got messy. So I translated these into stored-procs functions
instead, and tied Me.RecordSource to one of them.
But that left me with a quandary. The SQL for the form could be any
one of a number of different cases, so my update-check would have to
have the same cases. This was ugly. So I found another solution - not
perfect, but it seems to work OK. What I do is this...
checkSql = "SELECT isnull(MAX(a.modifiedAt), 0) as modifiedAt FROM ("
& Me.RecordSource & ") a WHERE a.modifiedAt>'" & lastUpdate & "'"
The downside to this approach is that it has to run the whole query
and then filters out all but one of the rows. It would be much better
if that WHERE was inside the brackets, not outside, but that would
require the
Does anyone have another solution to the problem that's even better? I
was looking at Filter on the RecordsetClone, but didn't get very far.
Maury