Function produces Error 3027 - Cannot update when running rs.edit (1 Viewer)

[adhoustonj]

Hello AWF,
I have just migrated a back end access db to sql server, and have been altering code to include the dbOpenDynaset & dbSeeChanges as I was getting error messages about needing dbSeeChanges when interacting with IDENTITY columns in sql server.

The below function is called in a procedure with a form load event, and I'm getting the 3027 error about read only, cannot update when it is editing the recordset. All tables have primary keys, which seemed to solve for other threads I've read. Can I still use this code or am I missing anything obvious?

Thanks

Function sort_time_color(pcd As LongPtr)
Dim newseq As LongPtr, side As LongPtr, tasksec As LongPtr, sta As LongPtr, newst_seq As LongPtr
Dim newv As String
Dim stc As Integer
Set db = CurrentDb()
'resequence the pcd seq numbers
        
strSQL = "SELECT tblTask.seq, tblTask.st_seq, tblTask.task_id, tblTask.stat_id, tblTask.stat_color " _
& "FROM (tblSide INNER JOIN tblZone ON tblSide.side_id = tblZone.side_id) INNER JOIN ((tblStation INNER JOIN tblPCDzone ON tblStation.zone_id = tblPCDzone.zone_id) INNER JOIN tblTask ON (tblTask.pcd_id = tblPCDzone.pcd_id) AND (tblStation.stat_id = tblTask.stat_id)) ON (tblZone.zone_id = tblStation.zone_id) AND (tblZone.zone_id = tblPCDzone.zone_id) " _
& "WHERE (((tblPCDzone.pcd_id) = " & pcd & ")) " _
& "ORDER BY tblSide.side_id, tblStation.sec, tblTask.position, tblTask.seq, IIf([seq1] Is Null,0,[seq1]) DESC , tblTask.seq2 DESC"
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    newseq = 1
    newst_seq = 1
    stc = 1
    sta = rs!stat_id
    While Not rs.EOF
        If IsNull(rs!seq) Or rs!seq <> newseq Then
            rs.Edit
            rs!seq = newseq
            rs.Update
        End If
        If IsNull(rs!stat_id) Or rs!stat_id <> sta Then
            newst_seq = 1
            If stc = 1 Then
                rs.Edit
                rs!stat_color = 2
                rs.Update
                stc = 2
            Else
                rs.Edit
                rs!stat_color = 1
                rs.Update
                stc = 1
            End If
        Else
'ERROR is on next line "rs.Edit"
            rs.Edit
            rs!stat_color = stc
            rs.Update
        End If
        If IsNull(rs!st_seq) Or rs!st_seq <> newst_seq Then
            rs.Edit
            rs!st_seq = newst_seq
            rs.Update
        End If
        
        newseq = newseq + 1
        newst_seq = newst_seq + 1
        sta = rs!stat_id
        rs.MoveNext
    Wend
strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL
    
    'task secs
    strSQL = "UPDATE tblTask LEFT JOIN tblTask_times ON (tblTask.task_id = tblTask_times.task_id) AND (tblTask.task_id = tblTask_times.task_id) SET tblTask.task_secs = 0 " _
    & "WHERE (((tblTask.task_secs)>0) AND ((tblTask_times.task_id) Is Null) and ((tblTask.pcd_id)=" & pcd & "))"
    db.Execute strSQL
    
    strSQL = "INSERT INTO ltblTemp_secs ( tid, secs ) " _
    & "SELECT qryTask_secs.task_id, qryTask_secs.task_sec " _
    & "FROM qryTask_secs INNER JOIN tblTask ON qryTask_secs.task_id = tblTask.task_id " _
    & "WHERE (((tblTask.pcd_id)=" & pcd & "))"
    db.Execute strSQL
    
    strSQL = "UPDATE tblTask INNER JOIN ltblTemp_secs ON tblTask.task_id = ltblTemp_secs.tid SET tblTask.task_secs = [ltblTemp_secs]![secs];"
    db.Execute strSQL
    
    strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL

    'station secs
    strSQL = "INSERT INTO ltblTemp_secs ( tid, secs ) " _
    & "SELECT qryStation_secs.stat_id, qryStation_secs.stat_sec " _
    & "FROM qryStation_secs " _
    & "WHERE (((qryStation_secs.pcd_id)=" & pcd & "));"
    db.Execute strSQL
    
    strSQL = "UPDATE tblTask INNER JOIN ltblTemp_secs ON tblTask.stat_id = ltblTemp_secs.tid SET tblTask.stat_secs = [ltblTemp_secs]![secs];"
    db.Execute strSQL
    
    strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[Minty]

