The problem with DoCmd.RunSQL is that it ignores any errors.
Either of the following will display any error messages received
by the query. If using DAO, use Currentdb.Execute
strSQL,dbfailonerror.. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText You can then remove the docmd.setwarnings lines.
I have been using a replacement for RunSQL in my most active current
project. I call the function SQLRun and you can search and replace
to drop it in where using DoCmd.RunSQL and it will work
transparently. You might have unnecessary SetWarnings statements
then, but you can take those out as well. The code is after my
signature. You may or may not want to uncomment the transactions --
I found that it was causing some problems, so I took it out.
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/
Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler
If db Is Nothing Then Set db = CurrentDb
' DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
' DBEngine.Workspaces(0).CommitTrans
exitRoutine:
SQLRun = lngRecordsAffected
' Debug.Print strSQL
Exit Function
errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
vbExclamation, "Error in SQLRun()"
' Debug.Print "SQL Error: " & strSQL
' DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function
