Can't set index for recordset!

[billyr]

I have used this code sequence a lot with no problems - until Access2000.

Dim dbs as database, rst as recordset
set dbs=currentdb
set rst=dbs.openrecordset(tablename)
rst.index= desired index
etc,etc,etc

Now, I'm getting error msg saying I can't set that property for the object. Do I need to register something? I suppose I should have relented and started using ADOB? (How hard a conversion is that?) I think the solution is obvious; I just can't see it. Help Please. Thanks in advance.

 

[Fizzio]

The index must already be set at table definition level before you can index it in the recordset.

I am just making the transition from DAO to ADO and it is not too dissimilar. (Pat reccomends developing anything new in ADO but keep existing Db in DAO for consistency)

The ADO equivalent of your code would be..

Dim rst as adodb.recordset
set rst = new adodb.recordset
rst.activeconnection = currentproject.connection
rst.open "tablename",,adOpenDynamic,adLockOptimistic, adcmdtable
rst.index = "IndexField"
etc ...

There are a couple of subtle differences with the processing eg updating and searching but the help files in VBA I've found are helpful.

 

[fuzzygeek]

DAO to ADO

To use dao you must select dao in the references under tools. A good site to learn dao to ado is the following paper that MS has on their site. It shows how to do the same thing in both DAO and ADO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

 

[billyr]

Thanks for all the tips. The index I'm trying to set was created during the table design (Long ago). The reference to DAO3.6 is verified as already checked in the list. Could I have an unnecessary reference selected that is causing this. I have used this code sequence many times in other projects without incident. Just thought of this; the tables are split from application. Is that the problem? If so I know how to fix it using my own "locatetables" function.

 

[fuzzygeek]

Sequence

Access checks the order of the references. If the ado is above the dao reference, the default will be ado if you don't specify whether it is dao or ado in the code. I've seen the same thing happen in older code where the dao is selected, but below the ado selection. That's why we use code like adodb instead of just db. Just a thought.