Access2000: Suppressing messages for Append/Delete queries

[Arvi Laanemets]

Hi

Is there a way to suppress all messages like "You are about to delete ...",
"You are about to ran an append query ...", etc. invoked by
DoCmd.OpenQuery/DoCmd.RunSQL, until VBA code is processed.

Application.DisplayAlerts=False doesn't affect those messages.

 

[Ofer Cohen]

Instead of DoCmd.RunSQL try

CurrentDb.Execute("Delete * From TableName"), dbFailOnError

*******************
Or, for DoCmd.OpenQuery

CurrentDb.Execute("QueryName"), dbFailOnError

Or,

Docmd.SetWarnings False
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True


*** Note: it is important to set the warnings back on after the query is
executed ***

 

[Arvi Laanemets]

Thanks

DoComd.SetWarnings will do!

 

[Allen Browne]

Arvi, can I encourage you to consider Ofer's first suggestion?

If you set warnings off, you will not know if the action query succeeded or
not. More info in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Arvi Laanemets]

Hi

I'll look for it later - momentarily I needed a fast and simple solution.
And queries are extremly simple, so hardly anything may go wrong way, and
when it does, nothing incorrigible is happening.

The procedure reads monthly reports (excel workbooks) into Access database.
When report data of given type for this month exist in Access database, user
is asked for further actions - depending on users desicion, matching data
are deleted from Access database (1st action query), or the procedure is
aborted.
Then all existing data from an intermediate Access table are deleted (2nd
action query). After that the intermediate table is filled with new data
from report file (it's opened as recordset, and processed row-wise)
At last all data from intermediate table are appended to Access table (3rd
action query).

The only moment where something may go wrong way, is when manipulating
recordsets. When this happens, the procedure is aborted, this report will be
missing from database, and the user can always try to read it in again.


Arvi

 

[Allen Browne]

Okay, that's fine.

If someone else had the database open and was editing another record so that
it did not delete, you would be quite happy with the results. Or if all the
records failed to delete (e.g. because some required field was null, or a
duplicate index violated, or a foreign key value that didn't work, or a
validation rule not met), you would be quite happy not to know about it and
just assume it all worked okay.