ADO Recordset.Find question 
Author Message
 ADO Recordset.Find question

With an ADO recordset, is the Criteria parameter to Find restricted to
an expressions on a single column?

I've encountered a problem which leads me to believe this may be so --
and leaves me in a quandary:

The criteria we thought we could use looks like this:

"ID = 12  AND FromDate Is Null AND ThruDate Is Null"

We get an error that indicates a problem with the criteria -- yet it's
perfectly valid in the WHERE clause of a query.

VBA Help in Access 2000 isn't much help here:

"criteria   A String containing a statement that specifies the column
name, comparison operator, and value to use in the search."

Note the singular use of "column" in the above (from help).  

Can I conclude that the singular is intentional -- that criteria in
Find can only reference one column?

If so, what to do?

-------------

Wes Peterson
LexCraft Data Services



Thu, 28 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Hi,

Yep, Find can only address one column, in ADO. Using a query, anyhow, for
complex case, is probably preferable in most cases, even in DAO.

Hoping it may help,
Vanderghast, Access MVP.


Quote:
> With an ADO recordset, is the Criteria parameter to Find restricted to
> an expressions on a single column?

> I've encountered a problem which leads me to believe this may be so --
> and leaves me in a quandary:

> The criteria we thought we could use looks like this:

> "ID = 12  AND FromDate Is Null AND ThruDate Is Null"

> We get an error that indicates a problem with the criteria -- yet it's
> perfectly valid in the WHERE clause of a query.

> VBA Help in Access 2000 isn't much help here:

> "criteria   A String containing a statement that specifies the column
> name, comparison operator, and value to use in the search."

> Note the singular use of "column" in the above (from help).

> Can I conclude that the singular is intentional -- that criteria in
> Find can only reference one column?

> If so, what to do?

> -------------

> Wes Peterson
> LexCraft Data Services




Fri, 29 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Michael,

Quote:
>Yep, Find can only address one column, in ADO. Using a query, anyhow, for
>complex case, is probably preferable in most cases, even in DAO.

Thank you  for confirming that.  It's hard to swallow, though.  

This is our first Access 2000 project, and we'd hoped to be able to do
things like this in a manner similar to the way we'd attack it with
Delphi (our main development platform).

Given an appropriate index, you can use Delphi's FindKey method like
so:

do while not rsSource.EOF
  begin
    if rsDest.FindKey([iID, dFromDate, dThruDate]) then
      // update the record
    else
      // add a new record
    rsSource.Next;
  end;

FindKey performs a direct index search and returns True if a match was
found.

What we wound up doing with VBA/ADO was more like (omitting some
obvious code):

Dim sCriteria As String

do until rsSource.EOF
   ' build the criteria string
   rsExam.Open sCriteria .....
  if rsExam.RecordCount > 0 then
    ' add a new record to rsDest
  else
    ; update the record in rsExam
  end if
  rsExam.Close
  rsSource.MoveNext
Loop

It works, but it sure seems wasteful to be opening and closing a
recordset with each pass through the loop.

-------------

Wes Peterson
LexCraft Data Services



Fri, 29 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Yeah, that's one of my gripes about the Jet 4 OLEDB provider too - for some
reason they decided not to support the Index property, if they did then you
could use the Seek method.  I can see where continually opening a Recordset
can be slow, especially since opening recordsets seems to be the main
performance problem with ADO with Access.

You might try using the Filter on your Recordset - it might be a little
faster than opening new ones constantly.

But the only way I know of to fix all the ADO performance problems with
Access is to just forget it and go back to DAO 3.6.

Alden


Quote:
> Michael,

> >Yep, Find can only address one column, in ADO. Using a query, anyhow, for
> >complex case, is probably preferable in most cases, even in DAO.

> Thank you  for confirming that.  It's hard to swallow, though.

> This is our first Access 2000 project, and we'd hoped to be able to do
> things like this in a manner similar to the way we'd attack it with
> Delphi (our main development platform).

> Given an appropriate index, you can use Delphi's FindKey method like
> so:

> do while not rsSource.EOF
>   begin
>     if rsDest.FindKey([iID, dFromDate, dThruDate]) then
>       // update the record
>     else
>       // add a new record
>     rsSource.Next;
>   end;

