Consulting

Results 1 to 3 of 3

Thread: Solved: Error: 3027 Database or Object is read-only

  1. #1
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    649
    Location

    Solved: Error: 3027 Database or Object is read-only

    I am doing a simple add procedure from a form. The item is always added, but in addition to adding the record I want to update a field related to the Building it is assigned to.

    [VBA]Private Sub cmdNewReservation_Click()
    On Error GoTo Err_cmdNewReservation_Click
    Dim strSQL As String
    Dim db As Database
    Dim qDef As QueryDef
    Dim rst As Recordset
    Dim fieldYear As String
    Dim fieldName As String
    Dim initRes As Integer
    strSQL = "SELECT Building.BuildingID, Building.UnitsReserved06, Building.UnitsReserved07, " & _
    "Building.UnitsReserved08, Building.UnitsReserved09, Building.UnitsReserved10, " & _
    "[Buildings Reserved].AssignToBuilding " & _
    "FROM Building, [Buildings Reserved] " & _
    "WHERE (((Building.BuildingID)=[Buildings Reserved]![AssignToBuilding]));"

    If Not bAdd Then
    DoCmd.GoToRecord , , acNewRec
    Me.cmdNewReservation.Caption = "Insert Reservation"
    Me.Unit_Type.Locked = False
    Me.Unit_Name.Locked = False
    Me.Bldg_Name.Locked = False
    Me.Year_Reserved.Locked = False
    Me.Assign_to_Bldg.Locked = False
    bAdd = True
    Else
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    fieldYear = Right(Me.Year_Reserved, 2)
    Set db = CurrentDb
    Set qDef = db.CreateQueryDef("qryUpdateProjUnit", strSQL)
    Set rst = qDef.OpenRecordset
    db.QueryDefs.Delete ("qryUpdateProjUnit")
    Me.lstReserve.Requery
    fieldName = "UnitsReserved" & fieldYear
    If rst.RecordCount = 1 Then
    MsgBox "This will increment Projected Units for the year 20" & fieldYear & "."
    MsgBox fieldName & " in table Building will be incremented."
    initRes = rst.Fields(fieldName).Value
    rst.Edit
    rst.Fields(fieldName).Value = rst.Fields(fieldName).Value + 1
    rst.Update
    rst.Close
    End If
    Me.cmdNewReservation.Caption = "Add Reservation"
    Me.Unit_Type.Locked = True
    Me.Unit_Name.Locked = True
    Me.Bldg_Name.Locked = True
    Me.Year_Reserved.Locked = True
    Me.Assign_to_Bldg.Locked = True
    bAdd = False
    End If
    Exit_cmdNewReservation_Click:
    Exit Sub
    Err_cmdNewReservation_Click:
    If Err.Number <> 2501 And Err.Number <> 57097 Then
    MsgBox Err.Number & ": " & Err.Description
    End If
    Resume Exit_cmdNewReservation_Click

    End Sub[/VBA]

    It usually enters the Error trap in line with: rst.Edit. Any ideas why it thinks this is a read-only object? I have done similar procedures in other databases, but this one seems to be causing trouble.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,007
    Location
    Check that the openrecordset that you have created just before it is not a snapshot recordset and is in fact editable.
    Have you left it open and tried to change any values manually?
    I find it best to create a normal query and get it working and then copy it's SQL, because my SQL skills are not very good.

  3. #3
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    649
    Location
    [VBA]rst.Edit
    rst.Fields(fieldName).Value = rst.Fields(fieldName).Value + 1
    rst.Update
    rst.Close
    [/VBA]
    was replaced by
    [VBA]DoCmd.RunSQL ("Update " & fieldname & " " & rst.Fields(fieldName).Value + 1)[/VBA]

    That seemed to do it.

Posting Permissions

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