• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

access error 3027 object or db is read only

I had a piece of code which was working, i made a small correction in the sql of strsql2 to pick nulls on the join and now it gives error 3027.

I am not sure why

The first rst give the manufacturer to process, strsql2 gives the models where the tblprocess lookfor value appears in the string.

the next strsql3 looks un what the range should be called to be put into column groups.
it complains at the .edit

Public Sub vehicleranges()

Dim Db As DAO.Database
Set Db = CurrentDb()
Dim rstRangeMarque As DAO.Recordset
Dim rstRangeWords As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim RstToLookFor As DAO.Recordset


strSQL = "SELECT TblProcess.Marque" _
& " FROM TblProcess" _
& " GROUP BY TblProcess.Marque" _
& " HAVING (((TblProcess.Marque) Is Not Null));"

Debug.Print "sql " & strSQL
Set rstRangeMarque = Db.OpenRecordset(strSQL)


With rstRangeMarque

    .MoveFirst
    countrecords = .RecordCount

'cycle through the recordset of marque to do
    Do Until rstRangeMarque.EOF

                strSQL2 = "SELECT ClientWordGroups.concatenated, ClientWordGroups.MarqueAlias, ClientWordGroups.MarqueAlias, ClientWordGroups.groups, TblRangeExceptions.Exception, TblRangeExceptions.Range" _
                & " FROM ClientWordGroups LEFT JOIN TblRangeExceptions ON ClientWordGroups.concatenated = TblRangeExceptions.Exception" _
                & " WHERE (((ClientWordGroups.MarqueAlias) Like '*" & rstRangeMarque.Fields("Marque").Value & "*'" & " AND ((TblRangeExceptions.Exception) Is Null" & ")));"
                  
                
                Debug.Print "sql2 " & strSQL2
                Set rstRangeWords = Db.OpenRecordset(strSQL2)
                
                With rstRangeWords
                
                    .MoveFirst
                     Do Until rstRangeWords.EOF
                
                
                
                        strSQL3 = "SELECT TblProcess.Marque, TblProcess.Process, TblProcess.LookFor" _
                        & " FROM TblProcess" _
                        & " WHERE (((TblProcess.Marque) Like '*" & rstRangeMarque.Fields("Marque").Value & "*'));"
                
                        Debug.Print "sql3 " & strSQL3
                        Set RstToLookFor = Db.OpenRecordset(strSQL3)
                        With RstToLookFor
                                    RstToLookFor.MoveFirst
                                    Do Until RstToLookFor.EOF
                        
                                        If InStr(rstRangeWords.Fields("concatenated").Value, RstToLookFor.Fields("Lookfor").Value) <> 0 Then
                                            rstRangeWords.Edit
                                            rstRangeWords.Fields("groups").Value = RstToLookFor.Fields("Process").Value
                                            rstRangeWords.Update
                                        Else
                        
                        
                                        End If
                        
                        
                                    RstToLookFor.MoveNext
                                    Loop
                        End With


            rstRangeWords.MoveNext
    
            Loop
            End With

rstRangeMarque.MoveNext
Loop


End With




End Sub

Open in new window


am in access vba
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 8
  • 3
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
try removing most parentheses from the where clause

                strSQL2 = "SELECT ClientWordGroups.concatenated, ClientWordGroups.MarqueAlias, ClientWordGroups.MarqueAlias, ClientWordGroups.groups, TblRangeExceptions.Exception, TblRangeExceptions.Range" _
                & " FROM ClientWordGroups LEFT JOIN TblRangeExceptions ON ClientWordGroups.concatenated = TblRangeExceptions.Exception" _
                & " WHERE ClientWordGroups.MarqueAlias Like '*" & rstRangeMarque.Fields("Marque").Value & "*'" & " AND TblRangeExceptions.Exception Is Null;"

Open in new window

0
 
PeterBaileyUkAuthor Commented:
that didnt work either.
0
 
