CAN'T Update TABLE Linked from MS ACCESS to SQL SERVER

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chicago Dave - 06 Oct 2004 18:35 GMT

Hi,
I am using SQL SERVER 2000 and Access 2000. We have a set of tables in SQL
SERVER that are linked into our ACCESS Database(NOT AN ADP). We have ONE
table that will not allow updates in Access (SQL SERVER is fine) ? IT always
gives the error -

"The Record has been changed by another user since you started editing it,
if you save the record you will overite the changes the other user has made.
coping to the clipboard will let you look at the values the other user
entered then paste you changes back in if you decide to make changes"

Three bottons are shown Save, copy to clipboard, and Drop Changes
The SAVE button is DISABLED, and if Chose copy Right click PAste is disable
(also ctrl-v does nothing)

Why is this happing - it does not matter what user is using the system it
does not seem to matter what ACCESS security is set at or SQL secruity (I AM
'SA' and is does it to me)

The only thing I can think of is that this table has a compound primary key?
Would that matter?

Thanks

Reply to this Message

Steve Jorgensen - 06 Oct 2004 18:43 GMT

The problem is that Access' optimistic locking is confused, and there are many
possible reasons for it. In general, adding a TIMESTAMP type column to your
table, and re-linking from Access will solve your problem, because Access will
then use just the TIMESTAMP column to find out whether the record was changed
by another user rather than the more error prone method of comparing the
original values of all the columns in the table.

>Hi,
>I am using SQL SERVER 2000 and Access 2000. We have a set of tables in SQL
[quoted text clipped - 19 lines]
>
>Thanks

Reply to this Message

info - 02 Nov 2004 09:42 GMT

> The problem is that Access' optimistic locking is confused, and there are many
> possible reasons for it. In general, adding a TIMESTAMP type column to your
[quoted text clipped - 26 lines]
> >
> >Thanks

Reply to this Message

Anthony Vanderplaats - 23 Nov 2004 06:45 GMT

The problem can be BOOLEAN fields too.
I had an SQL Server table, that was imported from ACCESS, and appears in linked mode with design view to have Yes/No fields and would generate same message as this when trying to toggle the fields.
Changed to REAL type and immediately worked (after re-linking).

Tip:
I have a 'generic' SQL passthru query handy in the app like:
ALTER TABLE dbo.YEARS ALTER COLUMN [YEARLOCKED] real;

and use it to quickly make minor fixes
(when I am remote and can't get at SQL Enterprise)
and have a 're-attach' function in apps that reads a 'master table'
of all connections, and looks for any that flagged as needing re-connect.

Reply to this Message