Assuming that picture is of the query that wouldn't be editable.
If it isn't the quick and dirty check is to paste your SQL query string into a new blank query and see if you can edit it.

If you can't you may need to get creative with linking a view from SQL Server to the table you want to update, and see if that becomes updateable.

 

[adhoustonj]

Thanks Minty. Yes, that pic attached was the query put into the query builder. It was editable before migrating to sql server. What I've done for now just in testing is inserted into a local temp table in the front end access app, and it now does not produce an error. I have a lot of work ahead of me it seems if i want to move this db into production with sql server vs the previous access backend.

strSQL = "INSERT INTO tblTempTest(seq,st_seq,task_id,stat_id,stat_color) " _
& "SELECT tblTask.seq, tblTask.st_seq, tblTask.task_id, tblTask.stat_id, tblTask.stat_color " _
& "FROM (tblSide INNER JOIN tblZone ON tblSide.side_id = tblZone.side_id) INNER JOIN ((tblStation INNER JOIN tblPCDzone ON tblStation.zone_id = tblPCDzone.zone_id) INNER JOIN tblTask ON (tblTask.pcd_id = tblPCDzone.pcd_id) AND (tblStation.stat_id = tblTask.stat_id)) ON (tblZone.zone_id = tblStation.zone_id) AND (tblZone.zone_id = tblPCDzone.zone_id) " _
& "WHERE (((tblPCDzone.pcd_id) = " & pcd & ")) " _
& "ORDER BY tblSide.side_id, tblStation.sec, tblTask.position, tblTask.seq, IIf([seq1] Is Null,0,[seq1]) DESC , tblTask.seq2 DESC"

strSQL = "SELECT seq,st_seq,task_id,stat_id,stat_color FROM tblTempTest"
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

 

[Minty]

I'm amazed that you could edit a query with joins like that, well maybe edit but certainly not add rows.
Have you tried removing the double join between the Zone_id's in the differing tables? I can't imagine they are needed.

Alternatively, create the subsequent data from the tables you are joining to minus the table you need to edit as a SQL view join to that and you will probably be able to edit it.

 

[Isaac]

I can't resist asking why in the world do you even WANT to do it this way? Editing a multi-table query just isn't something that ought to be done normally in database development. Editing a multi-table query when the tables are relational is like deliberately trying to violate the carefully planned (I hope) relationships of your database. You should be inserting records into the proper tables and performing updates in single tables. Or using bound forms with subforms, in which scenario Access itself helps to handle the simultaneous editing of multi-table data.

The usual rule of thumb is, don't expect to be able to update a query with multiple tables involved. (I'm generalizing here). Like Minty said, I'm surprised you could. But don't pursue the activity just because you found a glimmer of an exception. Understand why you wouldn't want to do that and learn how to do it right.

 

[adhoustonj]

I'm amazed that you could edit a query with joins like that, well maybe edit but certainly not add rows.
Have you tried removing the double join between the Zone_id's in the differing tables? I can't imagine they are needed.

Alternatively, create the subsequent data from the tables you are joining to minus the table you need to edit as a SQL view join to that and you will probably be able to edit it.

I will do some experimenting with views. Thanks for your input and suggestions!

 

[adhoustonj]

I can't resist asking why in the world do you even WANT to do it this way? Editing a multi-table query just isn't something that ought to be done normally in database development. Editing a multi-table query when the tables are relational is like deliberately trying to violate the carefully planned (I hope) relationships of your database. You should be inserting records into the proper tables and performing updates in single tables. Or using bound forms with subforms, in which scenario Access itself helps to handle the simultaneous editing of multi-table data.

The usual rule of thumb is, don't expect to be able to update a query with multiple tables involved. (I'm generalizing here). Like Minty said, I'm surprised you could. But don't pursue the activity just because you found a glimmer of an exception. Understand why you wouldn't want to do that and learn how to do it right.

Good feedback. I inherited this db is the short answer. The bound form does have two subforms.
I'm understanding what you are saying.. I will have to work to apply that to the sql/vba mix of opening a recordset and moving through it.. etc.

So I should always try to have only one join (if needed) when running an update statement?

 

[Isaac]

If your goal is to figure out how to be doing updates on related multi-table queries, without depending on bound forms and subforms, I have no comment as I don't do that, period.

My comment is more along the lines of, outside the context of subforms and bound forms, when you need to do an update, it's generally done to a single table. If you need to update a record, they're only ever in one table at a time, therefore you update one table.

No company I've ever worked for (most of which updated anywhere from 10's of thousands to millions of records per day) is ever trying to update records in more than one table at once. Sure, sometimes you have a new child record that needs a parent created first ... so you insert the parent, grab the PK created from that parent insert, and then insert the child with the knowledge of what its record's FK will be.
If you are updating a child, you just update the child with the appropriate Where clause.

