.adp update query using 2 tables

[Tom]

I am trying to build an update query or stored procedure or in line function
to update one table with the values from the matching rows of another table.
Only one table is allowed in a update query in an .adp file, but there seems
to be a way to get there, I have not been able to figure it out, can anyone
supply a solution to my problem, it would be greatly appreciated?

tom

 

[Sylvain Lafontaine]

After reading your post a couple of times, I still don't understand what you
want to do exactly. Also, the official newsgroup for ADP is
microsoft.public.access.adp.sqlserver.

 

[Tom]

I have a primary table of information on products, several thousand lines, 20
columns.

I have a second table that has a subset of the columns from the primary
table (3).

The second table has updated vendor information that needs to be placed in
the primary table.

In an .mdb database you can use an update query to pull the new information
into the primary table using an UPDATE QUERY with two tables.

when you try the same task in a .adp database access tells you that you are
only allowed one table in an update query, hence the question how to update a
primary table with information from a secondary table in a .adp database
using an UPDATE QUERY.

Thanks for the link to the adp site, will keep looking.

tom

 

[Sylvain Lafontaine]

On SQL-Server, you can update only one table at a time but you can use more
than one or two tables as the source of an Update Query. What query did you
try exactly?

 

[Tom]

On the create query tab, I selected 2 tables, then under query type I
selected "Update Query", Access then tells me that I can only use one table.
The question is how to do an update query using 2 tables in an .adp Access
file?

all help will be greatly appreciated
tom

 

[Sylvain Lafontaine]

That's right, on SQL-Server, you can only update one table at a time. If
you have to update more than one table, you have to perform two or more
update queries.

With JET, there's also something that's called the Multi-step procedure:
when JET needs to update more than one table from a view or a select query,
it makes multiple updates but this give you the impression that a single
update is taking place because the source is a single view or select query.

When working against a view, multiple update are also possible if you are
using an Instead Of trigger behind the scene.

However, the multistep procedure of JET doesn't work reliably against
SQL-Server and using Instead Of triggers is an advanced feature of
SQL-Server.

 

[Sylvain Lafontaine]

However, this doesn't mean that you cannot use more than a single table as
the source of the data but the syntax between JET et T-SQL (the dialect used
with SQL-Server) are slightly different when it comes to the Update.

 

[Dave Robinson]

On the create query tab, I selected 2 tables, then under query type I
selected "Update Query", Access then tells me that I can only use one
table.
The question is how to do an update query using 2 tables in an .adp Access
file?

all help will be greatly appreciated
tom