Determine when record is added to a table

[Mike Lempel]

I have a link to a SQL table to which another application can append
records. When this occurs, I need to take certain actions in my Access app.
I'm handling this now by querying the table every minute (using the timer
event in an open form) to see if any new records were added since I last
checked. Records are added at unpredictable times (at intervals ranging
from several seconds to several hours), but I need to be aware of the event
within a minute or so.

Is there a better way to do this? I simply want to be alerted to the event
(record appended) when it happens, and then execute my code. I would like
to avoid having to query the table every minute.

 

[Klatuu]

What is the other App written in? Would it have the capability to kick of a
process in your mdb? Other than that, I don't know of another way to do
that. If the app you are using has other processing going on, maybe creating
a small stand alone that does nothing more that check for new records.

 

[Mike Lempel]

Thanks very much for your reply. I have no control over the other app. My
app has lots of processing going on, which is why I'm looking for another
approach. I actually thought of setting up another app which does the
checking, as you suggest, but got stuck on how the second app would notify
the first that the 'record(s) added' event had occured. Is it possible for
app2.mdb to cause a sub in app1.mdb to execute, assuming app1 is open? If
so, how? This would certainly solve my problem.

The best I could come up with was to have a one-field, one-record table
which app2 would set. But app1 would still have to check this table every
minute.

Thanks again,
Mike

 

[Graham R Seach]

Mike,

There is no native way of causing an event to fire in Access when a record
is added in SQL Server table (or any other table for that matter).

Is there a reason you can't use an SQL Server trigger?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Klatuu]

I have never interfaced to Access apps, so I don't know if or how it could be
done. Perhaps someone with experience in that area could help. I would
suggest posting a new question asking if one process in an mdb can fire a
process in another.

 

[Mike Lempel]

Thanks for the info. The reason I can't use an SQL Server trigger is that I
have no idea how, and even if I did, I'm not sure how it would help in my
situation.

Regards,
Mike

 

[Graham R Seach]

Mike,

<<...I have no idea how...>>
That's what we're here for. We can show you how.

<<...I'm not sure how it would help in my situation.>>
I don't know either. Why don't you start by telling us what you're trying to
accomplish, and we can work out the best way.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia