Determine when record is added to a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Lempel - 13 Dec 2005 19:57 GMT

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.

Reply to this Message

Klatuu - 13 Dec 2005 23:30 GMT

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.

> 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.
[quoted text clipped - 7 lines]
> (record appended) when it happens, and then execute my code. I would like
> to avoid having to query the table every minute.

Reply to this Message

Mike Lempel - 14 Dec 2005 05:37 GMT

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

> What is the other App written in? Would it have the capability to kick of
> a
[quoted text clipped - 18 lines]
>> like
>> to avoid having to query the table every minute.

Reply to this Message

Klatuu - 14 Dec 2005 13:50 GMT

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.

> 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
[quoted text clipped - 33 lines]
> >> like
> >> to avoid having to query the table every minute.

Reply to this Message

Graham R Seach - 14 Dec 2005 12:04 GMT

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
---------------------------

>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
[quoted text clipped - 7 lines]
> event (record appended) when it happens, and then execute my code. I
> would like to avoid having to query the table every minute.

Reply to this Message

Mike Lempel - 14 Dec 2005 22:48 GMT

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

> Mike,
>
[quoted text clipped - 20 lines]
>> event (record appended) when it happens, and then execute my code. I
>> would like to avoid having to query the table every minute.

Reply to this Message

Graham R Seach - 14 Dec 2005 23:57 GMT

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

> 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
[quoted text clipped - 27 lines]
>>> event (record appended) when it happens, and then execute my code. I
>>> would like to avoid having to query the table every minute.

Reply to this Message