Tables Linked by ODBC - record locking issues.

[RobGMiller]

Access 2003 connected to SQL Server 2005 Enterprise via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbName;UID=xx;PWD=xxx

There is only one user in one application connected to the problem records.

Originally the data was imported into SS2k5 from Access tables. All imported
records can be edited or updated without a problem but new records created
from
access using code or access table views cannot be edited.

The returned error:
The records has been changed by another since you started editing it. ....

If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.

It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.

The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.

If you log into the Server using Management Studio with the same credentials
you can do anything you want.

Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.

Thanks in advance for any help you might offer.

 

[Sylvain Lafontaine]

First, each time you are changing the design of a backend table or view, you
should refresh the link(s). Second, I suspect in your case that you have a
checkbox linked to a column and that there is a mix in the saved values for
the true values: probably a mix of 1 and -1. Check the field type in the
backend and use a compatible field type. If necessary, update all the other
values (ie., set all 1 to -1 or all -1 to 1). As I don't use ODBC at this
moment, I cannot tell you more about this recurrent problem.

Another possibility would be that the primary key on the table is not filled
properly when you create a new record (unsupported type like BIGINT, absence
of a primary key, etc.). You should check the records that you have created
yourself and try to discover any fundamental difference between them and the
records that were previously imported from Access. In particular, take a
look at any GUID field or any TimeStamp field and at the presence or absence
of any default value; not only for the GUID or TimeStamp field but for other
fields as well, especially if they are not bound to your form.

Finally, for the views, take a look at the following article:
http://support.microsoft.com/kb/q209123/