Moving a field into a Temp field receiving a [3027] Cannot Update. DB is read-only.

[dowlingm815]

In the following code, an error is generated when trying to move a selected record into a temp field to evaluate the record. the error is "[3027] Cannot Update. DB is read-only."

I am looking to get the selected record evaluate it. if a criteria is met, it will written to a table.

Option Compare Database

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As String

Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
  

strSQL = "SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date"
                 


Set rst = CurrentDb.OpenRecordset(strSQL)


strFirstRec = "Yes"

Do Until rst.EOF
    Debug.Print strFirstRec; " "; rst!PR_ID & " "; rst!PR_Date&; " "; rst!Workflow_Step_Name&; " "; rst!workflow_Step_Date; " "; rst!CountOfWorkflow_Step_Date
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rst!PR_ID = strPR_ID_TEMP
        
    End If

    If rst!PR_ID = strPR_ID_TEMP Then
        If rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP Then
            If rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP Then
                rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            End If
        Else
'            write record
            rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
            rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        End If
    Else
'        write record
        rst!workflow_Step_Date = strWorkflow_Step_Date_TEMP
        rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP
        rst!PR_ID = strPR_ID_TEMP
    End If

    rst.MoveNext
Loop
Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub

[NeoPa]

This is unlikely to be related to your code at all.

You posted the error message (helpfully). Did it not occur to you that your problem was with the access to the database file itself. I suggest you look at that issue and see what you can find. We would need more information about this before we can shed light in the right direction.

[patjones]

Hi dowling -

Your query has a "GROUP BY", which makes the resulting recordset read-only.

As a side note, usually when you make a change to a recordset, you need to use the "Edit" and "Update" recordset methods. For instance:

rst.Edit

rst!Workflow_Step_Date = strWorkflow_Step_Date_TEMP
rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP

rst.Update

Pat

[dowlingm815]

Pat,

The code is reading the data and then setting a temporary field equal to read-only fields. It is not editing the fields, therefore, I am not following your direction.

Mary

[dowlingm815]

NeoPa,

I am not sure what i am looking for within the db. the structure is as follows:

"[ID] COUNTER CONSTRAINT ndxID PRIMARY KEY,  " & _
           "[PR_ID] TEXT(25)," & _
            "[External_PR] TEXT(25), " & _
            "[PR_Name] TEXT(30), " & _
            "[PR_Date]DATETIME, " & _
            "[Buyer_username]TEXT(30), " & _
            "[Buyer_First_Name]TEXT(30), " & _
            "[Buyer_Last_Name]TEXT(30), " & _
            "[Buyer_Email]TEXT(30), " & _
            "[Department]TEXT(30), " & _
             "[Workflow_Step_Name]TEXT(30), " & _
             "[Workflow_Step_Date]DATETIME, " & _
             "[Workflow_Step_Action]TEXT(30), " & _
             "[Approver_Username]TEXT(30), " & _
             "[Approver_First_Name]TEXT(30), " & _
             "[Approver_Last_Name]TEXT(30), " & _
             "[Approver: Email]TEXT(30), " & _
             "[Approver: Department]TEXT(30)" & _

[patjones]

dowling -

Are not rst!Workflow_St ep_Date and rst!Workflow_St ep_Name fields from the query in strSQL, which has a GROUP BY clause? Queries that involve GROUP BY clauses are read-only, which means that you cannot assign values to rst!Workflow_St ep_Date and rst!Workflow_St ep_Name as you are trying to do here:

rst!Workflow_Step_Date = strWorkflow_Step_Date_TEMP 
rst!Workflow_Step_Name = strWorkflow_Step_Name_TEMP

To test what I'm saying, I made a simple piece code with a GROUP BY query and tried to assign something to one of the fields in the query as you are trying to do, and also received error 3207.

Perhaps what you mean to do is

strWorkflow_Step_Date_TEMP = rst!Workflow_Step_Date
strWorkflow_Step_Name_TEMP = rst!Workflow_Step_Name

If I'm off track here, perhaps NeoPa can shed light on the issue...

Pat

[dowlingm815]

If read-only is the case and then the value of each record cannot be copied into another record, true?

In SQL, a GROUP BY can created a table. Would this be the solution, create a SQL statement creating a table instead so it is readable?

SELECT T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date, Count(T_PRApprovalHistory.PR_Date) AS [CountOfPR Date] INTO T_Group_By_Test
FROM T_PRApprovalHistory
GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date
HAVING (((T_PRApprovalHistory.PR_ID)=11050254))
ORDER BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.Workflow_Step_Date;

[patjones]

dowling -

Yes, you can create a temporary table and put the results of your GROUP BY query into it. Then you could do anything you want with the records in the table.

For example:

Dim rstTemp As Recordset
Dim rst As Recordset
Dim i As Long

'Create temporary table
CurrentDb.Execute("CREATE TABLE tblTemp(PR_Id SMALLINT, Workflow_Step_Date DATETIME, Workflow_Step_Name VARCHAR(50))")

'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")

'Grab the data and put it in rst
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID,  " & _
                "T_PRApprovalHistory.PR_Date,  " & _
                "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                "T_PRApprovalHistory.Workflow_Step_Date,  " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory  " & _
        "GROUP BY T_PRApprovalHistory.PR_ID,  " & _
                 "T_PRApprovalHistory.PR_Date,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Name,  " & _
                 "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_ID)=11050254)) " & _
        "ORDER BY T_PRApprovalHistory.Workflow_Step_Date")

'Move the data into the temporary table
rst.MoveFirst
Do While rst.EOF = False
             
    rstTemp.AddNew
            
    For i = 0 To rst.Fields.Count - 1
