Cause Value of Field in SQL to Create a New Record in Access

[BigglesDad]

Hi Gurus, This is what I want to happen when I programmatically change the value of a logical field in SQL:

1. There's a trigger that runs a stored procedure.
2. The stored procedure creates a new record in a table in MS Access that is running on a different host.

The SQL is MS SQL Server 2000 and the Access is MS Access 2002.

Is it possible ...or should I find another tree to bark up?

 

[JohnDTampaBay]

Sure, this is possible. The following construct can be modified for your purposes. See BOL: Programming Triggers. Good luck!

This example creates an INSERT trigger my_trig on table my_table and tests whether column b was affected by any INSERT statements.

CREATE TABLE my_table
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JamesLean]

Also, to set up the link to the Access db, look up linked servers in BOL. Once you have that set up, you just use a four-part qualified name to reference the table.

--James

 

[BigglesDad]

OK guys. I've done some reading as you suggest. But it seems from what I've read that the Access DB has to be on the same server as the SQL DB if I want to add records to it.

Have I misunderstood?