Delete query neither deletes data nor gives error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MattGauch - 13 Nov 2007 12:16 GMT

I have a delete query (DELETE tblImportStaging.* FROM tblImportStaging;) that
neither deletes the data from the subject table, nor displays any error
message when I right-click on the query and choose Open, or hit RUN when in
Design View. (The subject table IS in the current database, and I've not set
any security or permissions on any database objects) Why doesn't this query
do what I intend?

Reply to this Message

Allen Browne - 13 Nov 2007 12:24 GMT

You may have turned SetWarnings off (in a macro or in code), or suppressed
the confirmation dialogs (under Tools | Options.)

Alternatively, there may be no matching records to delete at the time the
query runs.

Try opening the Immediate window (Ctrl+G), and entering:
CurrentDb.Execute "Query1", dbFailOnError
replacing Query1 with the name of your query.
Explanation:
http://allenbrowne.com/ser-60.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a delete query (DELETE tblImportStaging.* FROM tblImportStaging;)
>that
[quoted text clipped - 6 lines]
> query
> do what I intend?

Reply to this Message

MattGauch - 13 Nov 2007 13:45 GMT

Of course, SetWarnings is the first thing I thought of. But the access team,
in their infinite wisdom, decided to take away users' ability to set their
own options in Access 2007! At least, I can't find Tools|Options or
Tools|Preferences anywhere!! I've been trying for days to set my keyboard
behavior, too, so that my arrow keys go tto the next character in the field,
rather than the next field, and of course, this ability isn't even mentioned
in Help anywhere!!

Reply to this Message

Allen Browne - 13 Nov 2007 14:03 GMT

You can set the confirmation options under:
Office Button | Access Options | Advanced | Editing | Confirm

Here's an interactive (Flash) page to let you know where to find those menu
items in the new version:
http://office.microsoft.com/en-us/access/HA102388991033.aspx

More info about converting to A2007:
http://allenbrowne.com/Access2007.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Of course, SetWarnings is the first thing I thought of. But the access
> team,
[quoted text clipped - 6 lines]
> mentioned
> in Help anywhere!!

Reply to this Message

Maurice - 13 Nov 2007 14:17 GMT

In addition to Allen's posting, try deleting a record from the table where
you know it exists. So build in a where statement. Check if that record is
being deleted and you know that in you previous statement there are no
records to delete.

I know you have to do some digging in the 2007 to find what you are looking
for but you've got some great pointers from Allen there.. ;-)

hth

Signature

Maurice Ausum

> Of course, SetWarnings is the first thing I thought of. But the access team,
> in their infinite wisdom, decided to take away users' ability to set their
[quoted text clipped - 3 lines]
> rather than the next field, and of course, this ability isn't even mentioned
> in Help anywhere!!

Reply to this Message