Advanced Search

Return to Snippet

Revision: 57978
at June 19, 2012 02:55 by jfherring
Updated Code 
'Using Execute instead of DoCmd.RunSQL suppresses the warning dialogs
'automatically without having to mess with turning DoCmd.SetWarnings
'off and then back on when finished (if you remember).
'Execute can be used to run a query string you construct, or an Access
'stored query.
Dim strSql As String
strSql = "UPDATE table SET field = newvalue WHERE field = currentvalue"
CurrentDb.Execute (strSql), dbFailOnError

'The parameter "dbFailOnError" terminates the execute command if the,
'query fails, so I use it pretty much always.

'Also, when running this, you can return the number of rows affected
Dim intRowsAffected As Integer
intRowsAffected = CurrentDb.RecordsAffected


'If for some reason you are expecting lots of errors, you may want to
'stick with DoCmd.RunSQL and leave DoCmd.SetWarnings = true.  I don't
'like this as it always presents the user with "You are about to
'update a bunch of records, are you sure" or something similar 
'depending on the operation you are running.

Revision: 57977
at June 19, 2012 02:50 by jfherring
Initial Code 
'Using Execute instead of DoCmd.RunSQL suppresses the warning dialogs
'automatically without having to mess with turning DoCmd.SetWarnings
'off and then back on when finished (if you remember).
'Execute can be used to run a query string you construct, or an Access
'stored query.
Dim strSql As String
strSql = "UPDATE table SET field = newvalue WHERE field = currentvalue"
CurrentDb.Execute (strSql), dbFailOnError

'The parameter "dbFailOnError" terminates the execute command if the,
'query fails, so I use it pretty much always.

'Also, when running this, you can return the number of rows affected
Dim intRowsAffected As Integer
intRowsAffected = CurrentDb.RecordsAffected


'If for some reason you are expecting lots of errors, you may want to
'stick with DoCmd.RunSQL and leave DoCmd.SetWarnings = true.  I don't
'this as it always presents the user with "You are about to update a
'bunch of records, are you sure" or something similar depending on the
'operation you are running.
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Access 2007 Query via Execute

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic