runtime error 3027 - read only access problems (1 Viewer)

[etk]

I have this segment of code which creates an outlook appointment for a session. I implemented a 'Status_ID' field which I want to update in the recordset after sending the appointment. The Status_ID field indicates whether things like cancellations, reschedulings, but also whether or not the meeting has been added to outlook appointments.

Every time I run the code I get runtime error 3027 for read only access on the line with 'rsSession.Edit' Is this because I did a series of joins in the recordset? I have recreated the database in a new file and had the same error. Any suggestions on how to make this segment of my code work?

Private Sub SchedOutlook()
Dim rsSession As DAO.Recordset
Set rsSession = CurrentDb.OpenRecordset("SELECT tblSession.*, Employee.EMAIL_ADDRESS AS Employee_Email, & _
Mentor.EMAIL_ADDRESS AS Mentor_Email FROM (tblSession INNER JOIN tblEmployee AS Employee ON tblSession.EMP_ID = Employee.EMP_ID) & _
INNER JOIN tblEmployee AS Mentor ON tblSession.Mentor_ID = Mentor.EMP_ID", dbOpenDynaset)
    With rsSession
        rsSession.MoveFirst
        Do Until rsSession.EOF
            Dim outobj As Outlook.Application
            Dim outappt As Outlook.AppointmentItem
            Set outobj = CreateObject("outlook.application")
            Set outappt = outobj.CreateItem(olAppointmentItem)
            With outappt
              .MeetingStatus = olMeeting
               .Start = rsSession!Session_DT & " " & rsSession!Start_Time
               .Duration = rsSession!Session_Duration
               .RequiredAttendees = rsSession!Employee_Email & "; " & rsSession!Mentor_Email
               .Subject = "Test Appointment"
               .Body = "This is a test"
               .Location = "Test"
               .ReminderMinutesBeforeStart = 15
               .ReminderSet = True
               .Send
               .Save
            End With
            If rsSession!Status_ID <> 4 Then
               rsSession.Edit
               rsSession!Status_ID = 4
               rsSession.Update
            End If
               rsSession.MoveNext
        Loop
    End With
    
    rsSession.Close
    Set rsSession = Nothing
            
End Sub

 

[ions]

1) Try the following code at the start

Dim dbs as DAO.Database
Dim rsSession As DAO.Recordset

Set dbs = CurrentDb()
Set rsSession = dbs.OpenRecordset("SELECT .......

I have been told by MS Access MVP's not to open recordsets without creating a separate dbs variable.

Perhaps that will fix it.

2) It sounds like a record locking error. Are you editing / dirtying forms that have one of the tables in your SQL as recordsource?

 

[etk]

Thanks for the tip, but no, that did not solve the problem.

I don't have forms that would lock the records (yet), nor were any of my tables open or being utilised in any other way during runtime. I wonder if the join in the recordset creates the lock? I am stumped.

 

[missinglinq]

Allen Browne has an excellent article that explains why Multi-Table Queries are frequently Read-Only:

Why is my query read-only?

Linq ;0)>

 

[etk]

Allen Browne has an excellent article that explains why Multi-Table Queries are frequently Read-Only:

Why is my query read-only?

Linq ;0)>

Great resource! thanks!