Advanced Search

Access 2007 Query via Execute

/ Published in: Visual Basic
Save to your folder(s)
Running queries in Access to ignore warnings and get affected record count

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.