Unable to update data in a SQL table from within Access 1

[MasterPO]

Hi All:

I used DTS to move data from an existing Access 2000 database to a SQL Server 2000 database. I linked the SQL table back to Access.

The Problem: Imported records will not allow any data fields to be updated via Access. New records can be added and updated with no problem!

When I attempt to update any of the imported records, Access pops up the 'Write Conflict' message box that says "The record has been changed by another user..." but this is false (I am the only user!)

Tried so far:
1. Checked and set permissions to Public Read/Write in SQL Server
2. Checked the ODBC DSN to be sure it was using an authorized user account
3. Checked PK and rebuilt in SQL Server
3a. Checked to be sure Access was recognizing the PK
4. Ran Update statements via SQL Query Analyzer.. this works just fine.
4a. Tried the same query via an Access Pass Through query.. it failed.
5. Out of desperation, I deleted all records from the SQL table and used an append query (instead of DTS) to load them again from the original Access data table. Same results...

Any ideas??

 

[cmmrfrds]

Your table in SQL Server probably has a field defined as a bit data type and these cause a problem when linked through ODBC. There are a number of solutions, but the easiest is to put a datestamp data type field on your table. Another solution is to update all the bit fields where the NULLS are made false. Let us know if this is the problem.

 

[cmmrfrds]

I forgot to mention, make sure there is a primary key and unique index on the sql server table and that this is designated in the ODBC linked table.

 

[MasterPO]

That did the trick! I found an Access True/False field that converted with NULL values in the SQL bit field. I updated all NULL records to a 0 and the problem went away.

Many many thanks for delivering me from my frustration!