Can't update Records via ADO

[mmitchell]

I am converting from DAO to ADO in Access 2002 and using MS SQL2000 for my back end and when I run this code I get the error for the line I have colored red below; "Current recordset does not support updating. This may be a limitation of the provicer, or of the selected locktype."

The table is updateable, at least I can open it in Access and append new records and delete and update exitisting records.

Dim rstItems As Recordset, rstSchdlItems As Recordset, [COLOR=Blue]rstItemItems As Recordset[/COLOR]
Dim strItems As String, strSchdlItems As String
Dim lngOldItemTblItemID As Long, lngNewItemTblItemID
        
strItems = "SELECT T_Schedule_ProductionItems.ItemID, T_Schedule_ProductionItems.OrigID, T_Schedule_BatchItems.OrigID, T_Schedule_BatchItems.ItemID " & _
        "FROM T_Schedule_ProductionItems INNER JOIN T_Schedule_BatchItems ON T_Schedule_ProductionItems.OrigID = T_Schedule_BatchItems.OrigID"
           
Set rstItems = CurrentProject.Connection.Execute(strItems)

Set rstSchdlItems = CurrentProject.Connection.Execute("T_Schedule_ProductionSchedule")

[COLOR=Blue]Set rstItemItems = CurrentProject.Connection.Execute("T_Schedule_ProductionItems")[/COLOR]

If rstItems.BOF = False Then    '** records were found that need synch
    rstItems.MoveFirst
    Do
        lngOldItemTblItemID = rstItems.Fields(0)  '*[T-Schedule-ProductionItems].ItemID = 0
        lngNewItemTblItemID = rstItems.Fields(3)  '* [T-Schedule-BatchItems].ItemID = 3
        
        'updating tbl T-Schedule-ProductionItems
         rstItemItems.Find "[ItemID] = " & lngOldItemTblItemID
         If rstItemItems.EOF = False Then  '** found record with matching ID
            [COLOR=Red]rstItemItems.Fields("ItemID") = lngNewItemTblItemID[/COLOR]
            rstItemItems.Update
        End If
        
               rstSchdlItems.Find "[ItemID] = " & lngOldItemTblItemID
               If rstSchdlItems.EOF = False Then  '** found record with matching ID
        Do
           
            rstSchdlItems.Fields("ItemID") = lngNewItemTblItemID
            rstSchdlItems.Update
            
            rstSchdlItems.Find "[ItemID] = " & lngOldItemTblItemID
     
        Loop Until rstSchdlItems.EOF = True
        End If
        
    rstItems.MoveNext
    Loop Until rstItems.EOF = True   '** we have passed the last record in the recordset
End If

rstItems.Close
rstSchdlItems.Close

 

[WayneRyan]

Mitch,

rstItemItems.Edit
rstItemItems.Fields("ItemID") = lngNewItemTblItemID
rstItemItems.Update

Wayne

 

[mmitchell]

I thought you were supposed to take that out when using ADO?

When I put it back in (I had it originally in the old code) I get an error: "Method or data member not found"

 

[mmitchell]

If use the following code it works:

Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection 
rs.Open "T_Schedule_ProductionItems", cnn, 3, 3 'read write =  adOpenStatic, adLockOptimistic
                                               'read only would be rs.open "MyTable",cnn,3,1
With rs
    .Find "[ItemID] = " & 1
    .Fields("ItemID").Value = 701
    .Update
End With
rs.Close
Set cnn = Nothing
Set rs = Nothing

So, am I ussing the "CurrentProject.Connection.Execute" incorectly?