USING A QUERY IN AN SQL PHRASE

[bifteki via AccessMonster.com]

I have written this code:

sql_str = "UPDATE dbo.tbl_soft_items " & _
"SET dbo.tbl_soft_items.fld_upgraded = 1 " & _
"WHERE dbo.tbl_soft_items.fld_software_item_id = dbo.
view_software_upg_from.fld_upgraded_from_item_id"

Debug.Print sql_str
DoCmd.RunSQL sql_str

However when I run it, an error occurs, which reads:
The column prefix "dbo.view_software_upg_from" does not match with a table
name or alias name used in the query.
I guess there' s a problem with using a view inside a query. How can I do
this without getting an error?

 

[Patrice]

This is not specific to views. SQL doesn't know view_software as you don't
have this in a FROM clause in your request. You likely want something such
as :

UPDATE ... SET .. FROM dbo.tbl_soft_items JOIN dvo.view_software_upg_frm
etc...

 

[Douglas J. Steele]

If you have linked tables to SQL Server, the tables would have been renamed
dbo_tbl_soft_items and dbo_view_software_upg_from.

If you're trying to do a pass-through query so that the query runs on the
server, you need to ensure that you've set the Connect property properly.
You do this under Query | SQL-specific | Pass-through on the menu when you
have the query open in Design view.

 

[Bill Mosca, MS Access MVP]

You have included a table in the WHERE clause that is not accounted for. Try
this:
UPDATE dbo.tbl_soft_items
SET dbo.tbl_soft_items.fld_upgraded = 1
WHERE dbo.tbl_soft_items.fld_software_item_id IN
( SELECT dbo.view_software_upg_from.fld_upgraded_from_item_id
FROM dbo.view_software_upg_from
)

 

[bifteki via AccessMonster.com]

I tried this one but an error occured again. I tried to analyze the view in
its original query as follows:

sql_str = "Update dbo.tbl_soft_items" & _
"Set dbo.tbl_soft_items.fld_upgraded = 1" & _
"WHERE dbo.tbl_soft_items.fld_software_item_id IN" & _
"(SELECT dbo.tbl_soft_items.fld_upgraded_from_item_id" & _
"FROM dbo.tbl_soft_items" & _
"WHERE (NOT (dbo.tbl_soft_items.fld_upgraded_from_item_id IS NULL)))
"

but an error "Incorrect synax near 'dbo' " occured again.
I am fairly an amateur, so I ask for your understanding if what I'm trying to
do seems silly.

You have included a table in the WHERE clause that is not accounted for. Try
this:
UPDATE dbo.tbl_soft_items
SET dbo.tbl_soft_items.fld_upgraded = 1
WHERE dbo.tbl_soft_items.fld_software_item_id IN
( SELECT dbo.view_software_upg_from.fld_upgraded_from_item_id
FROM dbo.view_software_upg_from
)

I have written this code:

[quoted text clipped - 12 lines]

I guess there' s a problem with using a view inside a query. How can I do
this without getting an error?

 

[Bill Mosca, MS Access MVP]

"Set dbo.tbl_soft_items.fld_upgraded = 1" & _

"WHERE dbo.tbl_soft_items.fld_software_item_id IN" & _

You missed a space after the 1 in "= 1".

A good way to debug an SQL string is to use the Immediate window. Open teh
Immediate window and type a question mark before the string like this:
?"Update dbo.tbl_soft_items" & _
"Set dbo.tbl_soft_items.fld_upgraded = 1" & _
"WHERE dbo.tbl_soft_items.fld_software_item_id IN" & _
"(SELECT dbo.tbl_soft_items.fld_upgraded_from_item_id" & _
"FROM dbo.tbl_soft_items" & _
"WHERE (NOT (dbo.tbl_soft_items.fld_upgraded_from_item_id IS
NULL)))"

Then place your cursor at the end of the string and press Enter. You will
get what Access sees as the SQL statement. In this case it comes out as:
Update dbo.tbl_soft_itemsSet dbo.tbl_soft_items.fld_upgraded = 1WHERE
dbo.tbl_soft_items.fld_software_item_id IN(SELECT
dbo.tbl_soft_items.fld_upgraded_from_item_idFROM dbo.tbl_soft_itemsWHERE
(NOT (dbo.tbl_soft_items.fld_upgraded_from_item_id IS NULL)))

Notice there is no space before the first WHERE.

--
Bill Mosca, MS Access MVP

I tried this one but an error occured again. I tried to analyze the view in
its original query as follows:

sql_str = "Update dbo.tbl_soft_items" & _
"Set dbo.tbl_soft_items.fld_upgraded = 1" & _
"WHERE dbo.tbl_soft_items.fld_software_item_id IN" & _
"(SELECT dbo.tbl_soft_items.fld_upgraded_from_item_id" & _
"FROM dbo.tbl_soft_items" & _
"WHERE (NOT (dbo.tbl_soft_items.fld_upgraded_from_item_id IS
NULL)))
"

but an error "Incorrect synax near 'dbo' " occured again.
I am fairly an amateur, so I ask for your understanding if what I'm trying
to
do seems silly.

You have included a table in the WHERE clause that is not accounted for.
Try
this:
UPDATE dbo.tbl_soft_items
SET dbo.tbl_soft_items.fld_upgraded = 1
WHERE dbo.tbl_soft_items.fld_software_item_id IN
( SELECT dbo.view_software_upg_from.fld_upgraded_from_item_id
FROM dbo.view_software_upg_from
)

I have written this code:

[quoted text clipped - 12 lines]

I guess there' s a problem with using a view inside a query. How can I
do
this without getting an error?

 

[John Vinson]

Notice there is no space before the first WHERE.

or the second one either!

John W. Vinson[MVP]

 

[bifteki via AccessMonster.com]

Hmm... You 're right... :-|
OK, I tried the new code and it does the job perfectly.
Thank you everyone!