Solved

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

Posted on 2009-07-13
7
1,950 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 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