Moving from Access to SqlServer - .Index Problem

[Ph0b0s]

Hi.

I've a VB6 application, which was based on an Access database. Now we're moving to sqlserver. We use ADODB, and when we use this piece of code:

Dim TbTest as RecordSet

Set TbTest = new Adodb.recordset
TbTest.open "Custom", Curcnxni, adOpenStatic, adLockPessimistic, adCmdTableDirect

TbTest.index = "PrimaryKey"

It throws an error, property not supported. How does sqlserver handles indexes? This kind of index declaration is in many places in the code.

Any help? Thanks in advance!

[amrita]

Hope this link will help you!!

http://groups.google.co.in/group/mic...b0ec363f606172

[Hack]

Moved to database section

[Ph0b0s]

Thanks for the Link amrita. Somehow it was self-explanatory.

.index
.seek

Not supported by a sqlserver recordset, because in sqlserver we've a new philosophy, not bringing all the records over the network, only the needed. But, if i really wanted to use the .index and .seek, even if they are not the best option... isn't really there any solution?

[si_the_geek]

It's not a new philosophy, or unique to SQL Server - even with Access you should not be returning all rows to your program, as not only is the database engine more efficient at searching than your program, but it also reduces the amount of memory and CPU usage (as well as network usage if apt), and is less likely to cause errors when multiple users are working with the same database.

There is an alternative method suggested in the help, but that can't be recommended either, for the same reasons.

What you should do is merge the .Open/.Index/.Seek rows into one, eg:

  TbTest.open "SELECT * FROM Custom WHERE PrimaryKey = value", Curcnxni, adOpenStatic, adLockPessimistic, adCmdText

..or more readable (and easier to debug):

Dim strSQL as String
  strSQL = "SELECT * FROM Custom WHERE PrimaryKey = value" 
  TbTest.open strSQL, Curcnxni, adOpenStatic, adLockPessimistic, adCmdText