Board index » delphi » Problem with DELETE SQL statement in TADOCommand

Problem with DELETE SQL statement in TADOCommand

Hi,

I've looked through the posts on this group, but couldn't find a problem
like mine (although I vaguely remember one like it in the past), so sorry if
this has all been done before :-)

I am using a TADOCommand to clean "processed" records out of a Table used
for queuing data in an interface between Palm and PC applications.

The SQL statement is:
"DELETE FROM [PDA Expenses] WHERE Journal IS NOT NULL AND Line IS NOT NULL
and [Last Sync] IS NULL OR [Last Sync] <> 376998725"

No records are deleted and an "exception" is reported.  The message is:
"Could not update; currently locked"

But no other apps are running, and I've tried rebooting etc. in case there
are "hidden locks" somewhere.  Interestingly enough, the records concerned
CAN be manually deleted from the Access 2000 database if I log on to Access
and do it "manually".

Any suggestions?

Cheers, Paul
mailto:p...@holland.name

 

Re:Problem with DELETE SQL statement in TADOCommand


Have you tried put bracket around the conditions
ie
 "DELETE FROM [PDA Expenses] WHERE (Journal IS NOT NULL) AND (Line IS NOT
NULL)
 and (([Last Sync] IS NULL) OR ([Last Sync] <> 376998725))"

you may also need square brackets for the field "Line"  ...  ([Line] is not
null)

Quote
"Paul Holland" <p...@holland.name> wrote in message

news:3e783fcb@newsgroups.borland.com...
Quote
> Hi,

> I've looked through the posts on this group, but couldn't find a problem
> like mine (although I vaguely remember one like it in the past), so sorry
if
> this has all been done before :-)

> I am using a TADOCommand to clean "processed" records out of a Table used
> for queuing data in an interface between Palm and PC applications.

> The SQL statement is:
> "DELETE FROM [PDA Expenses] WHERE Journal IS NOT NULL AND Line IS NOT NULL
> and [Last Sync] IS NULL OR [Last Sync] <> 376998725"

> No records are deleted and an "exception" is reported.  The message is:
> "Could not update; currently locked"

> But no other apps are running, and I've tried rebooting etc. in case there
> are "hidden locks" somewhere.  Interestingly enough, the records concerned
> CAN be manually deleted from the Access 2000 database if I log on to
Access
> and do it "manually".

> Any suggestions?

> Cheers, Paul
> mailto:p...@holland.name

Re:Problem with DELETE SQL statement in TADOCommand


Tried that Andrew, but same problem!  That is:
"DELETE FROM [PDA Expenses] WHERE (Journal IS NOT NULL) AND ([Line] IS NOT
NULL) AND (([Last Sync] IS NULL) OR ([Last Sync] <> 377007379))" failed!
[Could not update; currently locked]

Any other ideas?

Quote
"Andrew Muir" <arm...@bigpond.net.au> wrote in message

news:3e785aaa@newsgroups.borland.com...
Quote
> Have you tried put bracket around the conditions
> ie
>  "DELETE FROM [PDA Expenses] WHERE (Journal IS NOT NULL) AND (Line IS NOT
> NULL)
>  and (([Last Sync] IS NULL) OR ([Last Sync] <> 376998725))"

> you may also need square brackets for the field "Line"  ...  ([Line] is
not
> null)

> "Paul Holland" <p...@holland.name> wrote in message
> news:3e783fcb@newsgroups.borland.com...
> > Hi,

> > I've looked through the posts on this group, but couldn't find a problem
> > like mine (although I vaguely remember one like it in the past), so
sorry
> if
> > this has all been done before :-)

> > I am using a TADOCommand to clean "processed" records out of a Table
used
> > for queuing data in an interface between Palm and PC applications.

> > The SQL statement is:
> > "DELETE FROM [PDA Expenses] WHERE Journal IS NOT NULL AND Line IS NOT
NULL
> > and [Last Sync] IS NULL OR [Last Sync] <> 376998725"

> > No records are deleted and an "exception" is reported.  The message is:
> > "Could not update; currently locked"

> > But no other apps are running, and I've tried rebooting etc. in case
there
> > are "hidden locks" somewhere.  Interestingly enough, the records
concerned
> > CAN be manually deleted from the Access 2000 database if I log on to
> Access
> > and do it "manually".

> > Any suggestions?

> > Cheers, Paul
> > mailto:p...@holland.name

Re:Problem with DELETE SQL statement in TADOCommand


Bizarre!  I think I've solved it myself.  By playing with Access "Visual
Query Builder" I've found a Delphi SQL Statement that works!  Can anyone
tell me what the difference is from my original CommandText?  How does
specifying each of the fields referred to in the WHERE, get around the
"Locked Record" reported in the error?  (Or have I found a bug in Delphi 6?)

The new SQL is:
"DELETE [PDA Expenses].*, [PDA Expenses].[Last Sync], [PDA
Expenses].Journal, [PDA Expenses].Line FROM [PDA Expenses] WHERE Journal IS
NOT NULL AND Line IS NOT NULL AND ([Last Sync] IS NULL OR [Last Sync] <>
377040917)"

Strangely it seems that the * and each of the FieldNames are required...

Quote
"Paul Holland" <p...@holland.name> wrote in message

news:3e783fcb@newsgroups.borland.com...
Quote
> Hi,

> I've looked through the posts on this group, but couldn't find a problem
> like mine (although I vaguely remember one like it in the past), so sorry
if
> this has all been done before :-)

> I am using a TADOCommand to clean "processed" records out of a Table used
> for queuing data in an interface between Palm and PC applications.

> The SQL statement is:
> "DELETE FROM [PDA Expenses] WHERE Journal IS NOT NULL AND Line IS NOT NULL
> and [Last Sync] IS NULL OR [Last Sync] <> 376998725"

> No records are deleted and an "exception" is reported.  The message is:
> "Could not update; currently locked"

> But no other apps are running, and I've tried rebooting etc. in case there
> are "hidden locks" somewhere.  Interestingly enough, the records concerned
> CAN be manually deleted from the Access 2000 database if I log on to
Access
> and do it "manually".

> Any suggestions?

> Cheers, Paul
> mailto:p...@holland.name

Other Threads