Please help with ADO Code

[Al]

I am trying to rewrite the following code, using ADO instead but I am having
trouble with the ".edit". I keep getting a message that it is not a method.
what would be the equivelent in ADO to the following snippet of code?
thanks
Al
*******************************************
Dim strSql As String, db As Database, rst As Recordset
strSql = "SELECT EMP_ID, Assigned FROM tblkpEmployees where Emp_ID='" &
Me.EmpTrk_Emp_ID & "';"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

rst.MoveFirst
With rst
.Edit
!assigned = -1
.Update
End With
rst.Close
db.Close

 

[Brotha Lee]

There is no equivalent. You can't use the .edit in ADO. The records are
locked on the .update command

 

[Ed Adamthwaite]

Hi Al,
try the following:

Sub DoSomething()
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim s As String
Dim sSQL As String

sSQL = "SELECT EMP_ID, Assigned FROM tblkpEmployees " _
& "where Emp_ID='" & Me.EmpTrk_Emp_ID & "';"
'Debug.Print sSQL

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Open sSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
.MoveLast 'force error 3021 if no records
.MoveFirst
.Fields("assigned") = -1
.Update
End With
rs.Close
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case -2147217908 'command text not set
Case -2147217865 'cannot find table
Case 3021 'no records
Case Else
MsgBox "Problem with DoSomething()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
If Not rs Is Nothing Then Set rs = Nothing
conn.Close
End Sub
'---------------------------------------------------------------

Regards,
Ed.

 

[Post Tenebras Lux]

Take a look at the following:
http://msdn2.microsoft.com/en-us/library/aa189735(office.10).aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

http://support.microsoft.com/kb/325020

Should get you started.

 

[Al]

Thank you For the helpful links. I will check it out.
Al

 

[Al]

Thank you Ed, that is what I was looking for. I will try it.
Al