problem in seek value in recordset with index.

[miao jie]

Hi,
now I try to use index in recordset to improve my program speed,
I create a table name: values_Clplus_YTD
have three column: entity, account, value. and I create primarykey index for
column entity and account.

now I have code as following,
SQL = "select * from values_Clplus_YTD"
Access_ADO_Connect ' defined function
Set acceRs = New ADODB.Recordset
acceRs.Open SQL, acceConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
If Not acceRs.EOF Then
If acceRs.Supports(adIndex) And acceRs.Supports(adSeek) Then
acceRs.Index = "PrimaryKey"
acceRs.Seek dataArry, adSeekAfterEQ
MsgBox acceRs.fields("entity") & acceRs.fields("account")
End If
End If

acceRs.Close
Set acceRs = Nothing
Access_ADO_Disconnect ' defined function

acceRs.Supports(adIndex) and acceRs.Supports(adseek) always say false
whenever I set open recordset with adOpenKeyset. so I can't use seek function.

anyone can help me out, thanks in advance

 

[Stephen Bullen]

Hi Miao,

acceRs.Supports(adIndex) and acceRs.Supports(adseek) always say false
whenever I set open recordset with adOpenKeyset. so I can't use seek function.

anyone can help me out, thanks in advance

IIRC, Seek is only available with a direct table connection, which you've
correctly passed in the Open method, but I think you should just be passing the
name of the table in the SQL string, not a query:

acceRs.Open "values_Clplus_YTD", acceConn, adOpenKeyset, adLockReadOnly,
adCmdTableDirect

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[Jamie Collins]

Hi,
now I try to use index in recordset to improve my program speed,
I create a table name: values_Clplus_YTD
have three column: entity, account, value. and I create primarykey index for
column entity and account.

now I have code as following,
SQL = "select * from values_Clplus_YTD"
Access_ADO_Connect ' defined function
Set acceRs = New ADODB.Recordset
acceRs.Open SQL, acceConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
If Not acceRs.EOF Then
If acceRs.Supports(adIndex) And acceRs.Supports(adSeek) Then
acceRs.Index = "PrimaryKey"
acceRs.Seek dataArry, adSeekAfterEQ
MsgBox acceRs.fields("entity") & acceRs.fields("account")
End If
End If

acceRs.Close
Set acceRs = Nothing
Access_ADO_Disconnect ' defined function

acceRs.Supports(adIndex) and acceRs.Supports(adseek) always say false
whenever I set open recordset with adOpenKeyset. so I can't use seek function.

anyone can help me out, thanks in advance

You need a client side cursor plus a data engine *and* data provider
that supports Seek and Index. I can't see anything wrong with your
code but perhaps your experience suggests a flawed approach i.e. such
operations should be undertaken on the server side e.g. using a stored
procedure. Otherwise, you may find you have to use another recordset
method such as Find or Filter. If you need an index for performance,
take a look at the Optimize dynamic property:

http://msdn.microsoft.com/library/d...en-us/ado270/htm/mdprooptimizepropertyrds.asp

Jamie.

--