"On-the-fly" Inserts triggered from a Linked SQL SERVER DB

[ItsDouglas]

I asked this question a while back and received no replies. I think I might have been a bit convuluted with my scenario. So I will try to be more concise this time.

I am creating an NT Network based work-flow system in Access 2003. I have some "local tables" in native Access format as well as table "links' to 22 SQL Server tables residing in one SQL Server database. I have created a Primary Key relationship involving three columns between one of the tables in the SQL Server database and one of the native Access tables. 2 of the three columns in the Primary Key exist in both tables while the third column is local to just the Access table (hence providing the unique aspect of the key).

Here is where I am stuck. The SQL Server database is an MS Project Server 2003 DB. The table in this discussion is one of the "View" tables Project Server maintains to provide resource Assignement information. I am accessing this table READ-ONLY through Access' Link Tables. What I need to be able to accomplish is when a record (or better said, a new assignment) is created in Proejct 2003, I need my local Access table to then create 8 rows with pre-populated data being "looked up" from yet another local Access table. (the lookup process I know how to implement) In other words, for every eligible record in the SQL SERVER DB Assignments Table, I need 8 records created in my local Access table (Local meaning it is stored on the network but not part of the SQL server DB). These 8 rows would be keyed off of the Proj_ID and UniqueAssignmentID taken from the newly created row in SQL SERVER. The third, local column in the Access table would be unique.

Obviously, referential integrity is crucial here but it is not available through Access when linking to foreign tables such as SQL SERVER DB. Yet if a record in the SQL SERVER DB table is deleted, Access needs to delete the "child" records (the 8 records I mentioned above) also.

I realize that I can have the user initiate this INSERT of 8 rows themselves, but that is not a viable option. I need to be able to programatically have Access create these 8 rows in the local table, or delete them, whenever an eligible record is created or deleted within the linked SQL SERVER table...

Hmm. Im not sure I did much better in trying to keep this simple. Anyway, any help very much appreciated!

Doug

 

[cmmrfrds]

The simplest is to run this from the sql server side. The DBA can setup the Access table(s) as linked server(s) in sql server. This way the Access table can appear as an sql server table, which can be maintained through a trigger on the sql server project table. Talk to your DBA about this.

 

[ItsDouglas]

cmm,

Thanks. I think you are right and I will meet with our dba today.

Thanks again
Doug