Run-time error '3027': Cannot update. Database or object is read-only

I have read through the many posts with this title or similar, but cannot find one relevant to my scenario. I am creating a recordset from SQL select statement and then I want to loop through the records and delete certain ones and finally export the results to Excel. I have written a separate function to handle the export (exportToExcel). But before I get to that point, I get the run-time error with the "rs.Delete" line highlighted.
Private Sub cmdRun_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dStart As Date
Dim sql As String
Dim dtTest1 As String
Dim dtTest2 As String
 
Set db = CurrentDb
dStart = Me!dtFrom
 
sql = "SELECT qReviewers.[rl_abbv] AS Role, [Protrev-copy].[prot_no] AS Protocol, [Contacts-copy].full_nm AS Reviewer, " _
& "[Syswftrn-copy].[d_start] AS RecDat, [Syswftrn-copy].[d_end] AS ComplDat FROM [Protrev-copy], (((([Syswftrn-copy] " _
& "INNER JOIN [Syswusr-copy] ON [Syswftrn-copy].pk_wusr = [Syswusr-copy].pk_wusr) INNER JOIN " _
& "[Contacts-copy] ON [Syswusr-copy].pers_id = [Contacts-copy].pers_id) INNER JOIN [Contacts-copy] " _
& "AS [Contacts-copy_1] ON [Syswftrn-copy].ownr_id = [Contacts-copy_1].pers_id) INNER JOIN " _
& "[Contacts-copy] AS [Contacts-copy_2] ON [Syswftrn-copy].reqs_id = [Contacts-copy_2].pers_id) " _
& "INNER JOIN qReviewers ON [Contacts-copy].pers_id = qReviewers.pers_id " _
& "WHERE ((([Protrev-copy].prot_no)=Left([trans_id],8)) AND (([Contacts-copy].full_nm)<>'Burnett, Louann' " _
& "And ([Contacts-copy].full_nm)<>'Knights, Marisa') AND (([Syswftrn-copy].d_start)>#" & dStart & "#) AND " _
& "(([Syswftrn-copy].src_trans) Not Like '*Amendment*') AND (([Contacts-copy_1].full_nm)<>[Contacts-copy].[Full_Nm]) " _
& "AND ((CStr([pk_protrev]))=Mid(Trim([trans_id]),14,5)) AND (([Syswftrn-copy].stat_wfusr)='Reviewer')) " _
& "ORDER BY [Protrev-copy].prot_no, [Contacts-copy].full_nm;"
 
If DCount("Name", "MsysObjects", "Name='ReviewStats'") = 1 Then
    DoCmd.DeleteObject acQuery, "ReviewStats"
End If
    
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
 
rs.MoveFirst
 
Do While Not rs.EOF
    dtTest1 = rs!Reviewer & rs!RecDat
    rs.MoveNext
    dtTest2 = rs!Reviewer & rs!RecDat
    If dtTest1 = dtTest2 Then
        rs.MovePrevious
        rs.Delete
        rs.Update
    End If
Loop
 
exportToExcel (rs)
    
End Sub

Open in new window

NashVegasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fabriciofonsecaCommented:
The problem is into the SQL.

You are joing tables that may have the relationship 1-n. Those kind of query are not updated.

Try to copy the SQL and create a query with this, you will realize you cannot edit or delete any record.

The way I use to fix this problem (not sure if it is the best)

1 - Create a new temporary table with the values I want to delete.
2 - Run a delete query with the table you want to delete, using were records in "temporary table you created"


Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NashVegasAuthor Commented:
Thanks, I will try this approach. I know I would need to add something like:
          Dim td As DAO.TableDef
          Set td = db.CreateTableDef("TempReviewStats")
But I have never worked with tabledefs in code before. Do I need to set all of the fields?:
          Dim fld1 As DAO.field
          Dim fld2 As DAO.field
          Set fld1 = td.CreateField("Role", dbText)
          Set fld2 = td.CreateField("Reviewer", dbText)
          etc.
And then how would I populate the table with the results of my query?
0
fabriciofonsecaCommented:
1 - You can use your own SQL to create the temp tab. Use the create table query..
    Try something like

