Access User can't modify a record added to a linked SQL Server table

I have a linked table in Microsoft Access 2003 that's linked to a table in SQL Server 2012.  The user specified in the connection has datareader permissions for the database in SQL Server, and for this one table has Grant permissions for Select, Update, and delete.  I can modify existing records and add a new records using this linked table, but I can't modify or delete records that *I* added using the linked table.  It gives me a message that the record was edited by another user when I try.  How can I fix this?
LVL 1
Declan_BasileITAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
Yes, an Access record is "read only" if it has a bit value that is not set - it must be o or 1 to allow the record to be edited. Well done for sorting it out.


Kelvin
0
 
Kelvin SparksCommented:
Firstly, you don't specify the INSERT grant - is that present?

Secondly, how are you altering these records - via VBA, directly into the linked table - or via a form?


Kelvin
0
 
Declan_BasileITAuthor Commented:
The user in the connection string has Insert, update, and delete permissions granted for this table.  I'm trying to update the record directly in the linked table and I'm getting a write conflict.  Also, I found out that if I add a record to the table under sa in SQL Server I still can't delete it or modify it in the linked table.  However, already existing records I can modify directly in the linked table.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Declan_BasileITAuthor Commented:
I think I figured it out.  The table had a series of bit fields with no default values and "allow null" set.  I changed these fields to have a default value of "0" and now the users can edit and delete new records.  It wouldn't allow users to modify or delete records if the record had a null value for a bit field.  Did you ever hear about this limitation?
0
 
Kelvin SparksCommented:
It's a common oversight when migrating a database from Access to SQL Server. Generally any table built in SQL will have a default value set at the table level and be set to NOT NULL.


Kelvin
0
 
Declan_BasileITAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.