Solved

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

Posted on 2009-07-13
7
1,961 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

751 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