Linked table creaes key violation error

[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

 

[FoFa]

From you description and the error message it appears you have those rows locked for some reason.

 

[hi2jenny]

What would be the possible reasons? I am the only one using the database so far. Is the lock from SQL server or access database?

 

[FoFa]

Not sure, is there a form open with the data on display?

 

[hi2jenny]

There is a report open when the button on the form is clicked. It always works the first time. After it fails the second time, I have to close the access application and open it again in order to open the form.

 

[FoFa]

Sounds like the report has a recordset open that is loocking the data.
What happens if you close all the forms (but not the DB or access) and then go back and try it (like close out to your main form or something)?

 

[hi2jenny]

Ok, I just tried. It's weird that I had to exit Access application in order to open the form successfully. If I just close the form or even just the database, it pops up with that message. I wonder what kind of lock caused that.

 

[FoFa]

Ok, Lets review.
Delete all rows from TblA
Run some stuff and populate rows back to TblA
Display report based off of TblA
Run process again, can't delete from TblA

Is that right?

 

[hi2jenny]

Yes, exactly. And I have to exit Access application, not just the database.

 

[FoFa]

Well this is a tuff one. Some thing is holding a lock on those records. By exiting Access, it is releasing them. Once Acess has those rows locked, can use Entiprise Manager (Sql Server) to go see what is locking those rows? Usually you can tell what processes are blocking are what.

 

[hi2jenny]

But I don't see any additional lock created after I opened the Access database or after I opened the report the first time.