Operation must use an updatable query

[Guest]

HI All,

I have a piece of VBA code (Access2k, Win2k) that has run
weekly for the last 5 weeks perfectly. Now when I try to
run it all of the update queries won't run with the error
message

"Runtime error 3073, Operation must use an updatable query"

Code Follows:

DoCmd.RunSQL "UPDATE tblMatrix INNER JOIN tblClmntTrans ON
tblMatrix.Cusip = tblClmntTrans.Field6 SET
tblClmntTrans.FdFam = [familyname];"

The queries are simple lookups with one inner join. When I
recreate the queries outside of the code I get the same
result.

I tried the exact mdb that ran last week with new or old
data and it won't run.

There is an ODBC connection to SQL Server but there were
no major changes and those linked tables are not being
updated, it is a local table that is now not updatable.

I'm stumped and past my deadline!!!

Any ideas would be much appreciated.

Thanks,

alr

 

[John Vinson]

"Runtime error 3073, Operation must use an updatable query"

Code Follows:

DoCmd.RunSQL "UPDATE tblMatrix INNER JOIN tblClmntTrans ON
tblMatrix.Cusip = tblClmntTrans.Field6 SET
tblClmntTrans.FdFam = [familyname];"

Is there a unique Index on Field6? If not, Access can't be sure which
record in tblClmntTrans should be used for the update, and will make
the query non-updateable.

 

[Guest]

Hi John,

Thanks for looking into this!

In fact this exact code ran 5 weeks in a row without a
problem.

I imported everything to my local machine including SQL
Server tables (not linked) and the thing ran without a
hitch. There were some data changes in the SQL Server
lookups but no structural changes were made.

I will need to run this again next week so I am very
interested in what happened and how to prevent/fix things.

It's great that I got the result but still have no idea
what went wrong.

Thanks again,

alr

-----Original Message-----

"Runtime error 3073, Operation must use an updatable query"

Code Follows:

DoCmd.RunSQL "UPDATE tblMatrix INNER JOIN tblClmntTrans ON
tblMatrix.Cusip = tblClmntTrans.Field6 SET
tblClmntTrans.FdFam = [familyname];"

Is there a unique Index on Field6? If not, Access can't be sure which
record in tblClmntTrans should be used for the update, and will make
the query non-updateable.


.

 

[John Vinson]

I imported everything to my local machine including SQL
Server tables (not linked) and the thing ran without a
hitch. There were some data changes in the SQL Server
lookups but no structural changes were made.

I will need to run this again next week so I am very
interested in what happened and how to prevent/fix things.

If it's a SQL/Server table, you need to have BOTH a unique index on
the SQL table, and also Access must be aware that this field is the
unique identifier. I wonder if in moving and relinking the SQL tables,
this setting got changed?

 

[Guest]

Whenever I link SQL Server tables is asks for a unique ID
if there is no primary key but in this case the update was
to a local table, even the initial problem.

So that does not seem like the source of the problem. Any
other ideas.

Thanks,

alr