MSDE equivalent of Jet .Seek ? 2

[Morningstarr]

I have a VB6 front end that I am moving from MS Jet 4.0 Access DB to MSDE. Within the project there are over 300 places where I use something to this effect: (Pseudo code)

rs.Open "tblCust", gConn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rs.Requery
rs.index = “lName”
rs.seek “Smith”

With MSDE when I change the “rs.open” to a sql statement pulling only the subset of records that I want, the seek statement returns the error:
3251~Current provider does not support the necessary interface for Index functionality.

Is there a way to use the underlying table indexes through MSDE to do some kind of ordered seek on a recordset?

I’ve searched MS website, get millions of results but no answers. Any help, websites would be greatly appreciated.
Thanks,
M

 

[jgoodman00]

Is there a reason why you need to specify which index to use?

Does it work without specifying an index?

Have you checked out the ADO help file for the index property?

The query optimizer automatically decides which, if any, indexes to use. You can however 'influence' its decision using index hints. However, even if you specify an index hint, the query optimizer may still override this & use its own choice of index, if any.

If you search BOL for index hints it will give you some information which may be of use...


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[vongrunt]

Probably because of over 300 places where this construct is used.

AFAIK generic SQL Server providers (SQLOLEDB included) don't support ADO adSeek/adIndex. Find and Filter are supported.

 

[Morningstarr]

James,
Performance from one machine to another I'm told was the reason for the seeks originally. One machine is the server the other(s) is basically a dumb terminal. Pulling entire record sets across the network degraded performance substantially and seek was an easy fix.
You do make a good point though. Why even specify an index if the query optimizer does it for you. One more question if you would, is using a .Execute sql statement faster than using a stored procedure or is it the other way round?
Thanks,
M

 

[Morningstarr]

vongrunt,

I don't understand your answer, what does this mean?
>>>Probably because of over 300 places where this construct is used.<<<
thx
M

 

[SonOfEmidec1100]

Why not just pull the record you need rather than the whole table, something like

    sReqName = "Smith"
    sQuery = "SELECT * FROM tblCust WHERE NameField = '" & sReqName & "'"
    rs.Open sQuery,gConn, adOpenKeyset, adLockOptimistic, adCmdText

 

[jgoodman00]

Personally, I would create an sp which takes a parameter & returns a single record. That way you only pass in one value, & only row is returned...



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[vongrunt]

> I don't understand your answer, what does this mean?

I thought you want quick code fix... 300+ is lotsa places. The obvious solution is to findi SQL2k provider that supports ADO index/seek, if such provider exists.

Btw. for larger databases/applications seek/adCmdTable* stuff isn't good idea (server-side cursors, locks/concurrency issues).

 

[Morningstarr]

Hi Vongrunt,
Nah no quick code fix, that's what seeks were. Unfortunately I don't make the decisions. The provider decision has already been made. MSDE is it. I was just trying to find a solution that would work for both Jet and MSDE so I don't have to support both types of statements while MSDE is being rolled out to over 2000 machines:
If MSDE then
<execute sql statement>
else 'IF still Jet
<seek>
end if
300+ times!!
Gotta love rework!
Thanks very much for your help!!

 

[Morningstarr]

SonOfEmidec1100
Performance is degraded across the network to the slave when using straight sql statements.

James,
The stored procedures are probably going to be the way to go
but that leaves me with those pesky If Else statements (see previous note) Star for you for query optimizer note!! Excellent!

Thank you both for your input!

 

[jgoodman00]

If it is identical in the 300+ places, why not write an access function which can be maintained in one place?

You can also use the Search/Replace function to modify your code as necessary (VB).

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[Morningstarr]

Hi James,
That's another problem. It is 8 different databases and at the very least 6 tables per. I'm going to try and consolidate this mess into as few stored procedures as possible. There is one table that is called more than 150 times, which I have already written a stored procedure for. I just hope it works as well on the slave as it does on the primary!!
Thanks again for your help!
M