DB update error - DB or object is read only.

[SilverDevil72]

First, I know there is considerable material on this subject but i'm still unable to get past it. Had i known this is so difficult to fix i'd tested before building the app.


Please, find the code snippet and list of what I have already done.

Please help!!!

QRS = "SELECT ProdFile.[Abstract Bk # & Pg] as PBkPg, BOBTest.[Abstract Bk # & Pg] as BBkPg, ProdFile.[Volume & page #] as PVolPg, BOBTest.[Volume & page #] as BVolPg FROM ProdFile, BOBTest WHERE (((ProdFile.[Abstract Bk # & Pg]) = BOBTest.[Abstract Bk # & Pg]) And ((ProdFile.[Volume & page #]) = BOBTest.[Volume & page #]));"


UpdRS = "UPDATE ProdFile, BOBTest SET BOBTest.[Volume & page #] = BUpdVolPg, WHERE (((ProdFile.[Abstract Bk # & Pg])=BOBTest.[Abstract Bk # & Pg]) And ((ProdFile.[Volume & page #])=BOBTest.[Volume & page #]));"


Set RS = db.OpenRecordset(QRS, dbOpenDynaset, dbPessimistic)   


Do While Not RS.EOF
                 DO STUFF....   
                    RS.Edit         'Line causing the DB or Object is read only
                    DoCmd.RunSQL UpdRS
                    
                    'RS.Execute (UpdRS)
                    
                    BUpdVolPg = ""
                    RecCnt = RecCnt + 1
                    
                    RS.MoveNext      ' Read next record
Loop

Background: App was originally in OneDrive, moved it to Documents\folder. Two tables were created by importing excel files. Both have primary keys and one indexed field, not the field being updated.
What i have done.

• Verified folder, and MS Access file, is NOT read only.
• Verified folder permissions are set to full and added everyone with full.
• Verified Folders and Files are NOT being shared. properties.sharing.advanced
• Form has allow Additions property = yes but record locks = No Locks
• Created a new small app, in different folder, same result.
• Did compact and repair on the app file.
• Turned off folder read only attribute but it keeps reverting back to read only. Verified Windows Defender "Controlled Folder Access" is OFF! Searched MS support and they state they ignore that attribute yet is seemed a little unclear if it applies to Access data updates. If they don't ignore it, i need a solution for this as well.
• Rebooted PC
• Verified VBA References are good.
• Just a straight up Access app. No server, external connections,

I must/hope have missed something. How do i fix this issue?

Please help !!

[isladogs]

1. Does the form have Allow Edits = Yes
2. Why are you using a recordset? Why dbPessimistic?
3. Instead just execute this an an update query and scrap the recordset

CurrentDb.Execute "UPDATE ProdFile, BOBTest SET BOBTest.[Volume & page #] = BUpdVolPg
 WHERE (((ProdFile.[Abstract Bk # & Pg])=BOBTest.[Abstract Bk # & Pg]) And ((ProdFile.[Volume & page #])=BOBTest.[Volume & page #]));", dbFailOnError

NOTE: I have removed the comma before WHERE - it shouldn't be there

4. Recommend you fix your field names - no spaces or special characters such as #

[CJ_London]

Not clear to me what you are trying to do but I’m guessing you are trying to update a record which is already being edited so the record is locked

[SilverDevil72]

Firstly, many thanks for kindly responding and so promptly!!!
Background: App is matching two tables using two fields. When matched, do some processing then update the record.
1. Allow Edits = Yes, Yes
2. For two reasons, one is i am most familiar with the recordset although your suggestion is much cleaner. Second when i searched for best practice to process and loop through DB records and update, recommended select query, process records, update query to update using dbcurrent.openrecordset. Used dbdynaset because that plus the dbPessimistic makes the recordset updateable.
3. Like it. Use for both select and update queries will make the db updateable? Will this fix the db is read only error? Answer after testing... NO, still have the posted issue.
4. Agree, need to do that.
Update. currentdb.execute does not appear to work on sql select queries.

[SilverDevil72]

CJ_London, Many thanks for responding and so promptly. So very much appreciated!!!

See my response to isladogs for i think that will help. As for the record being locked, i had thought that might be the case then didn't act on it. Thank you so much for that observation and kick in the pants.

[SilverDevil72]

After several days of frustration i deleted the .edit command and the error went away!!!! Incredible!!
Many thanks for the responses.