Key violation for query

[FrankPV]

I'm having a problem with a query executing in my application. I'm using an Access Front end to an SQL Server 2000 database. I recently upsized the tables to SQL because we are adding users. After upsizing and specifying users, roles, and permissions everything worked fine except for one function. There is a function to copy an item which runs two append queries - one to copy the header information and the orher to copy the ingredients. This function works fine for me. I have administrative privileges. When another user tries to run these queries and copy an item they get an error. The error states that the records cannot be copied due to key violations. I don't know why I am able to run these queries and another user cannot. I also don't understand why the error is a key violation. Any help would be appreciated.

Thanks.

 

[jiqjaq]

First, check to make sure that the user has Insert privilege on the tables being appended. If they do, try to isolate the problem by running another query that only appends to one of the tables, probably the header.

Once you can get to a specific table, it's much easier to determine exactly what the problem really is. Access doesn't usually return the full message that you'd get if you ran the query directly in SQL Server.

 

[FrankPV]

Thanks for your suggestion. I had a chance to test this over the past few days. The user can access all the tables, add records individually, and edit all records on all tables. They just cannot run this query. I gave a user administrative privileges and they were able to run the query just fine. When I take away administrative rights I get the key violation error again. Just doesn't make any sense to me. Thanks.