Seeking records in SQL-Server

[moben]

I have a VB6 application which uses a Access 2000 database. I want to be able to connect to a SQL-Server table (can't link into Access DB, as SQL-Server table has too many indexes, which I can't delete).

I am accessing the SQL-Server table using the following code.

Dim rsFutures As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open strCnxn
Set rsFutures = New ADODB.Recordset
rsFutures.CursorType = adOpenKeyset
rsFutures.LockType = adLockOptimistic
rsFutures.Open "futures", Conn, , , adCmdTable
rsFutures.MoveFirst
' find/seek rsFutures!ticket="20060405ABC" ????

Questions.

1. How do I open a index for this SQL-Server table.

2. I want to be to search the table based upon a search value. FIND is taking far too long, and if I can't use SEEK, then I could run a query, but what code do I need to add to create a query which retrieves data from recordset rsFutures based upon my search criteria ?

Any and all responses will be greatly appreciated, as I need urgently to replace slowness of the FIND.

Regards,

Moben.

 

[macleod1021]

I would pass an sql statement instead of the find. It's a lot less intensive on your network and local resources.

Dim rsFutures As ADODB.Recordset
Dim str as string
Set Conn = New ADODB.Connection
Conn.Open strCnxn
Set rsFutures = New ADODB.Recordset
str = "Select * from futures where ticket='20060405ABC'
rsFutures.CursorType = adOpenKeyset
rsFutures.LockType = adLockOptimistic
rsFutures.Open str, Conn, , , adCmdTable
If rsFutures.eof = false then
rsFutures.MoveFirst
...do what you need to do here
End if
Set rsFutures = nothing
Conn.close

The good part about using this is you only get the record that you need instead of bringing down the entire table. You can also use a variable in the select statement suck as: Select * from futures where ticket='" & txtTicket & "'"

Hope this helps.

 

[moben]

Thanks Macleod1021 for your quick response, much appreciated.

I will try your solution later today.

Many thanks,

Moben.

 

[moben]

Hi Macleod1021,

I got the following error when passing a SQL statement into the recordset Open statement.

"Incorrect syntax near the keyword 'Select'"

I am passing a simple 'Select * from Futures' into variable str !

Any ideas ?

Moben.

 

[gmmastros]

Try replacing...

[tt][blue]adCmdTable[/blue][/tt]

With

[tt][blue]adCmdText[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[macleod1021]

Mastros is right...I didn't catch that when I copied from your post You have to use adCmdText. Sorry for the confusion.

 

[moben]

George,

Many thanks that worked.

Regards,

Moben.

 

[moben]

Hi,

Is there any way that I can pass another select statement with a different ticket value i.e. can I perform a refresh, or would I have to perform all the statements for creating the recordset again ?

Ideally I would like to perform a search operation, but I'm not search if there is an index on the SQL-Server table. Using Find is very slow, the Select method is a lot quicker.

Any help greatly appreciated.

Regards,

Moben.

 

[gmmastros]

With your original method, you were effectively passing the entire table from SQL Server to VB.

With the method macleod1021 showed you, you are only passing a single record from SQL Server to VB. This, obviously, is much faster. If you want the data from another record, using macleod1021's method, you would need to requery the server. This is the better approach.

You can return multiple records at the same time without returning the entire table.

macleod1021's original suggestion was to do...
str = "Select * from futures where ticket='20060405ABC'

If you want multiple records, you can modify his code like so...

str = "Select * from futures where ticket in ('20060405ABC','[!]2ndTicketNumber[/!]','[!]3rdTicketNumber[/!]')"




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[macleod1021]

To add another level of efficiency to the above suggestions, you don't have to use * on the select. If you only need one field returned, then you can specify it in the Select statement like this

str = "Select field1 from futures where ticket='20060405ABC'"

If you think you'll be doing a lot of DB programming, I'd HIGHLY recommend picking up a book on T-SQL. You'll be amazed at how complex you can make your query's. And that's not even bringing Stored Procedures into the mix

 

[moben]

Thank you all for providing me with the correct answers in a very easy to understand way.

Your responses put me back on track in completing my tasks on time.

Many thanks to all,

Moben