Update query lock violations

[PeterWieland]

Hi,

I have a very simple Access 2000 frontend, SQL 2000 backend database.

There is a table with 2 key fields on the SQL server that I want to update from a local table in the Access frontend. Only one field needs updating.

I have built a query with the key fields of the SQL table linked to the relevent fields in the Access table as follows:

UPDATE dbo_tblGrades INNER JOIN tblSetImport ON (dbo_tblGrades.SubjectCode = tblSetImport.SubjectCode) AND (dbo_tblGrades.RollNumber = tblSetImport.RollNumber) SET dbo_tblGrades.ClassCode = [tblSetImport]![ClassCode];

When I run the query, I first get a message saying that 16,057 records will be updated, then the warning that 67 records cannot be updated due to lock violations.

There are no other users on the system as I am still developing it, and am running SQL Server Personal. For this particular routine there will never be any other users, as it will be an occasional maintenance function.

I have searched this and other forums for lock violations, but all of the subjects returned only deal with key violations (this includes Access help)

Any Ideas

Thanks

Peter

 

[pono1]

Vague Idea

Peter,

Is there a bound form open when you run the query? If so, try running the query without that form open. Otherwise, not sure...

Regards,
Tim

 

[PeterWieland]

Hi Pat,

Thanks for that. You are right about the fields not being unique. I have since discovered that the school's information management system (a commercial product), allows you to enrol a student in a class twice!, so the exported list has duplicates in it. I have had to add an autonumber field to the table and use that as the index, then use a find duplicates query to get rid of the double entries.

Peter