Recordset.Seek problems

[dchendrickson]

I am using Access 2002/XP. I am writing VBA primarily
using ADO.

I am trying to find a record in a recordset searching on
two of the fields. The find method only allows for
searching on one field, so I tried to use the seek
method. But I keep having problems - Access tells me that
things are not supported by the cursor.

The primary index for the table is set to the two fields
I want to search on. The code looks something like this.

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim intSlot As Integer
Dim intRack As Integer

Set cnn = CurrentProject.Connection
rst.Open "tblIOChassis", cnn, adOpenStatic, ,
adCmdTableDirect

'some lines of code setting variables and program flow

rst.Index = "PrimaryKey"
rst.MoveFirst
rst.Seek Array(intSlot, intRack), adSeekFirstEQ

This causes an error when the .Index line executes
stating something is not supported by the cursor.

So I made an alternate version and I explicitly set the
cursor type. I even created a new index in the table that
is identical to the primary index but has a different
name (yeah, sometimes I grasp as straws...)

.....
'same DIM statements

Set cnn = CurrentProject.Connection
rst.CursorLocation = adUseServer
rst.Index = "IOChassisFormLoad"
rst.Open "tblIOChassisSlot", cnn, adOpenStatic, ,
adCmdTableDirect

'some lines of code setting variable and program flow

This version causes an error when the rst.Open line
executes. The error is:

Error # 2147217867 - Invalid Operation.

I think I understand that I have a problem with the
creation of the server-side cursor recordset, but not
sure what.

I know an alternate method would be to open the record
set with an SQL statement to return just what I want, but
then I would need to open and close the recordset a
couple hundred times as I loop though my code instead of
just opening once and finding the info I need.

Thanks for your much appreciated input.

-dc

 

[dchendrickson]

I set up a couple of scratchpad type procedures to
explore the connection object. It just plain doesn't look
like the .seek method is an option. Still don't know why,
but I have given up and created an alternate work around.
Not very pretty or efficient - but it works.

-dc