?
Solved

Access : This recordset is not updateable

Posted on 2010-01-06
3
Medium Priority
?
379 Views
Last Modified: 2013-11-28
Hi ,

(I am fairly sure of my diagnostics/conclusions below)

I had a simple form which shows information from 3 tables.
(i..e A MASTER table and 2 joins )

The form has been working fine for weeks but today I added a fourth table (1 field only)
When I added the 4th table my form gave the "Recordset is not updateable message".
I cannot update my table anymore ! Help!
When I remove the 4th table the form fails too as it seems to have lost most of its data (excuse the vagueness)

See code below.
Any thoughts appreciated.

SELECT MASTER.Updates, MASTER.ID, MASTER.COMMENTS, MASTER.LOCATION, MASTER.FILE_BAR, MASTER.FIRST_NAME, MASTER.SURNAME, MASTER.Long_string, MASTER.ADD1, MASTER.ADD2, MASTER.ADD3, MASTER.ADD4, MASTER.SURNAME_2, MASTER.FIRST_NAME_2, MASTER.SURNAME_3, MASTER.FIRST_NAME_3, MASTER.BU_NAME, MASTER.BU_CODE, MASTER.FILE_TYPE, MASTER.FILE_NUM, MASTER.ACTION_1, [BU Names].[Business Unit], MASTER.Last_updated_by, MASTER.SCV_Number, MASTER.NOTES, ALL_fs.Field2 AS [FS Status], Files_Moved_During_Closures.From_To
FROM ((MASTER LEFT JOIN [BU Names] ON MASTER.BU_CODE = [BU Names].BU) LEFT JOIN ALL_fs ON MASTER.FILE_BAR = ALL_fs.Barcode) LEFT JOIN Files_Moved_During_Closures ON MASTER.FILE_BAR = Files_Moved_During_Closures.barcode;

Open in new window

0
Comment
Question by:Patrick O'Dea
3 Comments
 
LVL 7

Accepted Solution

by:
js-profi earned 1000 total points
ID: 26192175
with the statement above you can't find syntax errors. if you would make a fine puryfing you would see that the parantheses were wrongly closed after the third joint.
SELECT MASTER.Updates, MASTER.ID, MASTER.COMMENTS, MASTER.LOCATION, 
       MASTER.FILE_BAR, MASTER.FIRST_NAME, MASTER.SURNAME, 
       MASTER.Long_string, MASTER.ADD1,MASTER.ADD2, MASTER.ADD3, 
       MASTER.ADD4, MASTER.SURNAME_2, MASTER.FIRST_NAME_2, 
       MASTER.SURNAME_3, MASTER.FIRST_NAME_3, MASTER.BU_NAME, 
       MASTER.BU_CODE, MASTER.FILE_TYPE, MASTER.FILE_NUM, MASTER.ACTION_1, 
       [BU Names].[Business Unit], 
       MASTER.Last_updated_by, MASTER.SCV_Number, MASTER.NOTES, 
       ALL_fs.Field2 AS [FS Status], 
       Files_Moved_During_Closures.From_To
FROM ((MASTER LEFT JOIN [BU Names] ON MASTER.BU_CODE = [BU Names].BU) 
              LEFT JOIN ALL_fs ON MASTER.FILE_BAR = ALL_fs.Barcode) 
              LEFT JOIN Files_Moved_During_Closures ON MASTER.FILE_BAR = Files_Moved_During_Closures.barcode;

Open in new window

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26192576
You may also benefit by changing the Recordset Property of your query to "Dynaset (Inconsistent Updates)"
J
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 31673581
ok
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question