[B]        rstTemp.Fields(i).value = rst.Fields(i).value[/B]  
    Next
            
    rstTemp.Update
    rst.MoveNext
        
Loop

At this point, rstTemp (and thus tblMain) should have the results of the GROUP BY query in it.

In this code, the line in bold print is what actually assigns the field value from the original query results into the temporary table. In the CREATE TABLE statement, you can add or take away fields as you see fit (I just put three of your fields in for illustration. You might also need to adjust the field types according to your needs.

Pat

[NeoPa]

NeoPa,

I am not sure what i am looking for within the db. the structure is as follows:
...

If your error message is accurate, then the contents of the database (neither code nor data) are not where you need to be looking. I would be looking at the access (small-'a') you have to your database file. Maybe it's on a share you don't have full rights to. Maybe it's simply got the Read-Only attribute set. Have you tried renaming that file? That usually indicates whether you have enough right to do any work in it at all. If you can't rename the file, you won't get very far inside it (unless it's simply in use at the time). If this is your issue we'd need more details to help you progress.

[patjones]

If your error message is accurate, then the contents of the database (neither code nor data) are not where you need to be looking.

I think that could be one cause for this error. But I was able to build a simple table and form, and regenerate this same error by attempting to update a recordset bound to a GROUP BY query, just like dowling is trying to do. When I modified the SQL to not include any aggregates I was able to do the update fine.

I could very well be wrong, but I would be interested to know whether the solution I laid out above will work for what dowling wants to do...

Pat

[NeoPa]

I could very well be wrong, ...

That applies to all of us Pat.

If you experience that message when the file is clearly fully accessible, then don't let me stop you pursuing that approach. Personally I'd check the access to the file first, that being pretty quickly and easily done after all, but I expect that your understanding may well reap dividends if that's not the problem.

[dowlingm815]

the database is on my c: drive, there are no accessibility issues.

[dowlingm815]

can you kindly post your code from when it works to when it doesn' t work. when i elminate the count function, i receive a 3219 invalid operaton error.

Option Compare Database

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strFirstRec As String
Dim strPR_ID_TEMP As String
Dim strWorkflow_Step_Name_TEMP As String
Dim strWorkflow_Step_Date_TEMP As String

Private Sub Generate__PR_Status_Table_Click()
On Error GoTo Err_Hndlr
    
    MsgBox "Entered the application", vbInformation
    
    
strSQL = "SELECT T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date INTO T_Group_By_Test " & _
"FROM T_PRApprovalHistory GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date HAVING (((T_PRApprovalHistory.PR_ID) = 11050254)) ORDER BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.Workflow_Step_Date;"

Set rst = CurrentDb.OpenRecordset(strSQL)
    
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Generate__PR_Status_Table_Click()"
End Sub

[patjones]

Simply removing the COUNT function isn't what I suggested. Please read my post #8 and try to implement what I'm doing there.

Just taking out the COUNT function won't work, because the query still contains a GROUP BY clause. If you really need to use the results of this query, write them to a table and then work with the table as I'm suggesting in post #8...

Pat

[patjones]

Suppose I have some code, which prints out a list for a particular employee, with: ID, last name, first name, date worked, hours worked. Then I change the hours worked each day and reprint the results. This works...

Dim strSQL As String
Dim rst As Recordset

strSQL = "SELECT tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate, tblMain.fldHours 
          FROM tblMain INNER JOIN tblNames ON tblMain.fldERN = tblNames.fldERN 
          WHERE tblMain.fldERN = '" & Replace(Replace(Me.txtERN,"'",""),"""","") & "'"

Set rst = CurrentDb.OpenRecordset(strSQL)

With rst

    'Print old records
    .MoveFirst

    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & !fldHours
        .MoveNext
    Wend
        
    'Modify the records
    .MoveFirst
    
    While Not .EOF
        .Edit
        !fldHours = Me.txtHoursNew
        .Update
        .MoveNext
    Wend
        
    'Print modified records
    .MoveFirst

    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & !fldHours
        .MoveNext
    Wend

End With

Now, suppose I change the SQL string to

strSQL = "SELECT tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate, SUM(tblMain.fldHours) AS [Hours Count] 
          FROM tblMain INNER JOIN tblNames ON tblMain.fldERN = tblNames.fldERN 
          WHERE tblMain.fldERN = '" & Replace(Replace(Me.txtERN, "'", ""), """", "") & "' 
          GROUP BY tblMain.fldERN, tblNames.fldNameLast, tblNames.fldNameFirst, tblMain.fldDate "

which basically does the same thing, except it would, for instance, combine multiple records with the same date. I'll be able to print the records just as before:

With rst

    'Print old records
    .MoveFirst

    While Not .EOF
        Debug.Print !fldERN & "   " & !fldNameLast & "   " & !fldNameFirst & "   " & Format(!fldDate, "mm/dd/yy") & "   " & ![Hours Count]
        .MoveNext
    Wend

But when I go and try to modify something from within the query (it doesn't matter what field you pick):

    'Modify the records
    .MoveFirst
    
    While Not .EOF
        .Edit
        ![Hours Count] = Me.txtHoursNew
        .Update
        .MoveNext
    Wend

I get error 3027, just as you do. This is because the individual rows that result from doing a GROUP BY are combinations of multiple rows from the source table(s). In my instance, I can't go and modify total hours worked ([Hours Count]), because that result is the total from multiple rows in my source table.

If however I took my recordset here and dumped it into another table that I just created for that purpose, I'd be able to work with those records however I wanted to.

Does this make any sense?

Pat