I'm just saying in normal database development - at least, my 17 years of it - you don't update multi table queries. There just isn't a reason to.
What you update are records. A record by definition is in one table, not many at once.

The main exception to all this is in a front end like Access, where you have to have a multi-table QUERY because it's the recordsource of a bound form. And you want to update one column or field on a form. That is the only time it makes sense to want to update a multi-table QUERY. And the general way to accomplish this is to use Subforms, wherein each subform actually ISN'T based on a multi table query, it's based on a table (or single-table query), which is related to its parent form by a Key. Access helps control the situation and make sure you don't violate the integrity of the database, like inserting a new child with no parent (etc).

The thing is that in your case none of this was the case. You're running a VBA statement outside of a form to make an update.
In this case why do you need multiple tables in the SQL statement at all? The record you are updating at any given time only resides in a single table.

 

[adhoustonj]

@Isaac that is not my goal.
Is doing updates on related multi-table queries, WITH depending on bound forms and subforms okay?
I thought that was what was happening. And I'm not trying to update records in more than one table at once - I would never even attempt. I was kind of surprised though that I can not (or maybe shouldn't) update fields in one table when a recordset is comprised of a multi table join query. It does make sense that I could create a view/temp table with the multi-table join query and then just join to that when doing the updates. I'm trying to understand the logic of the joins in the function.. You are right they may not be needed.

 

[Minty]

You will probably find the only reason for the multi-table joins is to populate related controls on the form, this looks better than a load of combos displaying the same data, and is quicker to build.

An inexperienced developer will then discover, by some miracle, that they can update the underlying query and simply do so, but it's not efficient, and as you have discovered doesn't scale well once you move out of a pure access development.

I'm currently re-working an unsplit client Access database into SQL and there are numerous forms that simply drag in all the records because in their existing usage environment it works. Constant crashing and corruption mean it now needs a lot of adjusting to work efficiently in a more robust manner.

 

[Isaac]

the original post is a VBA function not a bound form

 

[Minty]

the original post is a VBA function not a bound form

@Isaac Yes, but I suspect the query was simply copied from a form's recordsouce as it worked and had the right records in it.

 

[adhoustonj]

The function is called from the form. The initial query/picture I provided is the first strSQL in the function.

Private Sub Form_Load()
Dim pcd As LongPtr
pcd = [Forms]![frmPCDcont]![pcd_id]
 Call sort_time_color(pcd)

End Sub

and the forms recordsource is a different query.

SELECT Switch([front_rear]=1,"front",[front_rear]=2,"rear") AS f_r, *
FROM tblTask LEFT JOIN (SELECT tblPCD.pcd_id AS tbpcd_id, tblPCD.pcd, tblPCD.pcd_title, tblPCD.rev, tblModel.model, tblStation.stat_id AS tbl_stat_id, tblStation.station, tblStation.front_rear, tblZone.zone, tblZone.order, tblSide.side, tblSide.side_id, tblStation.sec AS stat_seq FROM tblModel INNER JOIN tblPCD ON tblModel.mod_id = tblPCD.mod_id, (tblSide INNER JOIN tblZone ON tblSide.side_id = tblZone.side_id) INNER JOIN tblStation ON tblZone.zone_id = tblStation.zone_id WHERE (((tblPCD.pcd_id)=[Forms]![frmPCDcont]![pcd_id])))  AS s ON (tblTask.pcd_id = s.tbpcd_id) AND (tblTask.stat_id = s.tbl_stat_id)
WHERE (((tblTask.pcd_id)=[Forms]![frmPCDcont]![pcd_id]))
ORDER BY s.side_id, s.front_rear, tblTask.seq;

 

[Pat Hartman]

An inexperienced developer will then discover, by some miracle, that they can update the underlying query and simply do so, but it's not efficient, and as you have discovered doesn't scale well once you move out of a pure access development.

But the real reason is that it is logically dangerous. Most joins that are updateable in Access will also be updateable in SQL Server but there are some exceptions. I frequently bind my forms to multi-table joins. ONE table I intend to update and perhaps several "lookup" tables so that I don't need to use VBA to populate the other fields from the master combo or using dLookup()'s.

The SAFE way to do this is to LOCK all the lookup values. For example, on an order form, you choose the customer but the combo when its closed will only show the the customer name. But you need to also see the Billing Address and the SalesRep. You could concatenate the address with the Customer name so that when the combo closes, you can see the Address info below the Customer name but it makes no sense to include the sales rep there so you really want that to show in a separate field. So I just lock the address and SalesRep field to prevent them from being changed on this form. Those pieces of data belong to the Customer. They do NOT belong to the order and you don't want someone to accidentally update them on the order thinking that they are only affecting THIS order when in fact, they are changing EVERY order.