> FindKey performs a direct index search and returns True if a match was
> found.

> What we wound up doing with VBA/ADO was more like (omitting some
> obvious code):

> Dim sCriteria As String

> do until rsSource.EOF
>    ' build the criteria string
>    rsExam.Open sCriteria .....
>   if rsExam.RecordCount > 0 then
>     ' add a new record to rsDest
>   else
>     ; update the record in rsExam
>   end if
>   rsExam.Close
>   rsSource.MoveNext
> Loop

> It works, but it sure seems wasteful to be opening and closing a
> recordset with each pass through the loop.

> -------------

> Wes Peterson
> LexCraft Data Services




Fri, 29 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Alden,

Quote:
>Yeah, that's one of my gripes about the Jet 4 OLEDB provider too - for some
>reason they decided not to support the Index property

So the problem is in the Jet 4 OLEDB provider.  Bummer!  I was
thinking I might be better off getting ADO components for Delphi but
it the limitation is in the provider, that won't help.

Quote:
>You might try using the Filter on your Recordset - it might be a little
>faster than opening new ones constantly.

Hadn't thought of that.  Thanks.

Quote:
>But the only way I know of to fix all the ADO performance problems with
>Access is to just forget it and go back to DAO 3.6.

Grrrrr.  <g>

Thanks again.

-------------

Wes Peterson
LexCraft Data Services



Fri, 29 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Hi,

Seek is working with ADO 2.1, even on compound index:

    rs.Index="FirstAndLastNameIndex"
    rs.Seek Array("Wes", "Peterson"), adSeekFirstEQ
    If rs.EOF Then     MsgBox "Not found"

Hoping it may help,
Vanderghast, Access MVP.


Quote:
> Yeah, that's one of my gripes about the Jet 4 OLEDB provider too - for
some
> reason they decided not to support the Index property, if they did then
you
> could use the Seek method.  I can see where continually opening a
Recordset
> can be slow, especially since opening recordsets seems to be the main
> performance problem with ADO with Access.

> You might try using the Filter on your Recordset - it might be a little
> faster than opening new ones constantly.

> But the only way I know of to fix all the ADO performance problems with
> Access is to just forget it and go back to DAO 3.6.

> Alden



> > Michael,

> > >Yep, Find can only address one column, in ADO. Using a query, anyhow,
for
> > >complex case, is probably preferable in most cases, even in DAO.

> > Thank you  for confirming that.  It's hard to swallow, though.

> > This is our first Access 2000 project, and we'd hoped to be able to do
> > things like this in a manner similar to the way we'd attack it with
> > Delphi (our main development platform).

> > Given an appropriate index, you can use Delphi's FindKey method like
> > so:

> > do while not rsSource.EOF
> >   begin
> >     if rsDest.FindKey([iID, dFromDate, dThruDate]) then
> >       // update the record
> >     else
> >       // add a new record
> >     rsSource.Next;
> >   end;

> > FindKey performs a direct index search and returns True if a match was
> > found.

> > What we wound up doing with VBA/ADO was more like (omitting some
> > obvious code):

> > Dim sCriteria As String

> > do until rsSource.EOF
> >    ' build the criteria string
> >    rsExam.Open sCriteria .....
> >   if rsExam.RecordCount > 0 then
> >     ' add a new record to rsDest
> >   else
> >     ; update the record in rsExam
> >   end if
> >   rsExam.Close
> >   rsSource.MoveNext
> > Loop

> > It works, but it sure seems wasteful to be opening and closing a
> > recordset with each pass through the loop.

> > -------------

> > Wes Peterson
> > LexCraft Data Services




Sat, 30 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Michael,

Quote:
>Seek is working with ADO 2.1, even on compound index:

>    rs.Index="FirstAndLastNameIndex"
>    rs.Seek Array("Wes", "Peterson"), adSeekFirstEQ
>    If rs.EOF Then     MsgBox "Not found"

Thanks for that.  I'll give it a try.

-------------

Wes Peterson
LexCraft Data Services



Sat, 30 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Not true.  Jet 4 Oledb does support the Index property and the Seek method.


