This Recordset Is Not Updateable: SQL Server Backend, Breaks Upon Refresh

[RyanJames]

Hi All,

I've been lurking these forums for a very long time, but I finally have an issue that I cannot resolve with what is already available. I've been programming access VBA for about 14 years, but I simply have no clue with this one.

As the title suggests, I have an Access frontend with a SQL Server backend. This database has about 50 tables and has been working reliably for approximately 2 years. There are three main tables. UnitLot, which contains information about physical space, Starts, which has information about who is building the home and when, and OrderProcess, which defines which home designer will design the home and tracks their progress through the home ordering process.

Previously, before refreshing tables, I was able to do an inner join on these three tables and do small data changes. Whenever I try to change data now, I get the "This Recordset is Not Updateable" message. Likewise, If I try to use VBA to open a recordset and change data with these 3 tables, I get the same issue. Interestingly enough, I am able to write an UPDATE query that joins these 3 tables and use information from the UnitLot table in the WHERE clause to update data in the OrderProcess table. This makes me believe this may be an issue with Access, not SQL server.

What I've tried so far:
1.) Refreshed/relinked tables.
2.) Verified primary and foreign keys in SQL server.
3.) Verified Access identifies the primary keys as such.
4.) Defined the table relationships in the Access relationship manager.
5.) Removed a calculated field in the UnitLot table.
6.) Compact/Repair.

Other observations:
1.) Access recognized both relationships as 1 to 1.
2.) All tables are in the same datasource.
3.) I am able to update these tables when only 2 are involved in the query.
4.) Update queries work, so I don't think this is permissions related.

Is there anything else I am missing? It seems to me like this could require a change in the SQL server because of some limitation of Access that I am unaware of.

Thanks,
Ryan

 

[theDBguy]

Hi Ryan. Welcome to AWF!

You didn't mention which version of Access you're using, but there was a recent update to 2016/365 that caused a few issues. Just wondering if maybe you're affected too. Just a thought...

 

[RyanJames]

Hi Ryan. Welcome to AWF!

You didn't mention which version of Access you're using, but there was a recent update to 2016/365 that caused a few issues. Just wondering if maybe you're affected too. Just a thought...

Thanks for the welcome and the info. I am in fact on 365 at the moment and also have a 2016 machine displaying the same issue. I will track down the patch notes and see if I think I might need a roll-back.

 

[theDBguy]

Thanks for the welcome and the info. I am in fact on 365 at the moment and also have a 2016 machine displaying the same issue. I will track down the patch notes and see if I think I might need a roll-back.

Good luck!

 

[RyanJames]

All,

Just a follow up, I found a machine that was patched most recently back in mid April and encountered the same issue. I will continue to update this post. I am going to try refreshing tables one by one to see if I can narrow it down to a specific table.

 

[NauticalGent]

equivalent of the compact and repair on the SQL Server database

Is there such an animal?

 

[RyanJames]

Is there such an animal?

I ran all the compacting and reorder tools I know of with no luck. I even recreated the table that seems to be giving me issues completely including keys and indexes and ran an insert statement to repopulate it, but that resulted in the same problem.
I might create a copy of the DB and see if I can roll back to see what may have changed. I have full backups every day and incremental backups every 30 minutes.

 

[RyanJames]

First of all, I would like to thank everyone for their help. I've been working with Access/SQL for a very long time, so it is unusual to run into something I haven't seen before.

I was able to correct the issue and it was actually on the SQL Server side, or at least in how Access handles keys from SQL Server. I had a non-clustered unique index on the process table that was not built correctly. (I believe per the ANSI SQL standards, NULL should not violate unique, but it does in SQL server. I use these indexes to make a unique OR NULL constraint, and I guess this one was messed up somehow.)

I did not think this was the case because I was able to run action queries through the access query interface, but my SELECT queries would comeback immutable. Just in case someone stumbles upon this thread with a similar issue, everything above could serve as a checklist on first steps to take when troubleshooting a query issue like this.

Pat - I was trying to update through the typed SQL interface, but I was also having issues with forms and record sets.

Thanks again, everyone. Hopefully I can repay the favor in the future.

Ryan

 

[theDBguy]

First of all, I would like to thank everyone for their help. I've been working with Access/SQL for a very long time, so it is unusual to run into something I haven't seen before.

I was able to correct the issue and it was actually on the SQL Server side, or at least in how Access handles keys from SQL Server. I had a non-clustered unique index on the process table that was not built correctly. (I believe per the ANSI SQL standards, NULL should not violate unique, but it does in SQL server. I use these indexes to make a unique OR NULL constraint, and I guess this one was messed up somehow.)

I did not think this was the case because I was able to run action queries through the access query interface, but my SELECT queries would comeback immutable. Just in case someone stumbles upon this thread with a similar issue, everything above could serve as a checklist on first steps to take when troubleshooting a query issue like this.

Pat - I was trying to update through the typed SQL interface, but I was also having issues with forms and record sets.

Thanks again, everyone. Hopefully I can repay the favor in the future.

Ryan

Hi. Congratulations! Glad to hear you got it sorted out and thank you for sharing your findings. Cheers!