PortletPaulfreelancerCommented:
when reporting back "didn't work" does not help us respond

the same error message? a different error message? (if so what was that message) or, no results?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PeterBaileyUkAuthor Commented:
i am not being funny but a comment like"when reporting back "didn't work" does not help us respond" is very unhelpful and certainly not inducive of me renewing next year. I do pay for this service and always courteous but what you said is appalling if you feel that way then do not reply., unless you hear otherwise the error message is still the same.
0
 
PeterBaileyUkAuthor Commented:
and to a moderator if your monitoring with an increase of 30% next year, this is even a more important point.I have great regard for the experts but I am not fodder for petty comments.
0
 
PatHartmanCommented:
Peter,
Please understand that even though you pay for this service, we - the people who actually provide the service - do not get paid to provide it so this is not a quid pro quo.  You pay with money, we pay with time.  So we both pay to be here. The site owner reaps the benefit from your money and our effort.  It's an excellent business model.

Since you only changed the SQL, can you please post the original SQL and the modified version.  Did something you changed make the query not updatable?  Also, point out the line on which the error occurs.
0
 
PeterBaileyUkAuthor Commented:
I get what your saying to but maybe this is just me sometimes an expert will make a mistake but i do not go out of my way to bring that to their attention. In this case I felt I had to say something. 1 second I will go and get the previous version sql.
0
 
PeterBaileyUkAuthor Commented:
it was previously producing this:

SELECT ClientWordGroups.concatenated, ClientWordGroups.MarqueAlias, ClientWordGroups.MarqueAlias, ClientWordGroups.groups, TblRangeExceptions.Exception, TblRangeExceptions.Range FROM ClientWordGroups LEFT JOIN TblRangeExceptions ON ClientWordGroups.concatenated = TblRangeExceptions.Exception WHERE (((ClientWordGroups.MarqueAlias) Like '*BMW\MINI\ALPINA*'));

this failed because:

 "740 LI M SPORT (315)"       315 is a  bhp in this case but its also a valid 3 series bmw

so i made an exception table for that client model and then produced this:

SELECT ClientWordGroups.concatenated, ClientWordGroups.MarqueAlias, ClientWordGroups.MarqueAlias, ClientWordGroups.groups, TblRangeExceptions.Exception, TblRangeExceptions.Range FROM ClientWordGroups LEFT JOIN TblRangeExceptions ON ClientWordGroups.concatenated = TblRangeExceptions.Exception WHERE ClientWordGroups.MarqueAlias Like '*BMW\MINI\ALPINA*' AND TblRangeExceptions.Exception Is Null;

Now the query does not pick up these bmw 740, which is correct, but i get the error 3027

Autoid      Range      Exception      Client
1      3Series      740 I M SPORT (315)      abi
2      3Series      740 I M SPORT (326)      abi
3      3Series      740 I SE (315)      abi
4      3Series      740 LI M SPORT (315)      abi
5      3Series      740 I M SPORT (326)      abi
6      3Series      740 LI SE (315)      abi
7      3Series      740 LI M SPORT (326)      abi
0
 
PeterBaileyUkAuthor Commented:
so the query is working but now for some reason it squeals at the .edit method
0
 
PortletPaulfreelancerCommented:
Peter,

My apologies, It certainly wasn't my intention to offend you. I can be clumsy at this at times.

Sorry,
Paul
0
 
PeterBaileyUkAuthor Commented:
Its ok apology accepted, life is too short for such things like this and I do appreciate that your all busy. Thank you for replying.
0
 
PatHartmanCommented:
Is ClientWordGroups  a query?  Is it updateable?  Is TblRangeExceptions a table?  Is it updateable?

For a query to be updateable, all parts must be updateable.
0
 
PeterBaileyUkAuthor Commented:
yes i made these checks and it was that adding the join with the null turned it into a read only query. so i have saved to a table instead.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now