Also, the OP does not appear to have bothered with RI. That is a mistake in most databases.

 

[adhoustonj]

Pat, is it the double join in the query picture that makes it appear no RI? I thought it was being followed (not in that query example, in the db). Here is relationship diagram.

I thought the structure was correct. I haven't explained this db much either - but basically it is a process engineering db. You have a process, it can have 100 tasks, those tasks can have 10 steps each. Then tasks are assigned to stations, that have sides (left, right, front), and those stations are in zones. The tasks steps have time associated and can be moved from station to station, or in a different sequences, etc.

So there are locked down fields like you are referring to on the form, and tasks are in two subforms on that form. One sub for left and right/other. As the form loads or changes in sequencing/time are made, this function recalculates the row color, the time by station (sums up all task times), etc.

I have just started the process of Moving this to sql server, which is a must for wifi/vpn, and hopefully performance. Initial speed/time tests indicate I need a lot of optimization, and is making me think I need to restructure the tables & relationships, or the way I handle the records with vba.

First on the list is converting all DAO to ADODB and then identifying bottlenecks and optimization opportunities. Is that a bad plan?

 

[Pat Hartman]

I don't know why the joins with RI are not showing that but at least two joins in the query are incorrect and that means the table schema is incorrect. Relationships are always FK to PK they are never data field to data field. One of the fields is ALWAYS the PK of the 1-side table. You are joining pcd_id to pcd_id between PCDzone and Task. pcd_id is a data field. you probably need to replace pcd_id in Task with pcdz_id and then the relationship (and Join) will be correct. The join from zone_id in Station to zone_id in PCDzone is unnecessary as well as being incorrect.

It looks like the joins should be:
tblSide.side_id -- tblZone.side_id
tblZone.zone_id -- tblPCDzone.zone_id
tblPCDzone.pcdz_id -- tblTask.pcdz_id (once you fix this mistake)
tblTask.stat_id -- tblStation_id

That makes a strait line and so is probably going to be updateable. But always make sure you actually need all the tables when you are building queies.

 

[adhoustonj]

@Pat Hartman that makes perfect sense. I actually don't even need the tblPCDzone.pcdz_id -- tblTask.pcdz_id as I was joining by pcd_id to have a where clause tblPCDZone.pcd_id = " & pcd & ". I can just use the join tblZone.zone_id -- tbltask.zone_id to filter tbltask.pcd_id.

My function is working now! thanks all for the help. It is just really slow. about 5x here I come adodb.

Function sort_time_color(pcd As LongPtr)
Dim newseq As LongPtr, side As LongPtr, tasksec As LongPtr, sta As LongPtr, newst_seq As LongPtr
Dim newv As String, strSQL As String
Dim stc As Integer
Dim db As DAO.Database

Set db = CurrentDb()
'resequence the pcd seq numbers
       
strSQL = "SELECT tblTask.seq, tblTask.st_seq, tblTask.task_id, tblTask.stat_id, tblTask.stat_color " _
& "FROM (tblSide INNER JOIN tblZone ON tblSide.side_id = tblZone.side_id) INNER JOIN (tblStation INNER JOIN tblTask ON tblStation.stat_id = tblTask.stat_id) ON tblZone.zone_id = tblStation.zone_id " _
& "WHERE (((tblTask.pcd_id) = " & pcd & ")) " _
& "ORDER BY tblSide.side_id, tblStation.sec, tblTask.position, tblTask.seq, IIf([seq1] Is Null,0,[seq1]) DESC , tblTask.seq2 DESC"


'rs.Open strSQL, adOpenKeySet, adLockOptimistic
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    newseq = 1
    newst_seq = 1
    stc = 1
    sta = rs!stat_id
    While Not rs.EOF
        If IsNull(rs!seq) Or rs!seq <> newseq Then
            rs.Edit
            rs!seq = newseq
            rs.Update
        End If
        If IsNull(rs!stat_id) Or rs!stat_id <> sta Then
            newst_seq = 1
            If stc = 1 Then
                rs.Edit
                rs!stat_color = 2
                rs.Update
                stc = 2
            Else
                rs.Edit
                rs!stat_color = 1
                rs.Update
                stc = 1
            End If
        Else
            rs.Edit
            rs!stat_color = stc
            rs.Update
        End If
        If IsNull(rs!st_seq) Or rs!st_seq <> newst_seq Then
            rs.Edit
            rs!st_seq = newst_seq
            rs.Update
        End If
       
        newseq = newseq + 1
        newst_seq = newst_seq + 1
        sta = rs!stat_id
        rs.MoveNext
    Wend
strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL
   
    'task secs
    strSQL = "UPDATE tblTask LEFT JOIN tblTask_times ON (tblTask.task_id = tblTask_times.task_id) AND (tblTask.task_id = tblTask_times.task_id) SET tblTask.task_secs = 0 " _
    & "WHERE (((tblTask.task_secs)>0) AND ((tblTask_times.task_id) Is Null) and ((tblTask.pcd_id)=" & pcd & "))"
    db.Execute strSQL
   
    strSQL = "INSERT INTO ltblTemp_secs ( tid, secs ) " _
    & "SELECT qryTask_secs.task_id, qryTask_secs.task_sec " _
    & "FROM qryTask_secs INNER JOIN tblTask ON qryTask_secs.task_id = tblTask.task_id " _
    & "WHERE (((tblTask.pcd_id)=" & pcd & "))"
    db.Execute strSQL
   
    strSQL = "UPDATE tblTask INNER JOIN ltblTemp_secs ON tblTask.task_id = ltblTemp_secs.tid SET tblTask.task_secs = [ltblTemp_secs]![secs];"
    db.Execute strSQL
   
    strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL

    'station secs
    strSQL = "INSERT INTO ltblTemp_secs ( tid, secs ) " _
    & "SELECT qryStation_secs.stat_id, qryStation_secs.stat_sec " _
    & "FROM qryStation_secs " _
    & "WHERE (((qryStation_secs.pcd_id)=" & pcd & "));"
    db.Execute strSQL
   
    strSQL = "UPDATE tblTask INNER JOIN ltblTemp_secs ON tblTask.stat_id = ltblTemp_secs.tid SET tblTask.stat_secs = [ltblTemp_secs]![secs];"
    db.Execute strSQL
   
    strSQL = "DELETE ltblTemp_secs.* FROM ltblTemp_secs"
    db.Execute strSQL
   
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[Pat Hartman]

But you STILL NEED TO FIX THE table design. This relationship

tblPCDzone.pcdz_id -- tblTask.pcdz_id (once you fix this mistake) --- is WRONG as you have it defined. Add the pcdz_id to tblTask. Create an update query that joins to tblPCDzone on the other field and update the new pcdz_id with the correct value.

If pcd_id is not unique in PCDzone, you are in big trouble since you will never be able to assign the correct FK to tblTasks.

 

[adhoustonj]

pcd_id is not unique in PCDzone.
And the more I have looked at this, the more trouble I'm starting to think I am in.

tblPCDzone is a junction table of all tblPCD.pcd_id and tblZone.zone_id to track PCD/Zone revisions.

Could I use tblTask.stat_id to tblStation.stat_id(pk) in some way to get out of trouble? There could be tasks with no stations sometimes but remain in the db to be assigned again on another pcd potentially.

 

[Pat Hartman]

You are missing lots of relationships including- you have no relationship defined between tblTask and tblStation so that won't help you. It is the same problem as with tblPCDzone. You do have stat_id in the tblTask so you could define a relationship but you could also insert tblPCD in the query so you can join from task to PCD and from PCD to PCDzone. However, since Station is a child of Zone which is a child of PCDzone, having stat_id in tblTask could lead to a conflict. Having two paths to the same table cannot be right. How would you know that the stat_id that Task points to is the same value you would get were you to follow the path via PCD and there is definitely a m-m relationship between PCD and stat so how can you possibly pick ONE value for stat to have in Task?

You also have tables without primary keys. Those tables will not be updateable via Access nor will any query that includes them.

If you don't have a direct line between the table you are trying to update and the criteria you are using for selection, I'm not sure what you expect to happen. The results would seem to be unrelated to reality.

You really need to sit back and rethink this schema very carefully.

One thing that may help you to focus is to delete the tables from the diagram that are there strictly for lookup. That won't delete the relationship. It will simply remove them from the picture which eliminates clutter and lets you organize the remaining tables more coherently. I try very hard to eliminate crossed lines since they confuse the image. And I put the many-side table to the right of the 1-side table except for m-m tables. So, for the other side of the m-m, the tables go in reverse. they go "up" rather than "down". an alternative is to stack the relationships so that both sides are LEFT of the junction table. One on top of the other and the junction table is always on the far right.

The Access relationship window leaves a lot to be desired. Since the tables are in SQL Server, you can use that tool which allows you to have multiple views of the schema. This is very useful in a large database to help you to group related tables together without a lot of crossing lines.