calculating a value to match a composite key 2

[argeedblu]

In order to use the ADO Recordset Seek method, it is necessary to provide the search value as the first argument. This is simple enough for a single field key. But how do you determine the value to use when you want to seek using a unique muliticolumn index?

 

[SkipVought]

Hi,

What are the multi columns?
What are the data types?
What are some typical data values?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[argeedblu]

In this specific case the two fields are both long integers. They are actually foreign keys in a junction table for a many to many relationship. Together they make up a unique index so that there can be only one instance of any particular pair of value one and value two. So, typical values in field1 can be any value between minus ~ 2 billion and plus ~ 2 billion. Values in field two can also be any value in the same range.

That is the specific case. What I am really looking for, however, is the generic algorithm that access uses to calculate a composite key, regardless of the specific data types and number of columns involved. I want to usel the algorith in a function that would calcluate the search parameter for the seek function of an ADO recordset.

Glenn

 

[SkipVought]

Instead of a composite key, why not a where clause including both key values
[tt]
sSQL = "Select...
Where T1.Key1=" & Value1 & " And T1.Key2=" & Value2
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

the generic algorithm that access uses to calculate a composite key
???
Take a look at the Supports (adIndex and adSeek) method and the Index property of the ADO.Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[argeedblu]

the generic algorithm that access uses to calculate a composite key
???

As I understand it the seek method requires as its first argument a value of the key you are looking for in the specified index. That's no problem when you have a single field key. However with a composite key, the index values are made of some sort of combination (probably a hash) of the related record's relevant fields. In order to create an index entry, Access must use some sort of algorithm to compute the value it store. I want to be able to test any given combination of possible values, using the seek method to see if that combination is already in the index. So I want to take, in the specific example I quoted a potential value for field one and a potential partner for it in field two, compute what the key should be and seek it in the relevant index. If it's not there (i.e. we reach eof with no find) then a record with that combination can be added.

Glenn

 

[argeedblu]

Skip,

It looks like that is the direction I will have to go - opening a recordset with a where clause for the relevant values.

An alternative approach someone in another forum suggest was to use Dlookup to search for the relevant values. Do you know offhand, how this approach would compare, in terms of efficiency, to opening a recordset?

Thanks for your help.

Glenn

 

[PHV]

The first argument of the Seek method is an Array containing the keyparts values in the order of the composite key definition.
Be aware that the Seek method is available only for server side cursors opened with adCmdTableDirect.
You may consider the Filter property of the recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[argeedblu]

PH,

Array??? Oops, I have been misreading what documentation I could find on the ADO Seek method and completely missed the note that the argument is an array. Consequently I was setting myself out to do a lot more work than necessary on an approach that was doomed to fail. Many thanks for pointing me in the right direction.

I now have my function working. Incidentally, probably because of its 'hybrid' nature, I have found that an Access mdb ado recordset can use seek even if the serverside cursor is not explicitely specified.

This has been a time-cosuming but worthwhile learning experience for me.

Thanks again.

Glenn