insert trigger problem

[GerryGoldberg]

I have written an insert trigger that simply takes the inserted data (from the inserted table and inserts into another table, e.g. tblHistory). This trigger seems to work fine when I insert target records using SQL 7 Server Enterprise Manager. I insert a row in the target table (tblTrans) and it appears in the tblHistory table.

However, when I try to do the same thing in Access 97 (no code involved--I just try to insert a row to my linked target table in datasheet view), I get a SQL error:
ODBC--Insert on linked table tblTrans failed

What am I doing wrong in Access? Any suggestions?

Thanks,

Gerry Goldberg

 

[tlbroadbent]

Some things to check.

Does the TRIGGER have SET NOCOUNT ON as one of it's first commands? If not, add it to prevent the return of records when none are expected.

Does tblTrans have a unique index, primary key or timestamp column? Access cannot insert into a linked SQL table if it can't uniquely identify a row.

Does the login used to LINK the table have INSERT permissions in both tables, tblTrans and tblHistory? Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[GerryGoldberg]

Set nocount on is true, both tables have a unique index and both tables have insert permissions. Actually, the insert and the trigger work perfectly when performed using the SQL Enterprise Manager. The process doesn't work when I try to do the same thing using Access. I even re-linked both tables but had the same result.

Thanks,

Gerry Goldberg

 

[tlbroadbent]

Inserting in SQL Server from Enterprise Manager or Query Analyzer is different than inserting with JET and ODBC. The different results are not unusual.

Are you using the same login to LINK the SQL tables as you use in Enterprise Manager? Can you update an existing column in the linked table? Can you remove the trigger and update the linked table in Access? Does the error message provide any aditional information? Let's try to narrow this down to a likely cause.

I just had a thought. (Now that's dangerous!) Does the linked table have a clusered index? If not, can you modify the unique index to make it clustered also? I remember having trouble with linked tables until I added clustered indexes. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[GerryGoldberg]

I am using the same login for access that I used for Enterprise Manager. I can update the linked table and, after removing the trigger, I can insert into the linked table. The linked table has a clustered index. Still no luck.

Thanks,

Gerry GOldberg

 

[tlbroadbent]

There is a known bug documented in the article below. It is very specific in application.

1) Non SA login
2) Directly accessing Inserted table
3) SQL 7, SP1 or SP2 installed

Does it apply in your case?

http://support.microsoft.com/support/kb/articles/Q278/6/71.ASP

Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[GerryGoldberg]

I don't think that this exactly describes my problem but I'll investigate it just the same. Thanks for all of your help.

Gerry Goldberg