Solved

access error 3027 object or db is read only

Posted on 2014-09-24
13
169 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
 

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 34

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 34

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now