Share problem with Access to SQL 2005 table

[Guest]

We're using an Access 2003 ODBC connection to a SQL 2005 database. Once I
added fields to the SQL 2005 table and re-linked the connection in Access the
entire table began generating an error of "This record has been changed by
another user since you started editing it..."

The old ODBC link, although not showing the added SQL fields, can modify the
records without problem.

I've tried recreating a new ODBC link as well as testing on different
versions of Access 2003 (including a version with SP2 installed).

 

[Rick Brandt]

We're using an Access 2003 ODBC connection to a SQL 2005 database.
Once I added fields to the SQL 2005 table and re-linked the
connection in Access the entire table began generating an error of
"This record has been changed by another user since you started
editing it..."

The old ODBC link, although not showing the added SQL fields, can
modify the records without problem.

I've tried recreating a new ODBC link as well as testing on different
versions of Access 2003 (including a version with SP2 installed).

This can happen if you have certain field types in your table, particularly
if the fields with those types are part of the primary key. What fields and
data types did you add? Often just adding a Timestamp column to the table
on the server will clear this up.

 

[Guest]

Thanks for the response, Rick.

I added 3 nvarchar(50) fields (all other character fields in this table were
defined similarly as nvarchar) and 2 bit fields, none of which were part of
the primary key.

I can certainly try adding a timestamp column. I just don't see why adding
the columns kept the ODBC connection from working when the old ODBC
connection (although not showing the new fields) still can modify the table!

I'll wait before adding a timestamp column to see what you have to say. I
can send you the entire structure of the table if that would help. Only the
last four fields are new.

Thanks again.
Jer

 

[Rick Brandt]

Thanks for the response, Rick.

I added 3 nvarchar(50) fields (all other character fields in this
table were defined similarly as nvarchar) and 2 bit fields, none of
which were part of the primary key.

I can certainly try adding a timestamp column. I just don't see why
adding the columns kept the ODBC connection from working when the old
ODBC connection (although not showing the new fields) still can
modify the table!

I'll wait before adding a timestamp column to see what you have to
say. I can send you the entire structure of the table if that would
help. Only the last four fields are new.

Do your Bit fields allow nulls? The corresponding Yes/No field in
Access/Jet does not allow Nulls so when linked to a SQL Server table with
Bit fields that allow Nulls you get this problem. Either changing those
fields to not allow Nulls or adding the Timestamp field should solve the
problem.

It's not a bad idea to have a Timestamp field in every SQL Server table that
will be linked to from Access as it greatly simplifies the test that is
performed when doing edits.

 

[Roger Carlson]

It's probably the Bit fields. You won't be able to edit any records where
the bit field is null. Look at this article:
http://support.microsoft.com/kb/280730/EN-US/


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks, Roger! It worked. I converted the bit fields to int and everything
started working. The article reference was a great help, too.

Thanks again. I'll let my other developers here know about this, too.