Live Table Data Updates

[tfaiers]

I've been thinking over this problem and I'm not sure the best way of resolving it, if it's possible even.

I've made a frontend database that displays data from a database log created by a third party program. The program updates the database log everytime an event occurs and I simply (not sure that's the best word to use ) want to try and display the data as up-to-date as possible.

I've been using the Refresh function if the database detects that the last record has changed, but set this on a 1000ms cycle which as the database log file grows causes some issues with the refreshing going quicker than the query behind it.

I then created a temporary table that appends the last active 600 log entries and the display queries work on that which is a lot faster and still using a timer event, I check for 'new records' every couple of seconds and append or update data in the temporary table if things have changed.

Not the best methods. I'm not sure if Access is able to display 'live' data and if not, which is the best way of displaying the most up-to-date data onscreen or the fastest refresh method (hopefully without screen flickers).

The other issue I noted was that if a few new events occur with my last used method, the update/append query only takes the last record updated although with a little reworking I'm sure I can use a marker of some sort to determine the last known record and update/append the new data.

So, really, the key question is, can Access show live and instantly updating data from a recordsource that's been updated from elsewhere?

Thank You
Tony

 

[gemma-the-husky]

how often does the log change?

how often do you NEED to show changes?

eg - checking for changes every second seems unnecessarily frequent. Could you not just check the file datestamp say every minute. If the datestamp hasn't changed, there is no new data.

 

[tfaiers]

The filestamp reference is an interesting idea, but the frequency issue is a must as the program needs to provide 'live' feedback to show portable radio operators as they log in/out and talk over the air so there could be very infrequent activity or a burst of conversations over a short period of time.

When someone calls through to the operator, the requirement is to instantly display information relating to that user against three queries - users currently logged on or who have actively talked during the last 12 hour period - users currently logged off - users currently 'talking'.

The most important is the instant visual feedback to who the operator is talking to, hence the 'live' requirement.

 

[gemma-the-husky]

I am not sure about the timing aspect of interrogating a large log file in real time.

so does the "capture" program HAVE to write to a single log file?
how many conversations do you have?
how frequently could you recreate a new log file?

i would add some timers to your app, to see where any bottlenecks oocur

eg - this windows function is accurate to 1ms, unlike access vba timer function, which only works to a second.

Public Declare Function my_gettickcount _
Lib "kernel32" Alias "GetTickCount" () As Long

dim timetaken(100) as long

timetaken(1) = my_gettickcount

 

[tfaiers]

The capture program can only write to one log file although you could close the program, wipe the log file and re-run the program to keep the log file down to a minimum - the only problem with that is everytime you do so, you loose the last 12 hours of data.

There are up to 500 radios online at any one time but only one conversation can actually occur with the operator at a time.

Another possibility with the log file would maybe be to programatically close the third party program from Access, query out the last 12 hours of data to a temp table, empty the primary table, reinsert the temp table data and compress the MDB file and do this every 24 hours.

I know the bottleneck currently is when the screen refreshes every couple of seconds and runs 3 queries on a recordset of 163,400 records with an equated database size of 27MB, and that's 3 weeks worth of logging.

I think I like the idea of doing a 'manual reset' every 24 hours at the quietest communications time and therefore keeping the database small, but I'm still curious as to whether there's a way of displaying 'live' data unless it's something that would have to be pushed to the next level and programmed in VB.NET or some other programming language.