how does one really delete a deleted record?

[BeemerBiker]

I can't get rid of a "deleted" record in a database. Even if I delete it the record shows back up the next time I do an update.

The following code generates an error message when using "driver=microsoft access driver (*.mdb)" Either in the app QTADO or in my own perl script that uses dbi:ODBC

really simple command: "UPDATE mytable SET Field1='rec';

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"

Ok there is no other user anywhere. When I tried the same query from Access 2007 I get a more informative error message and actuallly can see something that does not make sense;

Trying this

Gets this error message

If I click on yes, all the records are properly updated (unlike qtado or my perl script) however, there is a strange beast here:

I then went and "deleted" that record and tried the query again and sure enough the exact same thing happened, That "#Deleted" record had been put back in.

I poked around the access 2007 menues but cannot find any type of "repair" option. This database was created by 2003 and possibly is corrupted. I would think if I delete something it should go away.

[NeoPa]

Sounds pretty weird (from another Beemer biker ;) ).

I can only suggest doing a Compact / Repair of the database and trying again.

Alternatively, see what happens when you take Access 2007 out of the picture entirely, just to determine if it's related.

[mshmyob]

The 2007 Compact & repair is under that big round Office Button in the upper left. Then select Manage and you will see it.

cheers,

[BeemerBiker]

Solved - that manage - repair fixed it and the "deleted record" disappeared never to return (so far)

However, I tried doing

DBI:$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=xxx
then
$dbh->compact()
 

but: Can't locate object method "compact" via package "DBI::db"

I read where the compact method was available in the 4.0 jet engine but I do not know exactly what that is and if dbi:ODBC is using it.


so what is the diffrence between

use Win32::OLE::Const 'Microsoft DAO 3.6 Object Library';
my $acc = Win32::OLE->new("DAO.DBEngine.36", 'quit');
 

and

use DBI;
DBI:$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq-blahblahblah;
 

Do either of these use the so-called "jet 4.0 engine"? If I did not have access I would have to call the compact method (or so I have been told)

thanks!

[NeoPa]

I wish I could help. I think I'll have to leave you in Mshmyob's capables.

[mshmyob]

I don't know ASP very well but I found this article on how to compact and repair an Access database using ASP.

The key is that you need to make a copy first and then compact and repair then copy back.

I also have never used any of the QTADO tools - sorry.

ASP method to compact repair access database

hope this helps

cheers,

[NeoPa]

Sorry, didn't realise that was ASP (This is the Access forum).

If it would help, I can get this moved to one of the ASP.NET or ASP (classic) forums for you.