Write conflict using linked SQL tables

[gwilym40]

Hi

I have an Access 97 Database with linked tables to SQL.
if multiple users access the same record at the same time - the first user who committs is ok, the second user gets a write conflict - this isn't the bit fields problem (using a default value of 0 in SQL - I've had that !) - can anyone provide a solution to my problem? the locking in access doesn't work either

regards
tony

 

[cmmrfrds]

Are you using a Form that is bound to a query? Is this a pass-thru query? If not, does the same thing happen with a pass-thru query?

Are you trying to update multiple tables in the query, or just 1 table? What happens if the query uses only 1 table? In standard ANSI SQL only 1 table can be updated in a sql statement. If multiple tables are updated then, Access and ODBC need to manage it with sql server. The pass-thru should remove Access from equation and isolate to just ODBC. As you mentioned bit fields are a problem with ODBC but the error is usually different.

 

[cmmrfrds]

Here is a quick thing to try, assuming the query has multiple tables you are trying to update and it is bound to a Form. Under the data tab, change the record type from dynaset to inconsistent dynaset.

 

[pandapark]

thanks cmmrfrds

the forms are bound direct to the SQL table - however I need to allow the user to update some of the fields on the access form - I tried a pass-through but then got - recordset is not updatable which i need it to be
I tried inconsistent dynaset as well but that didn't work

tony

 

[cmmrfrds]

Only 1 table in the SQL? When you linked the table with ODBC, you gave it the unique index from sql server? Check that there is a unique index that ODBC is using.