Hi,
Through an ActiveX API I receive a stream of data, which I write into a Table.
It is a lot of data; over 50,000 records in 8 hours

I need to filter and apply conditions to every incoming record, and if it passes the filter and conditions, it needs to be presented immediately.

The API runs in a sub like so:

Public Sub Subscribe_Click()
.
.
Set db = CurrentDb
Set rsCS = db.OpenRecordset("OptionStream", dbOpenDynaset)

L = TDAComm1.Subscribe(TList, TxTDASubTypes.TDAPI_SUB_L1)

End Sub


Private Sub TDAComm1_OnL1Quote(ByVal Quote As Object)
With rsCS
.AddNew
!Symbol = Quote.Symbol
!Bid = Quote.Bid
!Ask = Quote.Ask
!Last = Quote.Last
!High = Quote.High
!Low = Quote.Low
.
.
.Update

End With
End Sub

(There are about 35 fields, I didn't post them all here)

I need to filter the records by comparing the data of every new record that comes in, to another table. If I do that in the same sub, by adding a SQL statement, and then sending the records that pass the condition/filter to a form, it adds to much overhead, it takes to much time, and the streaming data starts to delay, and disconnect.

So my question is, how can I check if a new record is added to the Table, and perform computations on it, and presenting the record on a form, if it passed the condition/filter, WITHOUT delaying the streaming of the data! ?

so from another sub, I need to check if a new record was added, and then do my computations on that record, and present results to a form.
Is this possible?

Hi pthegr8,

You could use the form Timer event to run code to requery the form displaying the filtered records every half second, that's 57600 requeries per 8 hours though!! If you have a date/time field or some numerical field, if you sort it descending then the latest filtered record will be shown first. You cannot scroll freely as the form is continually being refreshed.

You may be able to use the new data macros to trigger an event when data is added to the table to refresh the display form! Never used these macros so not sure if that's feasible or whether there's restrictions on which objects you can interact with.

You will get delays in your streaming because Access is not multi-threaded. You may get better advice at stackoverflow.com

Malc.

Thank you for taking the time to reply.
I am currently experimenting with the Form Timer function


Variable "Tradecounter" gets increased in the sub where all the records come streaming in. (the subroutine mentioned in my first post)

then every 0.5 seconds the Timer subroutine checks whether "LookForTrades" is "True".
if it is then it runs the "LookForTrades" subroutine.
In which I open the table "Optionstream" as snapshot, and step back to the first record that was added since the last time LookForTrades was run.
It is very possible that there are several new records, so it needs to keep a counter.
Then it applies some filters, and presents it on the form on a first in, first out bases on 5 boxes on the form.
I'm showing a timer, to show how long it takes to run through the records.
I haven't really been able to fully test this.

But eventhough the same table is being opened in dbopendynaset in the streaming API subroutine, and at the same time as dbopensnapshot in the "LookForTrades" subroutine, it should not delay the streaming, correct?

the way I understand this to work, is that both subroutines run in parallel.


Private Sub Form_Timer()
Dim ToD As Date
ToD = TimeValue(Now)
If ToD >= TimeValue("09:30:00 AM") And ToD <= TimeValue("16:30:00 PM") Then
Me.MH = True
If LookForTradesFinished = True Then
LookForTrades
End If
Else
Me.MH = False
End If
End Sub

Private Sub LookForTrades()
LookForTradesFinished = False
Me.LookTrades = False
Dim Tradecntr As Double
Static Trades As Double
Dim StartTime As Double
Dim EndTime As Double
Dim T As Integer
Dim U As Integer
Dim V As Integer
Dim rsOS As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

Dim Newtrade As Integer


StartTime = Timer

Me.Tradecount = TradeCounter
If TradeCounter > Trades Then
Tradecntr = TradeCounter
Newtrade = Tradecntr - Trades
Me.Trads = Trades
Me.Newtr = Newtrade
Trades = Tradecntr
Set rsOS = db.OpenRecordset("OptionStream", dbOpenSnapshot)

With rsOS
.MoveLast
V = 0
If Newtrade > 1 Then
For U = 1 To (Newtrade - 1)
.MovePrevious
V = U
If .BOF Then
Exit For
End If
Next
Else
V = 1
End If

For U = 1 To V

If !IsTrigger = True Then
If !Trade = "Ask" Or !Trade = "Above Ask" Then
If !TradeVolume <= !OpenInterest Then
strstatus = Chr(36) & !Symbol & " " & "Qty " & !TradeVolume & " at " & !Last & " " & !Trade & " " & Chr(36) & " " & !TradeAmount & " " & "Vol " & !Volume & " " & "OI " & !OpenInterest & " " & "TimeOfTrade " & !TradeTimeCalc
Else
strstatus = Chr(36) & !Symbol & " " & "Qty " & !TradeVolume & " at " & !Last & " " & !Trade & " " & Chr(36) & " " & !TradeAmount & " " & "Vol " & !Volume & " " & "OI " & !OpenInterest & " " & "QTY" & Chr(62) & "OI" & " " & "TimeOfTrade " & !TradeTimeCalc
End If


If !CallPut = "C" Then
For T = 0 To 3
ShwCtrades(T) = ShwCtrades(T + 1)
Me.Controls("SPYC" & T) = ShwCtrades(T)
Next
ShwCtrades(4) = strstatus
Me.Controls("SPYC" & 4) = strstatus
Else
For T = 0 To 3
ShwPtrades(T) = ShwPtrades(T + 1)
Me.Controls("SPYP" & T) = ShwPtrades(T)
Next
ShwPtrades(4) = strstatus
Me.Controls("SPYP" & 4) = strstatus
End If
End If
End If
.MoveNext
If .EOF Then
Exit For
End If
Next
End With
rsOS.Close
End If


EndTime = Timer
If (EndTime - StartTime) > Duration Then
Duration = EndTime - StartTime
Me.QuoteTimer = Duration
End If

Me.LookTrades = True
LookForTradesFinished = True


End Sub