This Recordset Is Not Updateable: SQL Server Backend, Breaks Upon Refresh (1 Viewer)

RyanJames

New member
Local time
Today, 08:34
Joined
May 12, 2022
Messages
5
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

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 08:34
Joined
May 12, 2022
Messages
5
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

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 08:34
Joined
May 12, 2022
Messages
5
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,981
Check to make sure that the PKs are still correctly defined and that the relationships are still there. If you can run the equivalent of the compact and repair on the SQL Server database do that or ask the DBA to do it for you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,981
Yes there is. I think it might be called "reorg" or something line that. Relational databases get fragmented just the way hard drives do and they need the same type of maintenance, especially indexes. It really depends on how much add/delete you have of "internal" records and how much free space was defined when the database was configured. Typically new records are added to the end of the database just as they are with Access but if you are using a Clustered Index, records are added in that order. For example, your PK is EmployeeID and is an autonumber but you have defined a clustered index to store the data in the most searched by order which is LastName, FirstName. That means when you add a new record, it doesn't get added at the end of the used space, it has to get added where it belongs in index sequence so blocks need to be split and pointers created, etc.

 

RyanJames

New member
Local time
Today, 08:34
Joined
May 12, 2022
Messages
5
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,981
Are you using a form to do the updates or are you opening a query? Maybe the form or query is corrupted. Both happen. Try using the decompile to see if that clears up the problem. I usually decompile. Open the db and compact it. Close it and decompile again.

If that doesn't work, try rebuilding the query but first, go to the options and make sure that Name Auto Correct is turned Off. I open the existing querydef in SQL view and copy the string to notepad. That gets rid of any stray characters. Close the original query and rename the query. Then start a new querydef and paste in the string from notepad. Save and close.

There are several ways to rebuild a form. Control by control or export to text and then import from text.

The decompile is supposed to get rid of the hidden p-code and any junk left hanging around. But that doesn't always work so exporting objects to text is another way to clean the debris out of text.

Here's some sample code. When just doing one form at a time I hard code everything like this. If you want to do the whole database, you need a more sophisticated method. We'll get to that if you need it.
Code:
Public Function ExportForm()
Dim strPath As String
Dim strName As String
    strName = "frmQuoteAcc"
    strPath = "C:\Data\Work\AccessWorldForums\TMECO\Form_"
    Application.SaveAsText acForm, strName, strPath & strName & ".txt"
End Function
Public Sub ImportForm()
Dim strPath As String
Dim strName As String
    strName = "frm835"
    strPath = "C:\Pat\AOAClients\TextObjects\Form_"
    Application.Application.LoadFromText acForm, strName, strPath & strName & ".txt"
End Sub
 

RyanJames

New member
Local time
Today, 08:34
Joined
May 12, 2022
Messages
5
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
42,981
Thanks for reporting back with the actual problem description. That is hugely helpful to people searching for similar problems and what makes this forum great:)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
21,358
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!
 

Users who are viewing this thread

Top Bottom