How to delete all records from tables in VBA wihout getting the confirm delete prompt ?

  • Thread starter Thread starter Adrian
  • Start date Start date
A

Adrian

Hi,

May I know how to delete all records from tables in VBA wihout getting
the confirm delete prompt ? Thanks...
 
Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [Table1]"
DoCmd.RunSQL DeleteEverything

Hoping this will help ;-)
 
This also works:

Dim strSQL As String

strSQL = "DELETE * FROM MyTableName"
CurrentDb.Execute strSQL, dbFailOnError
 
Do you have to reverse the SetWarnings Fasle command at the end so not to
stop other important warnings latter on.

Thanks
AMH
 
JasonS said:
Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [Table1]"
DoCmd.RunSQL DeleteEverything

Hoping this will help ;-)
Click to expand...

As AMH points out you forgot to turn the warnings back on. You
should also be adding this line in any error handling. My standard
blurb follows.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top