|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Cannot UPDATE Linked SQL Server Table in Access
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Cannot UPDATE Linked SQL Server Table in Access
|
|
|
| "David Frick" <dave@frick-cpa.com> wrote in message news:<#2PPCbFiCHA.1840@tkmsftngp08>...
> I created a blank Access 2002 database and linked 4 tables from SQL Server
> 2000.
<<>>
> Does anyone have any idea why this simple linked table will not allow me to
> update records?
I'd have thought the trigger is your problem.
Access does a checksum as it reads data optimistically locked.
When you try and update it reads the record and does another checksum.
You get the changed by another user stuff if the checksums don't
match.
Or at least this is how it used to work - not read up on it for a few
years but odds are this is the same.
The other thing to watch out for is access needs a unique key before
you can update.... but I don't think that's your problem here.
| |
| David Frick 2002-11-11, 10:24 am |
| Thanks Andy.
I have put a lot of time into this and the only way I can get it to work is
to remove the "bit" data type fields from the table.
This doesn't make sense (one of the other tables has a bit data field and
works fine) but this is the only way I can avoid the concurrent user error
on updates.
I tried dropping the table and its relationship, recreating it with a
different name, removing all constraints (a PK and a DEFAULT). But nothing
worked until I dropped all the bit fields. Then it worked perfectly. Add a
single bit field back and I get the error again.
Any idea on what could possibly be causing this problem?
| |
| David Frick 2002-11-11, 5:23 pm |
| Andy:
Just in case you or anyone else encounters this problem, here is the
solution as provided by Rita Nikas, MCSE MCDBA of MS tech support:
---------------
280730
ACC2000: Write Conflict Error When You Try to Update Records in a Linked
http://support.microsoft.com/defaul...KB;EN-US;280730
Basically, you end up with this problem because Microsoft Access is
creating Null bit fields, which causes a type mismatch. To resolve this
problem, do one of the following:
- Using SQL Server, open the table in Design view, and assign a default
value of 0 (zero) on all bit fields. NOTE: With this option, you must
update records that were entered before this change was made. See the
next item for more information.
- Using SQL Server, run an Update Transact-SQL statement against the
table, and set all bit fields that have a value of Null to a new value
of 0 or 1.
- Using SQL Server, change the bit fields to some other data type, such
as int or tinyint.
- Using SQL Server, add a timestamp field to the table.
- Use a Microsoft Access project (*.adp) instead of an Access database
(*.mdb).
|
|
|
|
|