Problem using RunSQL in VBA code...

  • Thread starter Thread starter ...
  • Start date Start date
?

...

Hi all,

I have problem trying to make fully automatized delete of existing data in
table via VBA code.
I want to skip warning: "You are about to delete __ row(s)from the
specified table. ... Are you sure...?" that Access always asks.
Is it possible to answer YES on this questions, but without user seeing
warnings?
Thanks in advance.

Best regards,
Stole.

PS.
CODE works fine, but can't loose warning:
DoCmd.RunSQL "DELETE IDtel,PozivniBr,TelBr " & _
"FROM tblSnimTel"
 
You can either issue a DoCmd.SetWarnings False command before you run the
DoSQL command (and a DoCmd.SetWarnings True after!), or you can use the
Execute method:

CurrentDb.Execute "DELETE IDtel,PozivniBr,TelBr " & _
"FROM tblSnimTel", dbFailOnError

My preference is the latter, as it allows you to trap errors that may occur,
but I believe it does require that a reference be set to DAO. If you're
using Access 2000 or 2002, you might not have that reference set, so you'll
have to set it yourself.

BTW, there's no reason to list specific field names in the DELETE statement:
the entire row is deleted, not just specific fields. You can use "DELETE *
FROM tblSnimTel" or even "DELETE FROM tblSnimTel" with no problems.
 
The SUB displays how to use the .Execute method as an alternative. It
uses the a Transaction to prompt the user when the number of records
effected and determine if the transaction should continue or be
cancelled. I included this since there will be times where you want to
provide a user abort.

Sub dbTrans()

Dim db As DAO.Database
Dim strSQL As String
Dim countRecords As String

strSQL = "DELETE * from tblTest;"

Set db = CurrentDb
With db
BeginTrans
.Execute strSQL
countRecords = .RecordsAffected
If MsgBox(countRecords) = vbYes Then
Rollback
Else
CommitTrans
End If
End With

End Sub
 
Thank you David,

I will keep this code as I'm sure I'll need it later.

Best regards,
Stole.
 
Grasshopper, the laws of the Universe dictate that if you keep the code,
you'll never use it. It is only when you don't keep it and forget where
you saw the example that you'll have a dire need for the code.
 
Back
Top