Would this update SQL query work in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would this query run in Access?
I have SQL Server background so I wrote it manually:

update Assets
set
Assets.in_svc_date = dbo_ast_book.date_ast_placed_in_service
from dbo_ast_book
where
Assets.assetno = dbo_ast_book.ast_id and
dbo_ast_book.ast_id = "26203" and
dbo_ast_book_nbr = 1

The problem is it gives me a syntax error (missing operator).
How come?

Thanks,
VM
 
Would this query run in Access?
I have SQL Server background so I wrote it manually:

update Assets
set
Assets.in_svc_date = dbo_ast_book.date_ast_placed_in_service
from dbo_ast_book
where
Assets.assetno = dbo_ast_book.ast_id and
dbo_ast_book.ast_id = "26203" and
dbo_ast_book_nbr = 1

The problem is it gives me a syntax error (missing operator).
How come?
Click to expand...

If you're updating data in one table to a value from another table,
you need to use a Query joining the two tables:

UPDATE Assets
INNER JOIN dbo_ast_book ON Assets.AssetNo = dbo_ast_book.ast_id
SET Assets.in_svc_date = dbo_ast_book.date_ast_placed_in_service
WHERE dbo_ast_book.ast_id = "26203" and dbo_ast_book_nbr = 1;

The WHERE clause join won't allow the recordset to be updated; the
SQL/92 JOIN will.
 
Thanks.
Now it doesn't give me a syntax error but it does tell me "Operation must
use an updateable query".
Why?

The Assets table is an Access table with PK AssetNo.

dbo_ast_book is a linked table from Sybase.

VM
 
Thanks.
Now it doesn't give me a syntax error but it does tell me "Operation must
use an updateable query".
Why?

The Assets table is an Access table with PK AssetNo.

dbo_ast_book is a linked table from Sybase.
Click to expand...

That may be the problem: since it's a remote database Access may not
be able to confidently identify which record in dbo_ast_book. Is the
Asset ID field a Primary Key in the Sybase table, and did you identify
it as such in the ODBC linking operation?

You might need to update using a Subquery instead:

UPDATE Assets
SET Assets.in_svc_date = (SELECT
dbo_ast_book.date_ast_placed_in_service FROM dbo_ast_book
WHERE dbo_ast_book.ast_id = "26203" and dbo_ast_book_nbr = 1)
WHERE Assets.AssetID = "26203";
 
The sybase table (ast_book) doesn't have any primary keys.

dbo_ast_book.ast_id and dbo_ast_book.ast_book_nbr (apparently the "dbo_"
phrase was added to the name of the table during linking) were identified as
such in the Access linking operation.

I had previously tried it with the subselect but I still received the
"Operation must use an updateable query".

Vaughn
 
The sybase table (ast_book) doesn't have any primary keys.
Click to expand...

Then how can you be assured that there is only one record with that
ID? And if there are multiple records, which do you choose to provide
the update? THAT'S why the query is not updateable: Access can't
determine an unambiguous value for the update.
dbo_ast_book.ast_id and dbo_ast_book.ast_book_nbr (apparently the "dbo_"
phrase was added to the name of the table during linking) were identified as
such in the Access linking operation.

I had previously tried it with the subselect but I still received the
"Operation must use an updateable query".
Click to expand...

You may need to use DLookUp() to look up the first (arbitrary) record
for each ID, and update to the DLookUp result.
 
The sybase table (ast_book) doesn't have any primary keys.
Click to expand...

An ODBC-linked table without a unique index or unique constraint is not
updateable.

I'm not sure about this, but you *may* be able to do it if you can create an
indexed view on the table and link to that. Failing that, I think you'd need
to use a pass-through query.
 
In my case, it's more about having one query that does everything. In our
application, the user has a query that creates a temporary table *and*
another query that updates based on that table. So, if I want to do
anything, I have to open all three queries (there's another query that does
something else..).
I'd like to be able to create only one query that creates the table/view and
updates the table. But everything would be in the same query (not 2 or 3
queries for one process).

