How to create a multi-column index for a recordset for using Seek

[VirginiaMan]

Hi, I am having trouble with some ADO syntax in Access 2002 and wanted to get some feedback.

I am trying to create a multi-column index to use with a seek statement.

Here is what I have tried, to no avail!

I have two fields in my table that I want to create a recordset index for : "TCID", and "IMTH" (These are not fields in the primary key on the underlying table, if that makes a difference).

The seek seems to work fine when I use a single field name for the index such as:

rstAllReps.Index = "TCID"

and rstAllReps.Seek TCIDIndex, adSeekFirstEQ

I tried using an array such as:

rstAllReps.Index = Array("TCID", "IMTH")

or

SeekArray = Array("TCID", "IMTH")

and

rstAllReps.Index = SeekArray

I keep getting a "Type mismatch" error when I run this. Does anyone have an idea how I could set up a multicolumn index for a recordset, that I could use with Seek?

 

[rmikesmith]

According to my ADO reference (

http://www.w3schools.com/ado/default.asp),

indexes must already have been created on the table. So you would need to set up the multi-column (field) index from Access. Alternatively, this can also be done using ADOX (i.e., Index.Append).


Regards,
Mike

 

[VirginiaMan]

Thanks Mike, that works just fine. Also, thats an interesting website that I didn't know existed. Looks like I have more research to do!