chpicker
Programmer
- Apr 10, 2001
- 1,316
Here is my situation. I have a constantly growing table of records with a date/time field. The records do NOT appear in the database anywhere near correct date/time order since some get the current date/time placed on them while others are for hours or days in the future. I need a "view" of this table that is within 6 hours of "now", giving a 12 hour span.
The "old" way that was inherited from the previous developers was to use a second table that contained a mirror of the records for the current day. Once a day we have to clean out this table by getting rid of the old records and bringing in the new ones. It also has to be constantly monitored for newly entered ones that should be in it. Basically, it's a mess.
I've come to the conclusion that a View is the answer. Therefore, I used the View wizard to create one with a filter expression of "Time BETWEEN datetime()-21600,datetime()+21600" and ORDERed by the date/time field. This has given me the information I want, and in a table with 100000 records it takes 0.16 seconds to give it to me. Plenty fast enough for my liking. So I assign the controlsource of a grid to this View, and it looks good.
Now for the tough part. The grid needs to be refreshed every second or two to provide a real-time view into the table. Any new records added to the table that are within range need to show up, and those that have gone out of range should disappear. It needs to happen automatically (using a timer object). What are the commands I would use to have the grid re-execute the SQL statement for the View? Anyone have any other suggestions for how to better accomplish this?
The "old" way that was inherited from the previous developers was to use a second table that contained a mirror of the records for the current day. Once a day we have to clean out this table by getting rid of the old records and bringing in the new ones. It also has to be constantly monitored for newly entered ones that should be in it. Basically, it's a mess.
I've come to the conclusion that a View is the answer. Therefore, I used the View wizard to create one with a filter expression of "Time BETWEEN datetime()-21600,datetime()+21600" and ORDERed by the date/time field. This has given me the information I want, and in a table with 100000 records it takes 0.16 seconds to give it to me. Plenty fast enough for my liking. So I assign the controlsource of a grid to this View, and it looks good.
Now for the tough part. The grid needs to be refreshed every second or two to provide a real-time view into the table. Any new records added to the table that are within range need to show up, and those that have gone out of range should disappear. It needs to happen automatically (using a timer object). What are the commands I would use to have the grid re-execute the SQL statement for the View? Anyone have any other suggestions for how to better accomplish this?
<> 1