Write Conflict - MDB/SQL

[Guest]

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!

 

[Albert D.Kallal]

Assuming you are 100% sure that the ONLY thing you changed was addition of
two fields, then check the following:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table

http://support.microsoft.com/?id=280730

If the above is not your case, then that "other" person is often your code!!
If you use code to execute some update to the *same* data as the form, and
the form still has pending writes, then obviously you have a conflict. the
solution in this case is simply to force a disk write in your forms code
BEFORE you run the update code.

if me.Dirty = true then
me.Dirty = false
end if

.....now run your code that updates data....


The above code snip thus forces a disk write of the forms data...and then
you are free to use code to run update stuff.

So, check the above two things. And, as a side note, for sql server, you
want

** always have a primary key for each field...access *really* needs
this
** always have a timestamp field exposed in all the queries and
forms -- again, access uses this to determine if the record was updated, and
has a *harder* time figuring this out without a timestamp field (this is
especially noticeable in sub-forms).

 

[Danny J. Lesandrini]

Add a timestamp field to the SQL Server tables. Doesn't matter what it's named.

If either of the fields was a Memo field, then Access can't verify that the record
hasn't changed. Adding the Timestamp field simplifies the task.