Solved

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

Posted on 2009-07-13
7
1,935 Views
Last Modified: 2013-12-20
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

0
Comment
Question by:NashVegas
  • 4
  • 3
7 Comments
 
LVL 8

Accepted Solution

by:
fabriciofonseca earned 500 total points
ID: 24842225
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
 

Author Comment

by:NashVegas
ID: 24842405
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
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 24842864
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:NashVegas
ID: 24842868
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
 

Author Closing Comment

by:NashVegas
ID: 31602929
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
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 24842952
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
 

Author Comment

by:NashVegas
ID: 24842986
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now