Write Conflict MDB/SQL

[Robert_L_Ross]

We recently upsized data from the BE of a FE/BE MSAccess database to SQL
Server.

During the migration process (Development, Quality Assurance, User Acceptace
and Production) we had no problems.

I recently requested to have two fields added to one of the tables in the
Development environment. Suddently, I cannot modify records in that table
without getting a Write Conflict error. All other tables in the DEV region
operate as expected.

The table has a Primary Key and is Indexed. When I try to modify the data,
I get the Write Conflict dialog but the Save Record option is greyed out - I
only have the Copy to Clipboard and Drop Changes options.

I have no idea why this is happening. I've made a completely new blank DB
and added just two tables (the affected table and one that works) and I get
the same error when editing it directly in the table view, so I'm sure it's
not any form or code that is causing the problem.

We are clueless as to why this is happening. My SQL group tells me I have 2
connections when I hit the SQL table - but on my 'control' table, I have 2
connectiosn and it allows edits, on this one modified table I have 2
connections and it won't let me edit.

Does anyone have any idea how this happens and how to fix it?

Thanks!

 

[Duane Hookom]

I just came across the same issue today and searched Google with the error
message. I found a posting by MS Access MVP Rick Brandt that suggested
adding a Timestamp data type field to the SQL Server table. You can
basically ignore the field once you have added it to the table.

I tried this and the error stopped.

 

[Robert_L_Ross]

I read that too, but I'm trying to figure out why it only happens in the one
table, and only after adding the new field...I can't figure out why it's not
happening on other tables.

 

[Duane Hookom]

I didn't necessarily have time to worry about researching the "why". I
needed a solution that was quick and easy to implement. It works and I have
moved on to more important tasks.

 

[Robert_L_Ross]

eDuane,

Thanks for the help, but I think we may have found it.

We added three fields to the table, a NVARCHAR, an INT and a BIT. We took
all three out and added them one at a time in order.

When we got to the BIT field, I found the Write Conflict error again.

We went through in SQL and set the fields to default to 0 and the error went
away.

I'm guessing that it's something to do with how MSAccess reads Yes/No (BIT)
fields. If you notice when you put in a Yes/No field to a table, then tie a
check box to it in a form, if it has no default value, it will appear
'grey'...a sign of a third state (Null). Once our SQL group ran a query to
populate the BIT-Yes/No fields to No, the error dissapeared.

 

[Duane Hookom]

Sounds like a winner. Thanks for taking the time to reply back with your
findings.