ADO Recordset.find method Criteria

Can you use an adodb.recordset.find method to locate a record on an MS SQL 2000 server table? I can't figure out how to specify the criteria correctly and haven't found any good documentation. Ex:
Dim tblBusiness As New ADODB.Recordset
'Make an ADO connection to the database.
cn.ConnectionString = "XXXXXXXXXXXXXX'"
cn.Open
cn.Properties("Current Catalog") = "MyDB"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
I haven't found anything detailing how to specify the criteria for the find.
Thanks.
My recommendation would be not to use Find, but to use a select
statement with a WHERE clause, selecting only required data. What you
are trying to do is fetch an entire table, then throw away the rows
you don't want. Think for a moment about what happens to the network,
the server, and your client application when the table has 1,000,000
rows. "SELECT <column list> FROM <TableName> WHERE <some
field>=Criteria" is far more efficient than filtering after the fact.
--Mary
On Sat, 8 May 2004 16:31:01 -0700, "DB"
<anonymous@.discussions.microsoft.com> wrote:

>Can you use an adodb.recordset.find method to locate a record on an MS SQL 2000 server table? I can't figure out how to specify the criteria correctly and haven't found any good documentation. Ex:
> Dim tblBusiness As New ADODB.Recordset
>'Make an ADO connection to the database.
> cn.ConnectionString = "XXXXXXXXXXXXXX'"
> cn.Open
> cn.Properties("Current Catalog") = "MyDB"
> cmd.ActiveConnection = cn
> cmd.CommandType = adCmdText
> tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
> tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
>I haven't found anything detailing how to specify the criteria for the find.
>Thanks.

ADO Recordset.find method Criteria

Can you use an adodb.recordset.find method to locate a record on an MS SQL 2
000 server table? I can't figure out how to specify the criteria correctly
and haven't found any good documentation. Ex:
Dim tblBusiness As New ADODB.Recordset
'Make an ADO connection to the database.
cn.ConnectionString = "XXXXXXXXXXXXXX'"
cn.Open
cn.Properties("Current Catalog") = "MyDB"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
I haven't found anything detailing how to specify the criteria for the find.
Thanks.My recommendation would be not to use Find, but to use a select
statement with a WHERE clause, selecting only required data. What you
are trying to do is fetch an entire table, then throw away the rows
you don't want. Think for a moment about what happens to the network,
the server, and your client application when the table has 1,000,000
rows. "SELECT <column list> FROM <TableName> WHERE <some
field>=Criteria" is far more efficient than filtering after the fact.
--Mary
On Sat, 8 May 2004 16:31:01 -0700, "DB"
<anonymous@.discussions.microsoft.com> wrote:

>Can you use an adodb.recordset.find method to locate a record on an MS SQL
2000 server table? I can't figure out how to specify the criteria correctly
and haven't found any good documentation. Ex:
> Dim tblBusiness As New ADODB.Recordset
>'Make an ADO connection to the database.
> cn.ConnectionString = "XXXXXXXXXXXXXX'"
> cn.Open
> cn.Properties("Current Catalog") = "MyDB"
> cmd.ActiveConnection = cn
> cmd.CommandType = adCmdText
> tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
> tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
>I haven't found anything detailing how to specify the criteria for the find
.
>Thanks.

ADO MaxRecords and table variables

It appears that when I specify the MaxRecords property in ADO to something other than 0, not only does SQL Server 2000 limit the number of rows that are being returned from a stored procedure, it also limits the number of "select into"s within the stored proecudre (I'm inserting into table variables within the proc).

Here's a scenario of why this is a problem. Suppose I have a proc that needs to perform calculations on all employees in my system (10,000 of them). The calculation is performed by selecting these employees into a table variable with some calculated values being inserted as well. When the processing is complete, I need to select the top 1500 employees from the table.

If I specify MaxRecords in ADO, the "select into" statement that populates my table variable appears to limit the number of rows to 1500. This is a problem if I'm expecting an employee outside of the first 1500 to be considered for the calculations I'm performing.

Is there some way of indicating that MaxRecords should be ignored when selecting into table variables or temporary tables within a stored proc?

Thanks,
TerenceI know I could simply use "top" in the last select statement of the stored procedure to get around this problem, however, this is not an option on the system I'm currently developing (for design reasons (possibly poor design reasons now that I think about it ;) ))

Thanks again,
Terence