MS Access 2000 and SQL data - locks

[Vcscwi]

I have a MS Access 2000 database set up with a SQL data source. I have a form that allows you to add a record and that works fine. Although, if the user moves off the record or even the form and then back to that record to edit or delete it. They receive a lock error.

"The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decided to make changes."

Although if I open the table via the SQL Enterprise Manager I am able to change the data no problem. If I open the linked SQL table in MS Access, it does not let me edit it.

I've tried changing the Tools, Options Advanced tab from No locks to edit records and still have the same result. I've even exited from MS Access and then reopened it..and its still locked.

Now other records that I have not added but were converted from a previous data conversion I can edit and manipulate without the error.

Please any suggestions would be great.

 

[PHV]

Does access recognize the Primary Key ?
You may consider adding a TimeStamp field in the SQL-Server table and refresh the link.

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

 

[Vcscwi]

When I look at the table in design view through MS Access it does show me the primary key. Is there another way to know for sure?

 

[PHV]

If your SQL table has a BitField allowing null you definitively need to add a TimeStamp field.

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

 

[Vcscwi]

You are right on. When adding new records there was a bit field without a value... that locked it from anyone else updating the record!

THANKS!