By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Update Query Confirmation

P: n/a
I am incredibly new to access and trying to learn as I go. I have set up a
few queries one of which is executed by a docmd.runsql in visual basic.
Because it is an update query it asks for confirmation every time it wants
to update the table. Is there any way to get around this?
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
One way is to turn SetWarnings off, and turn it on again after the query
runs:
DoCmd.SetWarnings False

A better way is to Execute the query. Not only does this avoid the warning
message, but you can ask for an error if something goes wrong:
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon Mackie" <aw*****@telus.net> wrote in message
news:_YAZb.42443$Hy3.18330@edtnps89...
I am incredibly new to access and trying to learn as I go. I have set up a
few queries one of which is executed by a docmd.runsql in visual basic.
Because it is an update query it asks for confirmation every time it wants
to update the table. Is there any way to get around this?

Nov 12 '05 #2

P: n/a
Alright. The second way kept on giving me an error of Too Few Parameters or
something like that. I am assuming that it is because the SQL statement is
not formatted as required somehow... Anyways I saved the query and now am
not sure how to refer to it in the execute statement. I am trying the second
way because I need error checking of some kind and if I am not mistaken the
DoCmd.RunSQL does not allow for that.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
One way is to turn SetWarnings off, and turn it on again after the query
runs:
DoCmd.SetWarnings False

A better way is to Execute the query. Not only does this avoid the warning
message, but you can ask for an error if something goes wrong:
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon Mackie" <aw*****@telus.net> wrote in message
news:_YAZb.42443$Hy3.18330@edtnps89...
I am incredibly new to access and trying to learn as I go. I have set up a few queries one of which is executed by a docmd.runsql in visual basic.
Because it is an update query it asks for confirmation every time it wants to update the table. Is there any way to get around this?


Nov 12 '05 #3

P: n/a
You can Exeucte a saved query statement: just use the name of teh query in
place of "strSql".

Do you have an expression in your saved query such as:
Forms!MyForm!MyTextbox
If so, that won't work with Execute. Instead, you need to concatenate the
value into the string.

Example:
Dim strSQL As String
Dim db As DAO.Database

strSQL = "INSERT INTO ... WHERE Surname = """ & Forms!MyForm!MyTextBox &
""";"
Set db = dbEngine(0)(0)
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected " records inserted."
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon M" <aw*****@telus.net> wrote in message
news:FPLZb.33016$n17.29730@clgrps13...
Alright. The second way kept on giving me an error of Too Few Parameters or something like that. I am assuming that it is because the SQL statement is
not formatted as required somehow... Anyways I saved the query and now am
not sure how to refer to it in the execute statement. I am trying the second way because I need error checking of some kind and if I am not mistaken the DoCmd.RunSQL does not allow for that.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
One way is to turn SetWarnings off, and turn it on again after the query
runs:
DoCmd.SetWarnings False

A better way is to Execute the query. Not only does this avoid the warning
message, but you can ask for an error if something goes wrong:
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon Mackie" <aw*****@telus.net> wrote in message
news:_YAZb.42443$Hy3.18330@edtnps89...
I am incredibly new to access and trying to learn as I go. I have set up
a few queries one of which is executed by a docmd.runsql in visual
basic. Because it is an update query it asks for confirmation every time it

wants to update the table. Is there any way to get around this?



Nov 12 '05 #4

P: n/a
Sorry to be a bother yet again but it gives me a big evil user-defined type
not defined on the Dim db As DAO.Database line. I've searched all over...
can not find anywhere to include libraries, etc.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
You can Exeucte a saved query statement: just use the name of teh query in
place of "strSql".

Do you have an expression in your saved query such as:
Forms!MyForm!MyTextbox
If so, that won't work with Execute. Instead, you need to concatenate the
value into the string.

Example:
Dim strSQL As String
Dim db As DAO.Database

strSQL = "INSERT INTO ... WHERE Surname = """ & Forms!MyForm!MyTextBox & """;"
Set db = dbEngine(0)(0)
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected " records inserted."
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon M" <aw*****@telus.net> wrote in message
news:FPLZb.33016$n17.29730@clgrps13...
Alright. The second way kept on giving me an error of Too Few Parameters or
something like that. I am assuming that it is because the SQL statement is
not formatted as required somehow... Anyways I saved the query and now am not sure how to refer to it in the execute statement. I am trying the

second
way because I need error checking of some kind and if I am not mistaken

the
DoCmd.RunSQL does not allow for that.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
One way is to turn SetWarnings off, and turn it on again after the query runs:
DoCmd.SetWarnings False

A better way is to Execute the query. Not only does this avoid the

warning message, but you can ask for an error if something goes wrong:
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon Mackie" <aw*****@telus.net> wrote in message
news:_YAZb.42443$Hy3.18330@edtnps89...
> I am incredibly new to access and trying to learn as I go. I have
set up
a
> few queries one of which is executed by a docmd.runsql in visual

basic. > Because it is an update query it asks for confirmation every time it

wants
> to update the table. Is there any way to get around this?



Nov 12 '05 #5

P: n/a
You need a reference to the DAO library for your version of Access.

Details:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brandon M" <aw*****@telus.net> wrote in message
news:n17_b.41541$Ff2.32804@clgrps12...
Sorry to be a bother yet again but it gives me a big evil user-defined type not defined on the Dim db As DAO.Database line. I've searched all over...
can not find anywhere to include libraries, etc.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.