MS Access "Write Conflict" error when adding new field to record source

Question

I want to preface this by saying I don't have any real programming background, I'm just trying to update an existing database at work.

We have an access database and I want to add an additional Y/N checkbox to an existing form. The form updates a SQL table. Currently the record source is a SQL statement.

I can go to the SQL table, add a new field and make it Yes/No data type. There are other Yes/No fields in the table and the default settings for the new field are identical to the others. I next go and update the linked table through External Data in the ribbon. I can go into the table in Access and see the new field - so far, so good.

Next, go to the form design view and form properties, go to the record source, update the SQL statement to include the new field. (I've also tried this thru query builder, same result.) From here, I start to get the error.

If I go back to form view and change any data in the form and hit the next record button or save button, I get the Write Conflict error. "This record has been changed by another user since you started editing it..." The 'Save Record' button is greyed out. I am the only person accessing the database or SQL server.

I've tried to finish building the new button and linking it to the new field in control source (that went fine), but it didn't make any difference. If I go in to edit the record source and remove the new field, everything works again (but of course, the new field isn't in the control source list, so isn't linked to the check box).

Any ideas? Thanks.

Answer 1

A strong contender for the reason for your problem is the form itself.

Most likely the form is setup with a specific query as the Record Source. Its not to say that that is inherently incorrect, but it makes adding new columns to the source significantly more difficult.

The problem would likely be solved if you just changed the Record Source to reference the table itself, rather than a query, if that is in fact how it is referenced.

If Ms Access tries to pull data from a table using a query through a form it will inherently Pessimistically Lock the table in question, making it unable to be modified. However, you can usually still modify the table through the query itself, but you would need to add the column changes into that query first.

I'm not quite sure if I am making sense here, but something like this (for a table called "Table1"):

In theory, if the form is the problem... then if you closed it and tried to make modifications to the table, then the changes should work when the form is closed.

Answer 2

Someone else fixed it for me, but from what I understand, it was a communication issue between SQL and Access. Something with the setting of the null value in SQL not being set the way that Access could understand.
We had the issue narrowed down. When the new field was added to the table, you couldn't change any info directly in the table, but you could with the form. If you added the new field to the form's record source, you couldn't edit any info at all.

Thanks for everyone's input.