Link to home
Start Free TrialLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

#Deleted Problem with MS Access and MS SQL

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<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.
Have any triggers been added to the affected tables recently?  It could be a badly coded trigger at fault here.
Avatar of pcalabria

ASKER

Jim,

I'm not sure what a timestamp column is.  What data goes in the column?  No triggers.
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?
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial