Problem with deleting record

[lwidjaya]

Hi,
When I tried deleting records using db.execute "Delete * from tbl1", it
didn't work. It showed that the table was locked even though nothing was
using it. But when I used delete query, it worked fine. Is there a way to
find out what session or table that locks tbl1?

Thanks.

 

[Jeff Boyce]

You don't mention which version of Access you are using, or whether the data
is in the same database file, or linked from, say, SQL-Server.

The symptom you described sounds similar to a problem SQL-Server had knowing
if a record had been dirtied ... this particular issue required the addition
of a TS-type data field in the table.

More info, please...

 

[John W. Vinson]

Hi,
When I tried deleting records using db.execute "Delete * from tbl1", it
didn't work. It showed that the table was locked even though nothing was
using it. But when I used delete query, it worked fine. Is there a way to
find out what session or table that locks tbl1?

Thanks.

I'm sorry, but this makes no sense. "Delete * from tbl1" *IS* a delete query.
So it sounds like you're saying that a delete query didn't work, but a delete
query worked.

Context? What steps did you in fact take? What happened when you did?

 

[lwidjaya]

oops..sorry for the lack of info.
I'm using Access 2000. Front end is in mdb (later on it will be compiled to
mde), back end is in cdb.
tbl1 is a local table in the mdb that we use as temporary table.

Basically, we use this tbl1 to store sales transaction and after we're done
processing and inserting the data to the linked table, we'll delete the data
from tbl1. That's where we use db.execute "Delete * from tbl1". In debug
mode, after I ran this statement, there was a lock mark next to the record in
the table (slashed circle). I didn't get any error message even though it
failed executing the statement. But if I break before that statement and ran
a delete query from database screen, it worked perfectly fine. No error.
I tried running the query after the code failed deleting the data, and I got
this message:
Could not update; currently locked by another session on this machine

I'm still debugging the code right now to find out which process locking the
table. We tried using different cdb file and it worked fine too.

Thanks,
Lisa

 

[lwidjaya]

I found the problem. Thank you for all help.

oops..sorry for the lack of info.
I'm using Access 2000. Front end is in mdb (later on it will be compiled to
mde), back end is in cdb.
tbl1 is a local table in the mdb that we use as temporary table.

Basically, we use this tbl1 to store sales transaction and after we're done
processing and inserting the data to the linked table, we'll delete the data
from tbl1. That's where we use db.execute "Delete * from tbl1". In debug
mode, after I ran this statement, there was a lock mark next to the record in
the table (slashed circle). I didn't get any error message even though it
failed executing the statement. But if I break before that statement and ran
a delete query from database screen, it worked perfectly fine. No error.
I tried running the query after the code failed deleting the data, and I got
this message:
Could not update; currently locked by another session on this machine

I'm still debugging the code right now to find out which process locking the
table. We tried using different cdb file and it worked fine too.

Thanks,
Lisa

 

[Jeff Boyce]

Please consider posting your solution ... someone else in the future may
have the same problem and your solution may help them.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.