Solved

access error 3027 object or db is read only

Posted on 2014-09-24
13
173 Views
Last Modified: 2014-09-25
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
Comment
Question by:PeterBaileyUk
  • 8
  • 3
  • 2
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40341051
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
 

Author Comment

by:PeterBaileyUk
ID: 40341110
that didnt work either.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40341277
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:PeterBaileyUk
ID: 40341438
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
 

Author Comment

by:PeterBaileyUk
ID: 40341442
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40341694
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
 

Author Comment

by:PeterBaileyUk
ID: 40341729
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
 

Author Comment

by:PeterBaileyUk
ID: 40341776
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
 

Author Comment

by:PeterBaileyUk
ID: 40341796
so the query is working but now for some reason it squeals at the .edit method
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40341918
Peter,

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

Sorry,
Paul
0
 

Author Comment

by:PeterBaileyUk
ID: 40342041
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
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40342311
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 40344555
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

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Any Way to Print an Import Spec? 3 32
Access query expression 6 22
How to use DLookup with IsNull Function 4 27
Access Changing Number to Date with Seperator 5 21
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

831 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