[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-12-18
11
Medium Priority
?
1,749 Views
Last Modified: 2014-12-18
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
Comment
Question by:patrickmiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40506977
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40506986
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
 
LVL 58
ID: 40506987
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:patrickmiller
ID: 40506990
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
 

Author Comment

by:patrickmiller
ID: 40507011
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
 

Author Comment

by:patrickmiller
ID: 40507274
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
 
LVL 58
ID: 40507283
<< 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
 

Author Comment

by:patrickmiller
ID: 40507305
I added a time stamp and got the same results.
0
 

Author Comment

by:patrickmiller
ID: 40507375
I got rid of the column called   identity_column and then the #Deleted is gone and the records show up.
0
 
LVL 58
ID: 40507419
That's rather odd, but if it's working it's working.

Jim.
0
 

Author Comment

by:patrickmiller
ID: 40507498
Yes  it is working but you Timestamp suggestion helped point me in the right direction.  Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question