Posted By

jfherring on 06/19/12


Tagged

access vba


Versions (?)

Access 2007 Query via Execute


 / Published in: Visual Basic
 

Running queries in Access to ignore warnings and get affected record count

  1. 'Using Execute instead of DoCmd.RunSQL suppresses the warning dialogs
  2. 'automatically without having to mess with turning DoCmd.SetWarnings
  3. 'off and then back on when finished (if you remember).
  4. 'Execute can be used to run a query string you construct, or an Access
  5. 'stored query.
  6. Dim strSql As String
  7. strSql = "UPDATE table SET field = newvalue WHERE field = currentvalue"
  8. CurrentDb.Execute (strSql), dbFailOnError
  9.  
  10. 'The parameter "dbFailOnError" terminates the execute command if the,
  11. 'query fails, so I use it pretty much always.
  12.  
  13. 'Also, when running this, you can return the number of rows affected
  14. Dim intRowsAffected As Integer
  15. intRowsAffected = CurrentDb.RecordsAffected
  16.  
  17.  
  18. 'If for some reason you are expecting lots of errors, you may want to
  19. 'stick with DoCmd.RunSQL and leave DoCmd.SetWarnings = true. I don't
  20. 'like this as it always presents the user with "You are about to
  21. 'update a bunch of records, are you sure" or something similar
  22. 'depending on the operation you are running.

Report this snippet  

You need to login to post a comment.