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

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

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

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" <nospam@please.cxm> wrote in message
news:40edf679$1@duster.adelaide.on.net...[color=blue]
> Hi All,
>
> I have a select query which is based on tblOne and tblTwo. The query[/color]
selects[color=blue]
> 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
>
>
>
>[/color]

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

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" <knogeek@hotmail.com> wrote in message
news:sVoHc.1261$Ni2.66562@news.uswest.net...[color=blue]
> Nicolae,
> Given a choice, I favor doing things with SQL whenever possible. So, I'd[/color]
go[color=blue]
> with the UPDATE statement. You can suppress & manage warnings, etc., by
> running the update statement inside a function in VB, giving you the best[/color]
of[color=blue]
> both techniques.
>[/color]

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

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" <nospam@please.cxm> wrote in message
news:40ee2915@duster.adelaide.on.net...[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
code.[color=blue]
> 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" <knogeek@hotmail.com> wrote in message
> news:sVoHc.1261$Ni2.66562@news.uswest.net...[color=green]
> > Nicolae,
> > Given a choice, I favor doing things with SQL whenever possible. So,[/color][/color]
I'd[color=blue]
> go[color=green]
> > with the UPDATE statement. You can suppress & manage warnings, etc., by
> > running the update statement inside a function in VB, giving you the[/color][/color]
best[color=blue]
> of[color=green]
> > both techniques.
> >[/color]
>
>[/color]