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

Best way of updating a table, based on the results of a query

P: n/a
Hi All,

I have a select query which is based on tblOne and tblTwo. The query selects
some of the records in tblOne and tblTwo, based on a Where condition.
What I need to do is to change the value in Field9 in tblOne for all the
records found by the query.
I am not sure if I should use an update query or if I use some vba code.
In case I will have to use an update query, is there a way to disable the
warning prompts of it? (Or any warnings given by a query for that matter).
If I open an action query from VBA, it raises some warnings, and I don't
need the warnings sometimes.

Regards,
Nicolae


Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Nicolae,
Given a choice, I favor doing things with SQL whenever possible. So, I'd go
with the UPDATE statement. You can suppress & manage warnings, etc., by
running the update statement inside a function in VB, giving you the best of
both techniques.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...
Hi All,

I have a select query which is based on tblOne and tblTwo. The query selects some of the records in tblOne and tblTwo, based on a Where condition.
What I need to do is to change the value in Field9 in tblOne for all the
records found by the query.
I am not sure if I should use an update query or if I use some vba code.
In case I will have to use an update query, is there a way to disable the
warning prompts of it? (Or any warnings given by a query for that matter).
If I open an action query from VBA, it raises some warnings, and I don't
need the warnings sometimes.

Regards,
Nicolae

Nov 13 '05 #2

P: n/a
Hi Alan,

Thank you for your oppinion. The guy who supervises me doesn't want me to
use sql query expressions in my code. He wants to be able to run a query
outside the code and to check the results. I must admit, it is easier to
debug the application this way instead of embeding the sql statements in the
vba code... And I would expect there is a way of running an existing query
from VBA. After this message I posted some other piece of code, which is
able to execute an external update query, but there is an error in the code.
Once I figure out what the problem is, there won't be necessary to embed
queries in the code (I think).

Regards,
Nicolae
"Alan Webb" <kn*****@hotmail.com> wrote in message
news:sV******************@news.uswest.net...
Nicolae,
Given a choice, I favor doing things with SQL whenever possible. So, I'd go with the UPDATE statement. You can suppress & manage warnings, etc., by
running the update statement inside a function in VB, giving you the best of both techniques.

Nov 13 '05 #3

P: n/a
Nicolae,
I don't supposed telling him to relax would do any good. You can use the
msgbox command to display the embedded SQL in a message box. You can also
set the caption property of a label control to your embedded SQL. But your
boss is a bit clueless because an update statement modifies the data and is
not meant to be a means of checking the results. If he needs validation
before vetting the changes you should build him views using SELECT
statements that show him what the changes will look like after the update
statement is run. Update Queries based on the Jet db engine (a bit of an
oxymoron, but . . .) allow you to view the query in datasheet view and
that's probably what your boss is used to working with. Work out a process
flow with him that allows him to validate the proposed changes and then
running the embedded SQL to finalize them. That way you can write in
whatever way makes the most sense and your boss can have the verification he
or she needs.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40******@duster.adelaide.on.net...
Hi Alan,

Thank you for your oppinion. The guy who supervises me doesn't want me to
use sql query expressions in my code. He wants to be able to run a query
outside the code and to check the results. I must admit, it is easier to
debug the application this way instead of embeding the sql statements in the vba code... And I would expect there is a way of running an existing query
from VBA. After this message I posted some other piece of code, which is
able to execute an external update query, but there is an error in the code. Once I figure out what the problem is, there won't be necessary to embed
queries in the code (I think).

Regards,
Nicolae
"Alan Webb" <kn*****@hotmail.com> wrote in message
news:sV******************@news.uswest.net...
Nicolae,
Given a choice, I favor doing things with SQL whenever possible. So,
I'd go
with the UPDATE statement. You can suppress & manage warnings, etc., by
running the update statement inside a function in VB, giving you the
best of
both techniques.


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.