Linked table creaes key violation error 1

[hi2jenny]

Hi, I have a form that consists several buttons. One is to delete a table A, then add records to several tables and eventually it will do a join and insert records to table A, and display a report. It always works while all tables reside in the same Access database. Now we are trying to migrate to SQL server but not ready to get rid of Access yet. So we exported all tables to SQL server and created linked tables. We can open tables directly from Access without any problem. It shows the contents as the SQL database But when we tried to open the form and run the same button as before, we got an error:

Microsoft Access can't delete 0 record(s) in the delete query due to key violations and n record(s) due to lock violations.

We have no idea why this happens. Most of the time, it worked the first time when we clicked on the button. But we got the message when we clicked on the same button the second time. If we clicked "Yes" then it will append new records. There will be duplicate records since it doesn't delete the existing ones.

Did any of you encounter a similar problem before? Thanks in advance!


Jenny

 

[markgrizzle]

It sounds like your sql server tables need a primary key (timestamp). Without one on each table, access won't be able to perform the actions you want.

 

[hi2jenny]

True I didn't have a primary key but I just used the auto number field as the primary key of the table. But the problem still occurs. Any other reason you could think of?

 

[markgrizzle]

All out of ideas here. GL.

 

[PHV]

Have you added a TimeStamp field to all the SQL Server tables and refreshed all the links ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hi2jenny]

Thanks, PHV. It works! I guess I didn't refresh the linked tables before.