Updateable query problem. (1 Viewer)

E

expat1000

Registered User.

Local time

Today, 01:22

Joined

Apr 6, 2012

Messages

18

• Aug 23, 2019

• #1

I've been through the list of potential reasons for a query being non-updateable. I cannot spot any reason.

Code:Copy to clipboard

UPDATE twebrelay INNER JOIN ztWebRelay ON twebrelay.WebRelayID = ztWebRelay.SerialNo SET twebrelay.CBWIdentifier = [ztWebRelay].[CBWID];

twebrelay: Linked ODBC SQL Server table
has Primary Key
WebRelayID nvarchar(25), unique index, non null
CBWIdentifier nvarchar(25), unique index, non null

ztWebRelay: Local MS Access
has Primary Key
SerialNo short text (255) unique index non null
CBWID short text (255) unique index non null


There is no relationship between the tables. Thank you.

 

isladogs

MVP / VIP

Local time

Today, 09:22

Joined

Jan 14, 2017

Messages

18,216

• Aug 23, 2019

• #2

Is this the list of reasons you were referring to? http://allenbrowne.com/ser-61.html

Some questions
1. Can you update the SQL table itself manually in Access?
2. Do you have any other fields in your SQL table preventing updates. For example, Boolean fields with no default and some null values?
3. What message do you get when you run the query?
4. Have you tried UPDATE DISTINCTROW? Although specifying unique values can make queries read only, that's not always the case

 

E

expat1000

Registered User.

Local time

Today, 01:22

Joined

Apr 6, 2012

Messages

18

• Aug 23, 2019

• #3

Hi,
This list
https://stackoverflow.com/questions/24267398/why-is-my-query-not-updateable

The table is updateable in Access, including that specific field. Also, I append to it from ztWebRelay in another query without any problems so I'm leaning to thinking it has to be in the join but can't figure out what.

The error is: Must use updateable query.

 

isladogs

MVP / VIP

Local time

Today, 09:22

Joined

Jan 14, 2017

Messages

18,216

• Aug 23, 2019

• #4

Very similar list ...for obvious reasons.
You didn't answer points 2 & 4.

 

A

almahmood

Registered User.

Local time

Today, 13:52

Joined

Mar 28, 2017

Messages

47

• Aug 23, 2019

• #5

Please check if your linked ODBC SQL Server table has edit permission or not. Access Update query works in a different way than SQL Server.
1) You need to make sure that both of your queries are editable.
2) Also, many to many don't work.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 16:22

Joined

May 7, 2009

Messages

19,233

• Aug 23, 2019

• #6

how about using Left Join instead of Inner Join.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 04:22

Joined

Feb 19, 2002

Messages

43,263

• Aug 24, 2019

• #7

It isn't whether the join is inner or outer, it has to do with creating a Cartesian product. Are you joining on PK to FK or data field to data field? If it is the latter, you could be creating a Cartesian product where multiple records match each record and an update would cause conflicts.