👀 Views: 0 💬 Answers: 1 📅 Created: 2025-07-17

ms-access vba database

I'm sure I'm missing something obvious here, but Can someone help me understand I'm stuck on something that should probably be simple. I'm sure I'm missing something obvious here, but I'm working with a frustrating scenario in MS Access 2016 where I'm getting a 'Recordset is not updatable' behavior when trying to update records through a nested query in VBA. My current approach involves iterating through a recordset that is based on a SELECT statement that pulls data from multiple related tables. Here's the code snippet I’m using: ```vba Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.City='New York'", dbOpenDynaset) If Not rs.EOF Then rs.MoveFirst Do While Not rs.EOF rs.Edit rs!CustomerName = "Updated Name" rs.Update rs.MoveNext Loop End If rs.Close Set rs = Nothing Set db = Nothing ``` The behavior occurs at the `rs.Edit` line. I’ve tried switching the recordset type to `dbOpenSnapshot`, but that just returns a different behavior indicating that the recordset is read-only. I also made sure that none of the tables involved in the query are set to read-only in their properties. I understand that nested queries can often lead to these kinds of issues, especially if there are joins involved, but I need to update the records based on the results of this query. I've also verified that my permissions for the underlying tables are correct. Is there a way to work around this and still achieve the intended updates, or am I missing something in my approach? Any help would be greatly appreciated! I'd really appreciate any guidance on this. I'm working on a web app that needs to handle this. Is there a better approach? I'm using Vba 3.10 in this project. Am I approaching this the right way? I'm developing on Windows 10 with Vba. I'm open to any suggestions.