Solved

#Deleted Problem with MS Access and MS SQL

Posted on 2013-05-10
5
340 Views
Last Modified: 2013-11-20
Hello Experts,

I'm using MS Access 2000 as my front end and MS SQL Server as the backend (I believe it is 2008 R2).

I have a table with 3 million record that is giving me trouble.  At least part of the table has become currupt and the prhase #Deleted now appears in every field of a specific row.

The problem record can be displayed if I run this query:
SELECT CompPricing.Manufacturer, CompPricing.*
FROM CompPricing
WHERE (((CompPricing.Manufacturer) Like "*hHQp*"));

However, when the query results appear the work #Deleted appears in every field.

If I attempt to delete the row from the query, the system acts as it would if I were actually deleting a row, except that no deletion ever takes place.

I'm using ODBC to connect to the MS SQL Server.

I logged into the MS SQL server and attempted to manually delete the record.
As before, the system responded as it would if the record was deleted,
although no deletion took place.

The deleted record is causing other routines to fail so I can not ignore it .

Please HELP!

Thank you
0
Comment
Question by:pcalabria
  • 2
  • 2
5 Comments
 
LVL 57
ID: 39155499
<<I have a table with 3 million record that is giving me trouble.  At least part of the table has become currupt and the prhase #Deleted now appears in every field of a specific row.>>

 #Deleted does not mean the table is corrupt.

 #Deleted is used by JET to indicate that it can no longer find the row.

 This can be caused by numerious things.

 First, add a timestamp column to the table, whcih turns on row versioning and then re-link the table in Access.  That takes care of it in most cases.

 If not, we'll need to look at a little more in detail.

Jim.
0
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39155500
Have any triggers been added to the affected tables recently?  It could be a badly coded trigger at fault here.
0
 

Author Comment

by:pcalabria
ID: 39156518
Jim,

I'm not sure what a timestamp column is.  What data goes in the column?  No triggers.
0
 

Author Comment

by:pcalabria
ID: 39156702
I figured out what a timestamp is, but it turns out I have had one all along.

I deleted the link to the table, then recreated it, and have the same problem.

I also deleted the ODBC connect and reconnected... same prolem.

Any iteas?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39158007
<<If I attempt to delete the row from the query, the system acts as it would if I were actually deleting a row, except that no deletion ever takes place.>>

 This and #Deleted are usually indications that a unique index is not available or that the key that the record is based on is being changed by a trigger.

  To understand how JET works with ODBC datasources and why #Deleted can occur, have a read through this:

"#Deleted" errors with linked ODBC tables
http://support.microsoft.com/kb/128809

there is also a slightly updated version of that here:

PRB: Explaining "Record is deleted" error accessing ODBC table
http://support.microsoft.com/kb/172339

also, since this is Access 2000, it may be a result of this:

ACC2000: Linked SQL Server Table That Uses BigInt Data Type as Primary Key Displays #Deleted
http://support.microsoft.com/kb/321901

 Take a read through those and see if anything might pertain.  Also, I'd check if your up to date on service packs.

  There is one other problem that used to exist with JET 4.0, but that would give you #Deleted in every other row.  Once again however, it relates to the primary key being used and how JET works with it's ODBC keyset cursors.

Jim.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now