Write Conflict using Access 2K and ODBC/SQL.

[ADWoods]

Hi,

I hope somebody can help. I have an Access 2002 SP2 database that will not let me make changes to data in a SQL database via the Access link.

I have two parts to my database. A backend, MS SQL 2000 database located on a server. This is working fine and I can update any data I want.

The second part is a MS Access 2002 database that I use as a front end. I link to the SQL database using ODBC and an Access link.

When I go in to Access and try to edit data in an existing row I get an error: Write Conflict. (message about writing data at the same time as someone else), Save Changes (Greyed out), Drop Changes, Copy to Clipboard.

I have tried to reset the record locking to Edit Row but this has no affect. I have also applied all updates to Windows XP and Office. I have even tried to change the cells on other PCs. But have had no luck.

Can any one advise me about what to do?

Thanks, Gil.

 

[markgrizzle]

Make sure the sql table has a primary key index access can recognize. Without this Access can't tell one record from another, and prevents writes. The sql date/time stamp isn't enough.

 

[cmmrfrds]

ODBC does not do a good job of mapping data types, especially bit data types - there may be others. ADO is able to map these types okay. So, a couple of options use ADO or get rid of the bit data types. You could always use tinyint for the bit types.

What happens is that when Access/ODBC compares the edited record to the record in the server it thinks the record has changed because of the incorrect mapping, therefore, it determines the record was edited by another user. Of course, this is not the case but at this point you need to look at alternatives.

 

[ADWoods]

Thanks for the advice. I will try ADO route.

ADWoods.