Locked Records

[crosmill]

I have a hybrid SQL Server/Access db. The table sit on the SQL Server but I use Access as a front end. I been developing the database while it's been in use, so I have a copy of the database which I work on and the original that someone else updates data with.

I've been makeing changes to the table design, like adding new fields, however, when I try update the new fields it tells me that somone else has edited the record since I opend it and it won't let make changes. I can't seem to "unlock" the records. Obviously I've kicked everyone off it and now I'm the only person using it but I still can't update it.

Can anyone help me??

 

[FoFa]

Usually I find this when a record is open in more than place. Like a form has the record displayed, and I try to update it in VBA or something like that. The form will hold a record it thinks is open for edit (Sql locks it I think) then the other code can't update it. There maybe more things that cause this and maybe others can offer some advise also.

 

[crosmill]

Thanks FoFa,

I figured it out. Some of the new columns in SQL Server were of Bit data type, Access trys to pass a null value to it and that's where the conflict happens.

Soultion: Make sure all Bit data types DO NOT allow nulls and set the default value for the bit data types to '0'. Then populate all the null values with '0'.

schoolboy error.

 

[GTC]

I just had this same problem and had me stumped for a few hours - if it weren't for this solution, I'd be stumped for days more!!!

I had a perfectly working test DB, added four bit fields and saved. EM would update but MS Access wouldn't.

I looked at tables and it was display zero's in column so I thought it was sweet.

I ran an update query to set those four columns to zero, then in EM table design, removed the "Nullable" flag, refresh MS Access links and "viola"!!

All is sweet - thanks for saving me days of confusion!