Where issue with delete query

I have a single table delete query in ACC2003 that will only delete if
I drop in a specific field and put the criteria 'is not null' I am
just simply trying to delete all records from the table. If I change
it to delete from *, it will return '0 records will be deleted, if I
return to to a select query, there are definitely records back there?
Any suggestions?
Thanks

Re: Where issue with delete query

On Wed, 19 Dec 2007 09:50:59 -0800 (PST), AP wrote:

> I have a single table delete query in ACC2003 that will only delete if
> I drop in a specific field and put the criteria 'is not null' I am
> just simply trying to delete all records from the table. If I change
> it to delete from *, it will return '0 records will be deleted, if I
> return to to a select query, there are definitely records back there?
> Any suggestions?
> Thanks

Delete TableName.* from TableName;

If you wish more specific help, you'll need to post the exact query
SQL that works, as well as the exact SQL of the query that doesn't
work? You can see it, we can't.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: Where issue with delete query

Ok, here is the deal, I am trying to speed up a few queries because
another user who was trying to delete ALL records from a table used
the following method:

DELETE [tbl_Daily Inserted].Inserted
FROM [tbl_Daily Inserted]
WHERE ((([tbl_Daily Inserted].Inserted) Is Not Null));

The is not null, causes slower performance so I changed it to this
DELETE [tbl_Daily Inserted].*
FROM [tbl_Daily Inserted];

And it says I will delete 0 records, if I change it back, I get the
message that i will delete all 38K records. There are no null fields.
Never had this probelm>??????

Re: Where issue with delete query

On Dec 19, 3:04 pm, AP wrote:
> Ok, here is the deal, I am trying to speed up a few queries because
> another user who was trying to delete ALL records from a table used
> the following method:
>
> DELETE [tbl_Daily Inserted].Inserted
> FROM [tbl_Daily Inserted]
> WHERE ((([tbl_Daily Inserted].Inserted) Is Not Null));
>
> The is not null, causes slower performance so I changed it to this
> DELETE [tbl_Daily Inserted].*
> FROM [tbl_Daily Inserted];
>
> And it says I will delete 0 records, if I change it back, I get the
> message that i will delete all 38K records. There are no null fields.
> Never had this probelm>??????

It's difficult to analyse the inexplicable with all the clues.
It's a lot more difficult without all the clues:

Jet?

Split Backend?

Within a Transaction?

What does
Debug.Print DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) FROM
[tbl_Daily Inserted]")(0)
show?

Have you tried:
DELETE * FROM [tbl_Daily Inserted]
?

The other user? ... opens the db ... how? He/she has his/her own copy
of the frontend? He/she opens the same file (frontend) the you open?
What?

If you're deleting a million-zillion records it may be faster to drop
and rebuild the table.

Is the query a saved query? How do you run it?