Action query from code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?
 
If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
One way is settng the warnings off

Docmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True
****************************
Another way will be

CurrentDb.QueryDefs("QueryName").Execute dbFailOnError
 
One way is settng the warnings off

Docmd.SetWarnings False
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings True

****************************
Another way will be

CurrentDb.QueryDefs("QueryName").Execute dbFailOnError
 
Wow, Ofer, I learn something every day here. I can sure see the value of the
syntax for the Execute method you are using. I would have written it as:
CurrentDb.Execute("QueryName"), dbFailOnError

Now a note about this method for the OP:

In addition to bypassing the warnings method, it is, IMHO, the very best way
to run action queries in Access. It is much faster than any other method
because it bypasses the Access IU and goes directly to Jet. The only caution
is that you should always use the dbFailOnError parameter; otherwise you will
not know an error occured. Remember what I said about bypassing Access?
That means if you don't use dbFailOnError, Access will not know there was an
error.
 
Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again



John Spencer said:
If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Todd Bacon said:
How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?
Click to expand...
Click to expand...
 
It's possible that the update query is updating the same table that the form
is bounded to.
Like trying to update the same record on the same time, and that preduce the
error.

Is that the case?
If so, why do you need to run an update query if you can update the field
directly through the form?

--
Good Luck
BS"D


Todd Bacon said:
Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again



John Spencer said:
If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Todd Bacon said:
How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?
Click to expand...
Click to expand...
Click to expand...
 
Sounds as if the update is trying to change the same record that is
showing in your form. If you need to do this, you can try to force a
save of the bound record before you run the update query.

If Me.Dirty = True Then Me.Dirty = False
....
DbAny.Execute StrSQL, dbFailOnError
....


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Todd said:
Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again



John Spencer said:
If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Todd Bacon said:
How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?
Click to expand...
Click to expand...
Click to expand...
 
The form has only some control and a few lables, I don't want the users
changing anything here. All the changes are generated in code or pulled from
other locations.
'I use alot of Dlookup()'

Ofer Cohen said:
It's possible that the update query is updating the same table that the form
is bounded to.
Like trying to update the same record on the same time, and that preduce the
error.

Is that the case?
If so, why do you need to run an update query if you can update the field
directly through the form?

--
Good Luck
BS"D


Todd Bacon said:
Thanks, now I get another message:

When I exit the form that contains the routine I get:

Write Conflict
This record has been changed by another user......

Any ideas?

Thanks again



John Spencer said:
If you are using code to execute the query you can add DoCmd.SetWarnings to
your code

DoCmd.SetWarnings False
'Execute your SQL
DoCmd.SetWarnings True

I would advise you to always turn the warning back on. And have an error
handler that does the same. Otherwise you can find yourself in the
situation where you don't get warnings when you expect to.

Better yet is to use the execute method instead of DoCmd.RunsSQL, that way
you don't need to turn warning on and off


Dim dbAny as DAO.Database, strSQL as String
Set DbAny = DbEngine(0)(0)
strSQL ="UPDATE myTable Set [Field1]=0"
DbAny.Execute StrSQL, dbFailOnError
.....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

How do I disable the "You are about to update x Row(s)" message when I'm
executing an SQL statement from code?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top