Solved

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

Posted on 2009-07-13
7
1,943 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

896 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

14 Experts available now in Live!

Get 1:1 Help Now