Would it be possible to create a temp-table with the data (maybe at the
beginning of the query) and then do the update of the table? It'd be
something like this (Everything in 1 query):

SELECT ast_entity_id, ast_id, date_ast_placed_in_service into [TempTableX]
from dbo_ast_book

Update Assets
INNER JOIN TempTableX ON Assets.AssetNo = TempTableX.ast_id
SET Assets.in_svc_date = TempTableX.date_ast_placed_in_service
WHERE TempTableX.ast_id = "26203";

If something like this is posible, would I be able to create the temp table
so it only exists when running this instance of the query? As soon as it's
done, the table's disposed.

And thanks again for all the help,
VM
 
In my case, it's more about having one query that does everything. In our
application, the user has a query that creates a temporary table *and*
another query that updates based on that table. So, if I want to do
anything, I have to open all three queries (there's another query that does
something else..).
I'd like to be able to create only one query that creates the table/view and
updates the table. But everything would be in the same query (not 2 or 3
queries for one process).

Would it be possible to create a temp-table with the data (maybe at the
beginning of the query) and then do the update of the table? It'd be
something like this (Everything in 1 query):

SELECT ast_entity_id, ast_id, date_ast_placed_in_service into [TempTableX]
from dbo_ast_book

Update Assets
INNER JOIN TempTableX ON Assets.AssetNo = TempTableX.ast_id
SET Assets.in_svc_date = TempTableX.date_ast_placed_in_service
WHERE TempTableX.ast_id = "26203";

If something like this is posible, would I be able to create the temp table
so it only exists when running this instance of the query? As soon as it's
done, the table's disposed.

And thanks again for all the help,
VM
 
In my case, it's more about having one query that does everything. In our
application, the user has a query that creates a temporary table *and*
another query that updates based on that table. So, if I want to do
anything, I have to open all three queries (there's another query that does
something else..).
Click to expand...

Access simply doesn't do what you want... *in Queries*. Functionally,
for the user, however it should make no difference. They click a
button on a Form, it calls a VBA subroutine which runs three (or four,
or five) queries, they get their results.
 
VM

My friend, we have seen this post before. Unfortunately it would seem that you haven't picked u
on the key (no pun intended) requirement for a table to have a primary key to be updateable

Here's the plan if you are still struggling
put a PK on ast_entity_i
make seperate queries for the record selection and updat
create a subroutine which is called by whichever event you need it to be called b
make that subroutine use the appropriate querie

Are you noticing something here? Structured programming (in an Event-driven fashion) may no
be the prettiest thing since sliced bread, but it is certainly more elegant than the Spaghetti
paradigm you are proposing. Imagine incorporating greater functionality, fixing errors or whateve
else needs to be done if you implement this code inline with the rest of the Event-driver and Form
driver code.

Way too much work than there needs to be, instead use subs and get with the program (ok, pun
intended)

Chop

----- VM wrote: ----

In my case, it's more about having one query that does everything. In ou
application, the user has a query that creates a temporary table *and
another query that updates based on that table. So, if I want to d
anything, I have to open all three queries (there's another query that doe
something else..)
I'd like to be able to create only one query that creates the table/view an
updates the table. But everything would be in the same query (not 2 or
queries for one process)

Would it be possible to create a temp-table with the data (maybe at th
beginning of the query) and then do the update of the table? It'd b
something like this (Everything in 1 query)

SELECT ast_entity_id, ast_id, date_ast_placed_in_service into [TempTableX
from dbo_ast_boo

Update Asset
INNER JOIN TempTableX ON Assets.AssetNo = TempTableX.ast_i
SET Assets.in_svc_date = TempTableX.date_ast_placed_in_servic
WHERE TempTableX.ast_id = "26203"

If something like this is posible, would I be able to create the temp tabl
so it only exists when running this instance of the query? As soon as it'
done, the table's disposed

And thanks again for all the help
V
 
Back
Top