How to have a 'live connection' with a table?

  • Thread starter Thread starter kelly d via AccessMonster.com
  • Start date Start date
K

kelly d via AccessMonster.com

I realize that my term 'live connection' probably doesnt mean anything, but
for lack of a better term, its all I could think of. let me explain what I'm
after.

I've been asked to develop a simplistic Email and messenger style messaging
system for low-level users that arn't allowed to have Outlook on their
machines. The design itself is fairly simple, in fact it's one of the easier
apps I've ever made (I just wasnt extremely satisfied with the core of the
app of my last one but didnt know about this forum to ask anybody)
what I dont like is the idea of having to use an OnTimer to always requery to
see if a user has new messages for them cuz on slow networks requeries create
lag times and the user gets an hourglass for a couple seconds (or more
depending on the connection to the back end). if the OnTimer was set to every
minute, that wouldnt be bad but an Email and messenger system that only gets
new messages once a minute.. ah, that dont sound to good to me. my last one I
made I set to every ten seconds which was a happy middle but every 10 seconds
the hourglass cursor popped up and typing got slow. I didnt like it much at
all but the client was happy. oh well. what can you do. thats why I'm asking
this now.

Is there a way for a form on a front end to maintain something of a live
connection with a back end table so new records in the back end table reflect
immediately in the form without requerying. that way new messages could roll
in and be displayed immediately like they do in Outlook and all those other
Instant messenger systems out there. or if those systems are based on some
sort of requeries, then how do they do it without disrupting the operations
of the rest of the app?

thanks
 
How about requerying the form only when the data has changed? The following
code assumes that the table includes a date/time field that records the date
and time that the record was added to the table.

Option Compare Database
Option Explicit

Private mdtmTest As Date
Private mdb As DAO.Database
Private mrst As DAO.Recordset

Private Sub Form_Open(Cancel As Integer)
Set mdb = CurrentDb
Set mrst = mdb.OpenRecordset("SELECT Max([TestTime]) AS MaxTime FROM
tblTest")
mdtmTest = mrst.Fields("MaxTime")
Me.TimerInterval = 1000
End Sub

Private Sub Form_Timer()
mrst.Requery
If mrst.Fields("MaxTime") > mdtmTest Then
mdtmTest = mrst.Fields("MaxTime")
Me.Requery
Debug.Print "Requeried at : " & Now()
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
mrst.Close
End Sub
 
in my last project like this, I tried an idea similar to yours except I used
Dcount to see if the logged in user had more messages than when the code last
ran. would your approach run faster. cuz thats my whole agenda, to find the
method that runs the fastest so that the app doesnt slow down or stop while
the requery happens.

thanks.
 
I couldn't say, but it should be easy enough to try both methods and compare
the result.

For maximum performance, it may help to modify the example I posted to use a
saved query, and to index the field being tested, as well as any fields used
in any WHERE clause if they are not already indexed.
 
Couldnt you have a separate app running on the back end machine that sends
global prompts to the front-ends to only requery when an actual message is
present?

TonyT
 
....and another thought. Why not allow the user to toggle between
'conversation' mode (timer set to 3 seconds say - reverting back to default
if no incoming message in two minutes) and 'mail' mode (back up to a
minute). You could even get into a little bit of AI - if message out ends
with a question mark then automatically switch to 'conversation'.
 
In response to:
-Brendan: indexing, always a good optimization technique.

-Rob: some very good ideas. they would make some good features for
streamlining

-Tony: that idea sounds so advanced that I'm not even sure I'd know how to do
that. how would one go about sending global prompts to front-ends.

It did spark an idea with me though. thinking the back-end could have a sort
of 'toggle' table where each record would be the name of a user and a yes/no
field used as a toggle. when somebody sends a message, in addition to that
message being appended to the messages table sending the message can also
toggle the recipients yes/no toggle in the 'toggle' table to yes. then the
front-end would only have to requery the toggle table every couple seconds
for the yes/no status of that front-ends logged on user which would be a much
faster requery and then if the toggle is set to yes, then the front-end
requeries the messages for that user and then sets that users toggle back to
no.

any opinions on this idea would be appreciated.

thanks guys for the replies.

after I get past this little issue then I can move on to figuring out how to
simulate adding an attachment to a message :D
 
Sorry kelly, my programming isnt up to that without much research either,
just suggesting a different train of thought that maybe someone cleverer than
me could pick up on, although your second idea should allow you to speed up /
decrease timer intervals. Presumably you have a constant open-link between
front-end and back-end already to avoid negotiating .ldb slowdown issues?

TonyT
 
I'd say that your idea of a toggle table is a good idea. For maximum
efficiency I'd say to just add the username onto that table when there's a
new message, and delete after the message requery. Just slightly better as
you're only working on an indexed field instead of having to look up the
Yes/No field.
 
your right. that's an even better idea. I think I'll go with that.

Thanks.
 
Back
Top