Indexes on Computed columns and ADO

OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

TIAYou can try to set the "arithmetic abort" property to on (in SQlServer>properties>connection tab).

I had the same problem, in one of my application, except there I was logging on to the server via ODBC drivers, not OLEDB (as ADO usualy does).

I hope it works. Good luck!

IONUT|||Originally posted by zarathustra
OK maybe this crosses the SQLServer forum (apologies and dlet me know if so) BUT:

I have a composite PK/index with a computed column (basically CASTing a bit value). The index is fine in SQL2K however when I try to access the table through an ADO recordset (to a VBA form) the Datasheet control is locked.

Seems like a behind the scene ADO cursor-type/lock-type issue. ANY input is welcome I AM UNDER THE PRODUCTION GUN.

TIA

Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.|||Originally posted by Ad Dieleman
Try what happens if you lose the computed column in the primary key and replace it by the column(s) that the computed column was based on. There should be no need to have a computed column in the primary key.

I must admit that I also had trouble with ADO when using computed columns and I quickly ended up avoiding computed columns, not wanting to spend the time to find how to solve the problems.

I agree about not having the PK contain a computed val but IXs can't have bit values. Hence the comp'd field. I've got it working now. I've seen issues as well with computed fields/ADO and connection settings like ARITHABORT etc... joy.

thanks to you both.