Quote:
> Yeah, that's one of my gripes about the Jet 4 OLEDB provider too - for
some
> reason they decided not to support the Index property, if they did then
you
> could use the Seek method.  I can see where continually opening a
Recordset
> can be slow, especially since opening recordsets seems to be the main
> performance problem with ADO with Access.

> You might try using the Filter on your Recordset - it might be a little
> faster than opening new ones constantly.

> But the only way I know of to fix all the ADO performance problems with
> Access is to just forget it and go back to DAO 3.6.

> Alden



> > Michael,

> > >Yep, Find can only address one column, in ADO. Using a query, anyhow,
for
> > >complex case, is probably preferable in most cases, even in DAO.

> > Thank you  for confirming that.  It's hard to swallow, though.

> > This is our first Access 2000 project, and we'd hoped to be able to do
> > things like this in a manner similar to the way we'd attack it with
> > Delphi (our main development platform).

> > Given an appropriate index, you can use Delphi's FindKey method like
> > so:

> > do while not rsSource.EOF
> >   begin
> >     if rsDest.FindKey([iID, dFromDate, dThruDate]) then
> >       // update the record
> >     else
> >       // add a new record
> >     rsSource.Next;
> >   end;

> > FindKey performs a direct index search and returns True if a match was
> > found.

> > What we wound up doing with VBA/ADO was more like (omitting some
> > obvious code):

> > Dim sCriteria As String

> > do until rsSource.EOF
> >    ' build the criteria string
> >    rsExam.Open sCriteria .....
> >   if rsExam.RecordCount > 0 then
> >     ' add a new record to rsDest
> >   else
> >     ; update the record in rsExam
> >   end if
> >   rsExam.Close
> >   rsSource.MoveNext
> > Loop

> > It works, but it sure seems wasteful to be opening and closing a
> > recordset with each pass through the loop.

> > -------------

> > Wes Peterson
> > LexCraft Data Services




Sat, 30 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Manuel,

Quote:
>Not true.  Jet 4 Oledb does support the Index property and the Seek method.

According to Help in Access 2000, you can determine if Seek will work
by using Support(adIndex).

I tried the following code -- and it seems to say it'll work:

Dim rsDest As Recordset
Set rsDest = New ADODB.Recordset

rsDest.CursorType = adOpenKeyset
rsDest.LockType = adLockOptimistic
rsDest.Open "ProdPriceCalendar", CurrentProject.Connection, , ,
adCmdTableDirect

If rsDest.Supports(adIndex) Then
  MsgBox "Seek sould work!", vbOKOnly, "Status"
Else
  MsgBox "Can't use seek.", vbOKOnly, "Status"
End If

Now to go back and actually try Seek. <g>

-------------

Wes Peterson
LexCraft Data Services



Sun, 31 Mar 2002 03:00:00 GMT  
 ADO Recordset.Find question
Hi,

Just a detail, but an important one, your recordset has to be opened with
adCmdTableDirect; the default, adCmdUnknown, won't necessary allow you to
use Seek, a little bit like, in DAO, you cannot use Seek on a
dbOpenDynaset...

Hoping it may help,
Vanderghast, Access MVP


Quote:
> Michael,

> >Seek is working with ADO 2.1, even on compound index:

> >    rs.Index="FirstAndLastNameIndex"
> >    rs.Seek Array("Wes", "Peterson"), adSeekFirstEQ
> >    If rs.EOF Then     MsgBox "Not found"

> Thanks for that.  I'll give it a try.

> -------------

> Wes Peterson
> LexCraft Data Services




Tue, 02 Apr 2002 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. ADO - recordset.find question

2. ADO recordset.find question

3. ADO Finding Highest Number in ADO Recordset field?

4. Open ADO recordset on another ADO recordset - possible?

5. ADO recordsets / FIND

6. ADO Recordset find method

7. ADO Recordset Find

8. ADO Recordset Seek or Find

9. ADO frustration-recordset Find method

10. Finding Duplicates in ADO recordset

11. Recordset Find method in ADO

12. Recordset Find method in ADO (Corrected)

 

 
Powered by phpBB® Forum Software