SELECT (SELECT qReviewers.[rl_abbv] AS Role, [Protrev-copy].[prot_no] AS Protocol, [Contacts-copy].full_nm AS Reviewer, " _
& "[Syswftrn-copy].[d_start] AS RecDat, [Syswftrn-copy].[d_end] AS ComplDat FROM [Protrev-copy], (((([Syswftrn-copy] " _
& "INNER JOIN [Syswusr-copy] ON [Syswftrn-copy].pk_wusr = [Syswusr-copy].pk_wusr) INNER JOIN " _
& "[Contacts-copy] ON [Syswusr-copy].pers_id = [Contacts-copy].pers_id) INNER JOIN [Contacts-copy] " _
& "AS [Contacts-copy_1] ON [Syswftrn-copy].ownr_id = [Contacts-copy_1].pers_id) INNER JOIN " _
& "[Contacts-copy] AS [Contacts-copy_2] ON [Syswftrn-copy].reqs_id = [Contacts-copy_2].pers_id) " _
& "INNER JOIN qReviewers ON [Contacts-copy].pers_id = qReviewers.pers_id " _
& "WHERE ((([Protrev-copy].prot_no)=Left([trans_id],8)) AND (([Contacts-copy].full_nm)<>'Burnett, Louann' " _
& "And ([Contacts-copy].full_nm)<>'Knights, Marisa') AND (([Syswftrn-copy].d_start)>#" & dStart & "#) AND " _
& "(([Syswftrn-copy].src_trans) Not Like '*Amendment*') AND (([Contacts-copy_1].full_nm)<>[Contacts-copy].[Full_Nm]) " _
& "AND ((CStr([pk_protrev]))=Mid(Trim([trans_id]),14,5)) AND (([Syswftrn-copy].stat_wfusr)='Reviewer')) " _
& "ORDER BY [Protrev-copy].prot_no, [Contacts-copy].full_nm) MySQL
INTO Table2
FROM MySQL
WHERE MySQL. full_nm='lala';

Make sure the you do not have any full_nm='lala', so you will create an empty table. You need to do it only once, because later you can just delete everything into the table before you add records.

2 - in order to add records you can use

    If dtTest1 = dtTest2 Then
        rs.AddNew
        rs!Reviewer = "somevalue"
        rs!RecDat = "somevalue"
        rs.Update
    End If




0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

NashVegasAuthor Commented:
Okay, I think I have figured out the process of creating the temp table. But now there is a problem with my loop. My intent is to go to the end of the recordset, store a concatenation of 2 field values in a variable, move to the previous record and store the same two field values in another variable and compare the two. If they are the same, then delete the current record. Then repeat the process until you get to the beginning of the recordset. But I get an error "Run-time error '3167': Record is deleted". I assume this is because it is trying to store the field values of a deleted record. How do I wok around this?

Set qd = db.CreateQueryDef("ReviewStats", sql)
Set td = db.CreateTableDef("TempReviewStats")
td.Fields.Append td.CreateField("Role", dbText, 10)
td.Fields.Append td.CreateField("Protocol", dbText, 10)
td.Fields.Append td.CreateField("Reviewer", dbText, 50)
td.Fields.Append td.CreateField("RecDat", dbDate, 20)
td.Fields.Append td.CreateField("ComplDat", dbDate, 20)
 
db.TableDefs.Append td
 
db.Execute ("INSERT INTO TempReviewStats SELECT * FROM ReviewStats;")
 
Set rs = td.OpenRecordset
 
rs.MoveLast
 
Do Until rs.BOF
    dtTest1 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    rs.MovePrevious
    dtTest2 = Trim(rs!Reviewer) & Trim(rs!RecDat)
    If dtTest1 = dtTest2 Then
        rs.Delete
    End If
Loop

Open in new window

0
NashVegasAuthor Commented:
fabriciofonseca: I will start a new request for my current issue since it is really unrelated to my original question. Thanks for your help.
0
fabriciofonsecaCommented:
do not delete the record.

Change the field rs!Reviewer to "for delete", for example

and after the loop run a delete query, something like

delete from mytable where Reviewer='for delete'




0
NashVegasAuthor Commented:
fabriciofonseca: Thank you for your additional comment. I think I will still post this question and see what other suggestions are offered. It seems inefficient to update a field in a record and then run a delete query to remove it. I would prefer to find a way to delete the records while I am executing the loop. But maybe there is no other way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.