Hello,

I am developing an application for a client using Pervasive 2000i. I am
using OLEDB to get at the data. I can open the data and read the data one
record at a time. When I try to set the recordset index after opening the
recordset, I get the error "index does not exist". The index I am trying to
use is a segmented one. I need to do this so that I can Seek a record. I
have used BtSearch and the indexes are clearly visible in there for the
dataset in question.

I can also open a different recordset and set the recordset index
sucessfully and then use the seek command to get find the data I need.
Therefore I believe my code to be correct.

Is there any reason why I cant set the index on this one recordset

Cheers

Chris

Do ddf's reflect correct indexes? If so, are fields for each segment
defined? You can test segmented indexes w/matching ddf's using the
Pvsw\Demodata. Billing, Class, Department, Enrolls, Faculty, Person and
Room will have an index to test.

Don

I believe that the ddfs reflect the current indexes as they were supplied by
the developers of the application whos data I am trying to access. The
fields are defined for each of the segments.

In one of the index fields, I have noticed data that violates the field
length information in the ddf. This was caused by an erronous data
conversion . This data has since been corrected but I have not managed to
re-test.

Would this prevent access to the indexes?

Many thanks

Chris


"dbSw Solutions, Inc." <Don.Brady (AT) cox (DOT) net> wrote

ddf's if at all possible should exactly match btrieve indexes. Make sure
Index is specified before opening. Below is a VB6 example using Class table
and segmented index "Class_Name".

Dim RS As New ADODB.Recordset

With RS
..CursorLocation = adUseServer
..Index = "Class_Name"
..Open "Class", "Provider=PervasiveOLEDB;Data Source=DEMODATA", adOpenStatic,
adLockOptimistic, adCmdTableDirect
..Seek Array("PSC 101", "002"), adSeekFirstEQ
Debug.Print "Name=" & !Name & ";Section=" & !Section
..Close
End With
Set RS = Nothing

Don

In BtSearch where would I find the index name of segmented index. I am
using the numeric value of the index as noted in BtSearch,

Therefore I use

rst.index=1

I can use this sucessfully on other data sets with the same ddfs.

The fields in the index are "Pallet_Number" and "Purchase_Order_Number" and
using rst.index="Purchase_Order_Number" etc sill returns index not found

Cheers

Chris


"dbSw Solutions, Inc." <Don.Brady (AT) cox (DOT) net> wrote

Index name is what it is, don't confuse it with a field name.

If your index is segmented in this order: "Pallet_Number",
"Purchase_Order_Number"
and only care about "Purchase_Order_Number", find another index that has
"Purchase_Order_Number" as the first or only segment. If the segment order
is "Purchase_Order_Number", "Pallet_Number" then this will work:

rst.Index = ? (either the number or *Index name*)
Open the recordset, then
rst.Seek Array(PO number looking for, smallest value for Pallet_Number),
adSeekAfterEQ
Loop until rst!Purchase_Order_Number <> PO number looking for or rst.EOF

Here, a simple segmented index example. Two fields *FName* and *LName* are
indexed with an Index name of *idx_LFName*, segmented as follows, LName,
FName. Using this index would be a snap for all Jones (last name) but
useless for Jack (first name). The data looks like this: (both fields are
10,bytes)

Jaminson..Freddy....
Jones.....Barney....
Jones.....Jack......
Smith.....Art.......
Wilkerson.Jack......

rst.Index = "idx_LFName"
Open the recordset, then
rst.Seek Array("Jones", ""), adSeekAfterEQ
will take me to: Jones.....Barney.... loop until rst!LName <> Jones or
rst.EOF and give me 2 Jones.

Now, using this index and looking for Jack via Seek would look like this:
rst.Index = "idx_LFName"
Open the recordset, then
rst.Seek Array("", "Jack"), adSeekAfterEQ
will take me to: Jaminson..Freddy.... loop until rst!FName <> Jack or
rst.EOF and give me 0 Jacks.

Using this index to get our 2 Jacks would require this.
rst.Index = "idx_LFName"
Open the recordset, then
loop until rst.EOF, checking rst!FName = Jack for each record

Remember, anytime you use a segmented index and Seek, Array function must be
used and equal to number of segments. - Don

Index name is what it is, don't confuse it with a field name.

If your index is segmented in this order: "Pallet_Number",
"Purchase_Order_Number"
and only care about "Purchase_Order_Number", find another index that has
"Purchase_Order_Number" as the first or only segment. If the segment order
is "Purchase_Order_Number", "Pallet_Number" then this will work:

rst.Index = ? (either the number or *Index name*)
Open the recordset, then
rst.Seek Array(PO number looking for, smallest value for Pallet_Number),
adSeekAfterEQ
Loop until rst!Purchase_Order_Number <> PO number looking for or rst.EOF

Here, a simple segmented index example. Two fields *FName* and *LName* are
indexed with an Index name of *idx_LFName*, segmented as follows, LName,
FName. Using this index would be a snap for all Jones (last name) but
useless for Jack (first name). The data looks like this: (both fields are
10,bytes)

Jaminson..Freddy....
Jones.....Barney....
Jones.....Jack......
Smith.....Art.......
Wilkerson.Jack......

rst.Index = "idx_LFName"
Open the recordset, then
rst.Seek Array("Jones", ""), adSeekAfterEQ
will take me to: Jones.....Barney.... loop until rst!LName <> Jones or
rst.EOF and give me 2 Jones.

Now, using this index and looking for Jack via Seek would look like this:
rst.Index = "idx_LFName"
Open the recordset, then
rst.Seek Array("", "Jack"), adSeekAfterEQ
will take me to: Jaminson..Freddy.... loop until rst!FName <> Jack or
rst.EOF and give me 0 Jacks.

Using this index to get our 2 Jacks would require this.
rst.Index = "idx_LFName"
Open the recordset, then
loop until rst.EOF, checking rst!FName = Jack for each record

Remember, anytime you use a segmented index and Seek, Array function must be
used and equal to number of segments. - Don

Don,

Despite using both the index name and index number I still cannot open the
recordset and set the index. I can do this with the other data sets in this
application but this one dataset still eludes me.

I have even tried using the non-segmented indexes (just to test) that are
also present but still no joy with opening the recordset.

Do you have any other suggestions.

Cheers

Chris
"dbSw Solutions, Inc." <Don.Brady (AT) cox (DOT) net> wrote

Don

Actual code used...

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Index = 0
rst.Open "PALLET", connstring, adOpenDynamic, adLockReadOnly,
adCmdTableDirect
rst.Seek Array("888881", 1), adSeekAfterEQ

Index Data from BtSearch

Key Number Seg Field Name Start Pos Len Dups
Mod Descend Supplimental
0 0 desp_order_no 0 4
Yes Yes No No
0 1 pallet_number 8 4
Yes Yes No No

Many thanks for your continued help with this

Chris Smith

This works
rst.Index = ?
rst.Open ?

This fails with Index does not exist.
rst.Open ?
rst.Index = ?

Are you sure the btrieve file in question is OK and ddf's are an exact
match?
Could you include code snip for recordset Seek that works and one that does
not.