Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-09-2007, 01:01 AM   #1
trackdaychamp
Access Guy
 
Join Date: Jan 2007
Location: London
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
trackdaychamp is on a distinguished road
Question Moving to objects

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

trackdaychamp is offline   Reply With Quote
Old 02-09-2007, 11:23 AM   #2
KernelK
Registered User
 
KernelK's Avatar
 
Join Date: Oct 2006
Location: Central FL, USA
Posts: 173
Thanks: 0
Thanked 0 Times in 0 Posts
KernelK is on a distinguished road
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."
KernelK is offline   Reply With Quote
Old 02-09-2007, 11:58 AM   #3
RoyVidar
Registered User
 
Join Date: Sep 2000
Location: Norway
Posts: 805
Thanks: 0
Thanked 5 Times in 4 Posts
RoyVidar is on a distinguished road
1 - use/execute queries in stead of recordsets when applicable
Code:
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.

__________________
Roy-Vidar
RoyVidar is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Import Objects by command script? chumporn General 2 11-01-2006 11:47 AM
Writing Code to Reference All Objects on a Form/Section akt01 Forms 4 06-19-2003 11:49 AM
n period moving average in queries rolex Queries 6 11-03-2002 12:45 PM
Moving objects on form via code Anauz Forms 5 03-02-2001 09:39 AM
Moving Focus to various objects ernief Forms 2 03-03-2000 06:11 AM




All times are GMT -8. The time now is 12:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World