Moving to objects

[trackdaychamp]

Hi,

I need to convert some old code to ADODB format and I am having trouble finding the correct methods to use with the ADODB Recordset objects

Old Method -> New Method with ADODB connection objects
1 FindFirst() -> Find()? (I think this one will be ok)
2 NoMatch() -> ???
See old code below:

Dim db As Database
Dim rtable As Recordset
Dim dtable As Recordset

Set db = CurrentDb()
Set rtable = db.OpenRecordset("Tbl_I", dbOpenDynaset)
Set dtable = db.OpenRecordset("Tbl_d", dbOpenDynaset)

StrCriteria = "[IntID] = " & "'" & Me![lstInt].Column(0) & "'"
rtable.FindFirst StrCriteria

If Not rtable.NoMatch Then

dtable.AddNew
dtable("ProjectID") = rtable("ProjectID")
dtable("Interdependency") = rtable("Interdependency"

dtable.Update

'...............delete risk from live system
rtable.Delete

End If

 

[KernelK]

The NoMatch property is used in conjunction with the FindFirst method. In ADO, you do indeed use the Find method. But, instead of referring to the NoMatch property to determine if a match was found, you check the BOF and EOF properties. From Access help regarding the Find method of an ADO recordset: "If the criteria is met, the current row position is set on the found record; otherwise, the position is set to the end (or start) of the Recordset."

 

[RoyVidar]

1 - use/execute queries in stead of recordsets when applicable

strSql = "INSERT INTO Tbl_d (ProjectID, Interdependency) " & _
         "SELECT ProjectID, Interdependency " & _
         "FROM Tbl_I " & _
         "WHERE [IntID] ='" & Me![lstInt].Column(0) & "'"

' ADO

CurrentProject.Connection.Execute strSql, , adCmdText+adExecuteNoRecords

' DAO

CurrentDB.Execute strSql, dbFailOnError

strSql = "DELETE FROM Tbl_I " & _
         "WHERE [IntID] = '" & Me![lstInt].Column(0) & "'"
' execute...

2 - is there any particular reason why you want to switch to ADO? The usual recommendation, is to stay with DAO as long as you are using Jet.