• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2247
  • Last Modified:

Why is it showing #Deleted in all fields and records in my Access 2010 linked table?

I have a table in SQL 2008 database and I am trying to give users access to it and every field is showing #Deleted.   I need to update and add records to this table.    I am able to sucessfully link another table in that same database and make changes to it.   It is a one field table but I am able to make changes to it.    What am I doing wrong for the linked table to show #deleted.    Need an answer today.  Thanks.
0
patrickmiller
Asked:
patrickmiller
  • 6
  • 4
1 Solution
 
SimonCommented:
Try using linked table manager to refresh the link to the table. If that doesn't work, delete and then re-add the table. Have design changes been made in the table that shows #deleted ?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple of things:

1. Add a Timestamp field to the table, then re-link the tables.

2. Make sure you don't have a single or float as part of the PK.

3. If you have any bit fields, make sure they don't allow nulls.

4. Make sure your not using any triggers that would modify the key field values.

Usually #1 does the trick though.

 #Deleted is a result of the way JET works with ODBC datasources.  It uses a Keyset cursor.  When you go to do anything with a record, it re-SELECTS the record.  If it thinks any part of the record changed, it gives you a #Deleted on the row because it thinks it can't find the record again.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW on the bit fields, if you did allow nulls, make sure you update all the existing Nulls with False.

Things won't work right until you do.

Jim.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
patrickmillerAuthor Commented:
I just refreshed the linked table.    I have deleted it and then readded the link.     I made one of the fields the primary, but it had the same error before that.
0
 
patrickmillerAuthor Commented:
How do I change fields that have Null in them to false?     I am not an expert on SQL but I can get into and bring up the tables but I don't know how to change a date field and a text field to false.
0
 
patrickmillerAuthor Commented:
I got rid of the Nulls but I get the same results.  I have identity_column which is the primary key.   That should take the place of the timestamp field.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< That should take the place of the timestamp field. >>

 Actually no.   JET/ACE will latch onto a TimeStamp field for a unique key first before anything else.   A TimeStamp data type in SQL is really not a TimStamp value.  What it does is turn on row versioning in a table.

 It's basically a must for working with SQL tables from Access.

Jim.
0
 
patrickmillerAuthor Commented:
I added a time stamp and got the same results.
0
 
patrickmillerAuthor Commented:
I got rid of the column called   identity_column and then the #Deleted is gone and the records show up.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That's rather odd, but if it's working it's working.

Jim.
0
 
patrickmillerAuthor Commented:
Yes  it is working but you Timestamp suggestion helped point me in the right direction.  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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now