ADO Recordset

[mission2java_78]

Anyone know how to edit an ADO recordset? The edit method isnt available..I see an EditMode..but not sure if thats it...
basically I sqled a record out and wanting to use a recordset method to edit it...

rs.edit
rs("field").Value = "test"
rs.update
...

but edit isnt an ADO method...

I need to use ADO also

Jon

 

[mission2java_78]

Still having a bit of problem with this...
I cant seem to edit an ADO recordset...

The following error occurs:

Object / provider is not capable of performing this operation.

Code is as follows:

Private Sub UpdateSOMTRecord(ByVal recordID As Long, ByVal taskID As Long, completeFlag As Long)

    Dim Conn1 As New ADODB.Connection
    'Dim Rs1 As New ADODB.Recordset
    Dim Rs2 As New ADODB.Recordset
    Dim Errs1 As Errors
    Dim AccessConnect As String, strExecute As String, strExecute2 As String
    Dim strTmp As String
    
      
    ' Don't assume that we have a connection object.
    On Error GoTo AdoError
    
    'connect to the access database.
    'DBQ = supply path of access db \\Server\...
    AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                  "Dbq=C:\Temp\SOMT.mdb;" & _
                  "DefaultDir=;" & _
                  "Uid=;Pwd=;"
    
    If completeFlag = -1 Then
        strExecute = "UPDATE tblTasks SET Completed = " & completeFlag & _
                ", Status ='Closed', AFinish=PFinish WHERE RecordID = " & recordID & _
                " AND TaskID = " & taskID
        
        strExecute2 = "SELECT * FROM tblTasks WHERE RecordID = " & recordID & _
                      " AND Status ='Pending' AND EmailSent = False AND Completed = False Order By TaskID"
    
    Rs2.LockType = adLockOptimistic
    Conn1.Open AccessConnect
    Conn1.Execute (strExecute)
    
    'Set Rs1 = Conn1.Execute(strExecute)
    Set Rs2 = Conn1.Execute(strExecute2)
    MsgBox "Set RS2"
    
    Rs2.MoveFirst
    MsgBox "Moved RS2 to first"
    If Rs2.BOF Then
        'do nothing
    Else
        MsgBox "RS2 ELSE"
        
        Rs2("Status").Value = "Active"
        MsgBox "RS2 UPDATED"
    End If
    
    Else
        strExecute = "UPDATE tblTasks SET Completed = " & completeFlag & _
                ", Status = 'Pending', AFinish=Null WHERE RecordID = " & recordID & _
                " AND TaskID = " & taskID
        
        Conn1.Open AccessConnect
        Conn1.Execute (strExecute)
        'Set Rs1 = Conn1.Execute(strExecute)
    
    End If
    
    Conn1.Close
    
Done:
'    Set Rs1 = Nothing
    Set Rs2 = Nothing
    Set Conn1 = Nothing
    Exit Sub

AdoError:
    ' Get VB Error Object's information
    strTmp = strTmp & vbCrLf & "VB Error # " & Str(Err.Number)
    strTmp = strTmp & vbCrLf & "   Generated by " & Err.Source
    strTmp = strTmp & vbCrLf & "   Description  " & Err.Description

    MsgBox strTmp

    GoTo Done
End Sub

All im looking to do is to update an ADO record.

Jon

 

[dgm]

Hey Jon, this is straight from a book so I will assume that it works.

Sub Editrecords()
' Procedure that edits data
Dim rst As ADODB.Recordset
Dim strSQL As String

On Error GoTo errHandler

'Create the recordset object
Set rst = New ADODB.Recordset

'Write the SQL Statement to return the row you want
strSQL = "Select * FROM Customers WHERE CustomerID ='JimDan'"

'Open the recordset with a keyset cursor
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'Update the row
rst!CompanyName = "New Name"
'Use the Update Method or MoveNext
rst.Update

exitHandler:
    Exit Sub
errHandler:
    MsgBox Err.Description
    Resume exitHandler
End Sub

Dave