Please help with ADO Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
There is no equivalent. You can't use the .edit in ADO. The records are
locked on the .update command
 
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.
 
Back
Top