Manually setting ADO KeyColumn property
Posted on 2001-07-03
Do you know if it is possible to manually set the KeyColumn property for a column in an ADO Recordset? For example, I'd like to be able to do this:
rs("title_id").Properties("KeyColumn") = True
When I try this I get "The operation is not valid in this context".
Normally ADO and OLE-DB can figure out this property correctly. However, I'm opening up a recordset against a View which has two tables in the FROM clause (but only SELECTs fields from 1 table). I can update this view, but if you look at SQL Profiler, you see that the update statment uses every single field in the SELECT clause in the WHERE clause. It should just be using "WHERE title_id = x". For performance issues, I want to tell it to use the title_id column (which is guaranteed to be unique in my situation).
The recordset's CursorLocation must be set to adUseClient in my situation, and I am correctly setting the "Update Criteria" property to adCriteriaKey. If ADO would let me change the KeyColumn property of the title_id column to True, I am sure that the UPDATE statement would just use that field in the WHERE clause.
I am using MDAC 2.6 on the client and SQL 2000 on the back-end. I have created the view with the "WITH VIEW_METADATA" tag.