By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

how does one really delete a deleted record?

BeemerBiker
P: 87
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.
Feb 21 '09 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 21 '09 #2

mshmyob
Expert 100+
P: 903
The 2007 Compact & repair is under that big round Office Button in the upper left. Then select Manage and you will see it.

cheers,
Feb 22 '09 #3

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

However, I tried doing
Expand|Select|Wrap|Line Numbers
  1. DBI:$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=xxx
  2. then
  3. $dbh->compact()
  4.  
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

Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE::Const 'Microsoft DAO 3.6 Object Library';
  2. my $acc = Win32::OLE->new("DAO.DBEngine.36", 'quit');
  3.  
and
Expand|Select|Wrap|Line Numbers
  1. use DBI;
  2. DBI:$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq-blahblahblah;
  3.  
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!
Feb 25 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
I wish I could help. I think I'll have to leave you in Mshmyob's capables.
Feb 25 '09 #5

mshmyob
Expert 100+
P: 903
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,
Feb 25 '09 #6

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 25 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.