how do I get rid of a deleted record

[Beemer Biker]

I have a record in a 2007 mdb that has the phrase "#Deleted" in every field.
I cant get rid of it. It shows back up the next time I do an update such as
running a simple "UDPATE table SET Field1='rec';

When running the above query under "driver=microsoft access driver (*.mdb)"
with an app such as QTADO or my own perl script that uses ODBC I get the
error message: "The Microsoft Jet database engine stopped the process
because you and another user are attempting to change the same data at the
same time" There are no other users, not now or ever.

If I run the update from access 2007
http://swri.info/images/deleted_1.png

I get a more informative error message:
http://swri.info/images/deleted_2.png

and end up with that deleted record showing up where the update failed
http://swri.info/images/deleted_record.png

If I select the record using Access 2007 GUI and "delete it" it goes away
but comes back the next time I try an update.

I am new to access and was wondering it this type of stuff happens all the
time.

 

[Tom van Stiphout]

On Sat, 21 Feb 2009 09:06:18 -0600, "Beemer Biker"

It appears you have corruption in that table. To proceed:
First try a Repair and Compact.
If that does not help:
Create a new table with the same layout.
Copy the records with ID values less than the bad record
Copy the records with ID values greater than the bad record
Delete old table
Rename new table
Re-establish relationships.

-Tom.
Microsoft Access MVP

 

[Beemer Biker]

On Sat, 21 Feb 2009 09:06:18 -0600, "Beemer Biker"

It appears you have corruption in that table. To proceed:
First try a Repair and Compact.

Thanks Tom - that worked, once I found that tool. Access 2007 make it
difficult to find things compared to 2003, and if I dont enable "dangerous
content" I seem to be missing a lot of useful stuff.

Question: How would I run that Repair if all I had was QTADO or some perl
code that just uses that MS jet engine dll?

 

[Tom van Stiphout]

On Sat, 21 Feb 2009 12:00:04 -0600, "Beemer Biker"

Call the Compact method of the DBEngine object.

-Tom.
Microsoft Access MVP