Indexes in SQL Server with ADO Recordset 
Author Message
 Indexes in SQL Server with ADO Recordset

I'm want to open a recordset which is connected a SQL Server database, but I
not found the way to open the recordset to manage indexes.
I tried with all forms to open the recordset but in all vb tell me that the
recordset not supports indexes.

Anyone that knows the manner to open a recordset to manage indexes with SQL
Server?

Help please.

Thanks.



Sun, 05 Oct 2003 09:04:09 GMT  
 Indexes in SQL Server with ADO Recordset
When you use ADO to create a Recordset, the provider instructs the DBMS to
perform the query. In the case of Jet (pre 4.0) or SQL Server, the correct
index is selected automatically. If, however, you're using Jet 4.0 and have
ADO 2.5 or later you can use the Index property to select an index and use
the Seek method to position to indexed rows in the chosen table. There is an
example of this in my book (see page 130) www.betav.com has details.

hth
BV


Quote:
> I'm want to open a recordset which is connected a SQL Server database, but
I
> not found the way to open the recordset to manage indexes.
> I tried with all forms to open the recordset but in all vb tell me that
the
> recordset not supports indexes.

> Anyone that knows the manner to open a recordset to manage indexes with
SQL
> Server?

> Help please.

> Thanks.



Mon, 06 Oct 2003 04:23:01 GMT  
 Indexes in SQL Server with ADO Recordset
On Wed, 18 Apr 2001 13:23:01 -0700, "William \(Bill\) Vaughn"

Quote:

>When you use ADO to create a Recordset, the provider instructs the DBMS to
>perform the query. In the case of Jet (pre 4.0) or SQL Server, the correct
>index is selected automatically. If, however, you're using Jet 4.0 and have
>ADO 2.5 or later you can use the Index property to select an index and use
>the Seek method to position to indexed rows in the chosen table. There is an
>example of this in my book (see page 130) www.betav.com has details.

There are some qualifications here. Seek only works with a Server
cursor (not client), it only works if a table is opened directly by
name, and not, by  SQL string, and it is not supported by all
providers.

ie    
      rst.CursorLocation = adUseServer  
      rst.Open "Employees"  

works with seek (if employees is one of your tables), while

       rst.CursorLocation = adUseServer
       rst.Open "SELECT * FROM Employees"

does not wor (according to the ADO2.6 SDK documentation).

The Find method works with all providers, but is not as quick or
flexible as seek.

Seek work with the Access provider, but I do not know myself about SQL
server.

If rst is your recordset use:

        if rst.Supports(adIndex) And rst.Supports(adSeek)  then
                ' run Seek code here
`       else
                ' run Find code here
        end if

Richard.

Quote:
>hth
>BV



>> I'm want to open a recordset which is connected a SQL Server database, but
>I
>> not found the way to open the recordset to manage indexes.
>> I tried with all forms to open the recordset but in all vb tell me that
>the
>> recordset not supports indexes.

>> Anyone that knows the manner to open a recordset to manage indexes with
>SQL
>> Server?

>> Help please.

>> Thanks.



Mon, 06 Oct 2003 07:32:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Indexes in SQL Server with ADO Recordset

2. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

3. ADO 2.7 and SQL Index problem with supported Recordsets

4. ADO, Indexes & SQL Server

5. Important FYI - Multiple Recordsets w ADO/SQL Server

6. Important FYI - Multiple Recordsets w ADO/SQL Server

7. Important FYI - Multiple Recordsets w ADO/SQL Server

8. Inserting data from an ADO recordset into a SQL Server table

9. HELP - ADO recordset clone failing on SQL Server 7

10. Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure

11. Retreiving recordset through ADO from a SQL-server database generates timeout errors

12. ADO, MTS, SQL Server RecordSet Questions

 

 
Powered by phpBB® Forum Software