Query Not Updatable (1 Viewer)

Learn2010

Registered User.
Local time
Today, 06:15
Joined
Sep 15, 2010
Messages
415
I have an Access database that is linked to a SQL back-end. It runs slow. I checked some things you guys gave in a prior post. Now, I have created a new database and imported everything into it. I had to go through and add 4 References to match the old DB. Nothing else changed. It is updating from the SQL back-end. It has an Autonumber field linked to a number field. Here is the Access SQL for the query. However, one of my queries is now not updatable.

START OF QUERY
UPDATE tblRecommendationsView INNER JOIN tblDropWaiverStatus ON tblRecommendationsView.WaiverStatusID = tblDropWaiverStatus.WaiverStatusID SET tblRecommendationsView.RecommendationNote = [RecommendationNote] & Chr(13) & Chr(10) & "Waiver Status: " & [tblDropWaiverStatus].[WaiverStatus]
WHERE (((tblRecommendationsView.WaiverStatusID) Is Not Null));
END OF QUERY

Is there something that I need to import to make this work? Or, is there something else I am missing?
 

Minty

AWF VIP
Local time
Today, 10:15
Joined
Jul 26, 2013
Messages
10,353
I'm not 100% sure but if you are using an inner join your where clause becomes redundant as you will only see records where the WaiverStatusID is equal in both tables and therefore cannot be null...
 

Learn2010

Registered User.
Local time
Today, 06:15
Joined
Sep 15, 2010
Messages
415
My point: This has worked thousands of times before I imported everything from the original database. What changes could have taken place with that import?

Thank you.
 

Minty

AWF VIP
Local time
Today, 10:15
Joined
Jul 26, 2013
Messages
10,353
Okay, you didn't mention that this was previously working.

When you linked the tables did you identify the unique key field in the non-auto number table? If you don't the table itself won't be editable.
Simple test open the table(s) can you edit them?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:15
Joined
Oct 29, 2018
Messages
21,357
My point: This has worked thousands of times before I imported everything from the original database. What changes could have taken place with that import?

Thank you.
Hi. In line with what Minty was saying, if you are trying to update a View in SQL Server, then you need to specify the primary key/unique index for the view when you linked it, to make it updateable.
 

Users who are viewing this thread

Top Bottom