Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Access 03 Error 3027 - Cannot Update. Database or object is ready only

    i have used access query builder to create the following query:

    SELECT tblResultHeader.ref, tblResultHeader.costType, tblResultHeader.strataCode, tblResultHeader.areaCode_2, tblResultHeader.areaCode_1, tblResultHeader.blockCover, tblResultDetail.Element, tblResultDetail.Subelement, tblResultDetail.repYear, tblResultDetail.repSpot, tblResultDetail.repCost, tblResultDetail.renewalYear, tblResultDetail.renewalUnits, tblResultDetail.renewalSpot, tblResultDetail.renewalCost, tblScheduleOfRates.life, tblScheduleOfRates.house, tblScheduleOfRates.bungalow, tblScheduleOfRates.flat, tblScheduleOfRates.unit FROM (tblResultHeader INNER JOIN tblResultDetail ON tblResultHeader.ref = tblResultDetail.Ref) INNER JOIN tblScheduleOfRates ON (tblResultDetail.Subelement = tblScheduleOfRates.subelement) AND (tblResultDetail.Element = tblScheduleOfRates.element) WHERE ((Not (tblScheduleOfRates.unit)='IO'))

    I have then put this into vba code as follows:

    'get the data needed for costing the detail table up.
    strSQl = "SELECT tblResultHeader.ref, tblResultHeader.costType, tblResultHeader.strataCode, tblResultHeader.areaCode_2, tblResultHeader.areaCode_1, tblResultHeader.blockCover, tblResultDetail.Element, tblResultDetail.Subelement, tblResultDetail.repYear, tblResultDetail.repSpot, tblResultDetail.repCost, tblResultDetail.renewalYear, tblResultDetail.renewalUnits, tblResultDetail.renewalSpot, tblResultDetail.renewalCost, tblScheduleOfRates.life, tblScheduleOfRates.house, tblScheduleOfRates.bungalow, tblScheduleOfRates.flat, tblScheduleOfRates.unit " & _
    "FROM (tblResultHeader INNER JOIN tblResultDetail ON tblResultHeader.ref = tblResultDetail.Ref) INNER JOIN tblScheduleOfRates ON (tblResultDetail.Subelement = tblScheduleOfRates.subelement) AND (tblResultDetail.Element = tblScheduleOfRates.element) " & _
    "WHERE ((Not (tblScheduleOfRates.unit)='IO'))"

    Set rsCostDetail = objdb.OpenRecordset(strSQl)

    rsCostDetail.MoveLast
    intDetailRecordCount = rsCostDetail.RecordCount
    rsCostDetail.MoveFirst

    Do Until rsCostDetail.EOF
    intCurrentRecordCount = intCurrentRecordCount + 1

    'first lests deal with the catchup costs
    If rsCostDetail!repyear < 999 Then
    rsCostDetail.Edit
    rsCostDetail!repcost = rsCostDetail!repspot
    rsCostDetail.Update
    End If
    Loop

    However when i run the code i get an erroro code 3027 as described in the thread title.

    Can any1 help please.

    Thanks in advance,

    Jaspal

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    do you have any errorhandling built in to determine what line is throwing the error?

  3. #3
    Join Date
    May 2008
    Posts
    4
    fails on line rsCostDetail.Edit

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    i'd guess it's something to do with your query. you may be pulling together fields that don't allow you to edit one of them due to relationships. Try putting your SQL into query builder, and see if it'll let you change fields in there. If I were a betting man, I'd bet that it won't...

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    sorry...that's what i get for forgetting...so you said you'd already put it into query builder. run down to the bottom and see if there's a row for new additions, or try editing a field in there. i'll make sure to read better next time.

  6. #6
    Join Date
    May 2008
    Posts
    4
    i have put the sqlin to the query builder and there is no new line for additing a new record.

    i dont understand why as there are no relationships in this database at the moment.

  7. #7
    Join Date
    May 2008
    Posts
    4
    however if i change the recordset type in the query builder from dynaset to dynaset(inconsistent updates) then i can make additions. can i make this change in the code??????

  8. #8
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    try using a directional join vs an inner join. double click your relationship in the query builder and choose "select all from" whatever the main table is "and matching records from" whatever the other table is. you can change the type of recordset from your Currentdb.Openrecordset(strSQL,dbOpenDynaset)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •