Access operation must use an updateable query

[TonyLackey]

Hi,

Firstly i'm not an expert, so apologies if this is a dum question!

I am linking Access 97 to Sybase via ODBC.

Get the following message 'Access operation must use an updateable query'.

I get round it by creating a new table in sybase and then running my query within sybase.

Code:

UPDATE amos_CompJob INNER JOIN MOFE_LastDone_Engine ON amos_CompJob.CompJobID = MOFE_LastDone_Engine.CompJobID SET amos_CompJob.LastDone = [MOFE_LastDone_Engine]![Last Done Date]
WHERE (((MOFE_LastDone_Engine.[Last Done Date])>#4/1/1990#));

Any ideas how to run the query from Access?


Thanks in advance,

T

 

[FoFa]

Use a subselect on your MOFE_LastDone_Engine instead of inner join it.
UPDATE amos_CompJob
SET amos_CompJob.LastDone = (select [MOFE_LastDone_Engine].[Last Done Date] from MOFE_LastDone_Engine
WHERE (((MOFE_LastDone_Engine.[Last Done Date])>#4/1/1990#)) AND (amos_CompJob.CompJobID = MOFE_LastDone_Engine.CompJobID))

 

[TonyLackey]

Same error

FoFa,

Thanks for your response, i have tried modifying the code as suggested but get the same error.

Any other suggestions?

 

[Pat Hartman]

When updating linked ODBC tables, Access requires that they have a unique primary key defined.

 

[TonyLackey]

Pat,

Thanks for that, the unique key is CompJobID but still get the same error.

Can you use this type of query in Access to update Sybase, one of our 'Technical bods' doesn't think you can? i.e Updating from a table external to Sybase.

MOFE_LastDone_Engine is an Access table, AMOS_CompJob is in Sybase where CompJobID is a one-one relationship.....

 

[TonyLackey]

Sorted !

When linking tables (seems to be Sybase specific) you must define the primary key for each linked table that you intend to update using Access.

I tried this and it worked,

Thanks for your help FoFa and Pat !