SQL Table Corrupted

[apinzon]

Hello,

I got a problem with one SQL table that I access via ODBC in MS Access 2000.

This table have 2200 records but using a query appears 4028 records.

Those are records with the #deleted# tag in every field.

The problem is when I try to add records that are supposed deleted, I got an error "duplicate key value"
because that value is in a record with the #deleted# tag.

How can I restore those record that I think are corrupted.
Or How can I remove those records?

I never delete or try to delete those records and The "#deleted#" records is growing up.

In a query sorted by the index appears the deleted record with a value in the index field different from the original value.

Example of the query with the index field in ascending.

Index Field Name
1 Record 1,,,
2 Record 2,,,
3 Record 3,,,
4 Record 4,,,
3934934 #deleted#, #deleted#
6 Record 6,,,
7 Record 7,,,
9678658658 Record 8,,,
9 Record 9,,,
...

Thanks in Advance!!

 

[cdukes]

When you delete records from an access database, the records are never actually deleted, but are just flagged as being deleted. The only way to physically delete the records is to compact the database. This removes all flaged records and tidies up your database.

I would suggest, making a copy of the database, compact it and see if the problem goes.

Hope this helps,

Chris Dukes

 

[apinzon]

Hi,

Thanks for your response!

The funny thing is that I never delete those records. I don't know why those records are deleted.

Also, using a query its supossed to count only the undeleted records, why is counting those deleted records? 4028 records and if I go to the end of the table is 2000 record.

Well, I'll try to purge the table in SQL.

Thanks again

Angel Pinzon